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

Question

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

SELECT c1.id as sender, c2.id as replier
   FROM contacts c1, contacts c2;

How can you use the same table twice?

Answer 1

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

Answer 2

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;