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