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