Self join

We have seen how to retrieve data from related tables using an inner join, but sometimes it makes sense even perform an inner join from a table to itself. This is what is usually called a self join.

Let's add another field to our contact table, it looks a bit strange at first view, because it is a foreign key referring to the primary key of the same table. In this case we call it ref_id because it identify the contact who acted as reference for him to be entered in the contact table itself:

alter table contact add(ref_id number(*,0));

alter table contact add constraint
ref_contact_fk foreign key(ref_id ) references contact(id);

Let's say that Jones has id 1 and no one referenced him: his ref_id would be 1. Smith, on the other side, was referenced by Jones, so his ref_id is Jones's - that is 1.

The self join select showing the contact last names and their reference's one is:

select c.last_name, c2.last_name as ref
from contact c
inner join contact c2
on c.ref_id = c2.id;

We are using two different aliases (c and c2) to refer to the same table in the two different roles, c as "left" table (borrowing the outer join terminology) and c2 as "right".

A fun basic book on SQL: Head First SQL.