How Do Self-Joins in SQL Allow You to Query a Single Table as Multiple Tables?

In-depth understanding of SQL self-join

SQL self-join is a basic concept in database query, which allows you to query the same table multiple times just like operating two independent tables.

Definition of self-connection

Self joins work by creating an alias for the same table, allowing you to treat it as two different tables. A self-join simulates the presence of multiple tables without creating separate physical copies.

Self-connection example

Consider the following employee table:

<code>表 emp1
Id  姓名  主管Id
1   ABC   3
2   DEF   1
3   XYZ   2</code>

To retrieve each employee's name and their supervisor's name, perform the following self-join query:

<code>select c1.姓名, c2.姓名 As 主管
from emp1 c1
join emp1 c2 on c1.主管Id = c2.Id</code>

Output result:

<code>姓名  主管
ABC   XYZ
DEF   ABC
XYZ   DEF</code>

In this query, we assign the alias c1 to the primary instance of the emp1 table and the alias c2 to the alias representing the "second" instance. The join condition matches c1's supervisorId with c2's Id. This allows us to retrieve each employee's name from c1 and their supervisor's name from c2.

The above is the detailed content of How Do Self-Joins in SQL Allow You to Query a Single Table as Multiple Tables?. For more information, please follow other related articles on the PHP Chinese website!