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

Question

Hi I have the following query and I wonder what that 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 identifiers.

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

You use one table twice in a query, giving it two names, for example.

Aliases are often entered with the keyword AS. You also usually specify a join condition (without it, you get a Cartesian product of a table connected to 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 can be used for joining in this example; we have no information to help solve this problem.

As a rule, another table should be used as an intermediary, for example, a message 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; 

Answer 3

This is a simple answer: use your query specified in the example. It should work fine. Although this is probably a bad idea, if you want to use the same table twice, remember that you need to join the “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 

Answer 4

Yes, you can use the same table twice by specifying different aliases in the table. I think learning about how I join will help you understand.

Answer 5

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

Note that you need to use table correlation names (colloquial "aliases") when a table appears more than once within the same area. For example, the following SELECT query uses the same table twice, but since each of them is within a separate area (each of which is separated by the UNION keyword), the table correlation name is not required:

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