SQL - Self join

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

1 - About

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
Advertising

3 - Example

table2
(inner and outer set)
Column ID Column ID_2
A
B A
C C
[email protected]>SELECT t1.id "ID_table2"
  2        FROM table2 t1, table2 t2
  3       WHERE  t1.id = t2.id_2;
 
ID_table2
----------
A
C