SQL - Self join

A join does not always have to involve two different tables. You can join a table to itself, creating a self-join. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.

You can:

• answer practical question such as which products in the Product table have the same names but different types of packaging?
• or build a hierarchy

gerardnico@orcl>select t1.id "ID_table2"
  2        FROM table2 t1, table2 t2
  3       WHERE  t1.id = t2.id_2;
 
ID_table2
----------
A
C