Is it possible to use the same table twice in a select query?

Hi I have the following query, and I'm wondering what it means:

SELECT c1.id as sender, c2.id as r开发者_高级运维eplier
   FROM contacts c1, contacts c2;

How can you use the same table twice?

You use a single table twice in a query by giving it two names, like that.

The aliases are often introduced with the keyword AS. You also normally specify a join condition (for without it, you get the Cartesian Product of the table joined with itself). For preference you use the explicit JOIN notation.

SELECT c1.id AS sender, c2.id AS replier
  FROM contacts AS c1
  JOIN contacts AS c2 ON c1.xxx = c2.yyy;

It is not clear which columns might be used for the join in this example; we don't have any information to help resolve that.

Normally, there'd be another table to act as intermediary, such as a Messages table:

SELECT c1.id AS sender,  c1.email AS sender_email,
       c2.id AS replier, c2.email AS replier_email,
       m.date_time
  FROM messages AS m
  JOIN contacts AS c1 ON m.sender_id  = c1.id
  JOIN contacts AS c2 ON m.replier_id = c2.id;

This query creates a table containing all possible pairs of contact ids.

For example, if your contact ids were 1, 2, and 3 you would get, as a result

1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3

It's a simple answer : use your query listed in the example. It should work just fine. Although this is probably a bad idea, if you want to use the same table twice, be aware that you have to join "those" tables as they were different :

SELECT c1.id as sender, c2.id as replier
FROM contacts c1, contacts c2
WHERE sender.id = replier.id

Yes, you can use the same table more than once within the same SELECT query.

Note that you only need to use table correlation names (colloquially 'aliases') when the table appears more than once within the same scope. For example, the following SELECT query uses the same table twice but, because each is within a distinct scope (each separated by the UNION keyword), no table correlation name is required:

SELECT id, 'Sender' AS contact_narrative
  FROM contacts
 WHERE something = 1
UNION
SELECT id, 'Replier' AS contact_narrative
  FROM contacts
 WHERE something = 2;

Yes, you can use the same table twice by giving different aliases to the table. I think studying about self joins will help you understand.