Self Join
SQL SELF JOIN
A table can be joined to itself in a self-join.
SQL Self Join syntax
SELECT column_name(s)
FROM A_table, B_table
WHERE A_table.column_name = B_table.column_name;
Test
ID | NAME | PARENT_ID |
---|---|---|
1 | aaa | NULL |
2 | bbb | NULL |
3 | ccc | 1 |
4 | ddd | 1 |
5 | eee | 2 |
6 | fff | NULL |
7 | ggg | 6 |
Example
SELECT b.* FROM Test a, Test b WHERE a.id = b.parent_id ORDER BY a.id ;
Results
ID | NAME | PARENT_ID |
---|---|---|
3 | ccc | 1 |
4 | ddd | 1 |
5 | eee | 2 |
7 | ggg | 6 |