What is self-connection

Self-join is a special type of table join, which means that the connected tables are physically the same table, but logically multiple tables. Self-join is typically used when the data in a table has a hierarchy, such as area tables, menu tables, item categories, and so on. The syntax is as follows:

SELECT A.column, B.column
FROM table A, table B
WHERE A.column=B.column;
Copy the code

Such as:

  1. SQL > alter table area; SQL > alter table area;
field instructions
id A primary key
pid The parent ID
name name
id pid name
1 0 guangxi
2 1 The north sea
3 1 nanning
4 0 hainan
5 4 haikou

Query the province for each city.

select A.id, A.name, B.name as province  from area A, area B
where A.pid=B.id and A.pid<>0;
Copy the code