Outer join

An inner join select returns a row for each data coming from the two tables involved being connected by the "on" condition (or the "natural" PK -> FK relation).

If there a row in a table that has no relation with a row in the other table is simply skipped from the result.

If we actually want it in the result we use an OUTER JOIN. In this case the fields referring to the other table would report NULL as a value.

Let's add another contact to our table, this one with no job associated:

insert into contact values (3, 'Bill', 'Countless', NULL);

The inner join select we have written in the previous post would run giving the same result. If we want to see Bill last name, and a NULL as its job, we should use an outer join like this:

select c.last_name, j.description
from contact c
natural left outer join job j;

Being a LEFT outer join we have the data coming from the first table cited in the query (contact) and NULL for the missing data on the second one.

If we write the query the other way round, using job as first table, we get all the job descriptions and a NULL for the (eventually) missing relations:

select c.last_name, j.description
from job j
natural left outer join contact c;

We could achieve the same result keeping the tables in the same position and using a RIGHT outer join instead of a LEFT one:

select c.last_name, j.description
from contact c
natural right outer join job j;

The last two outer joins lead to the same result. It is a matter of taste using one style or the other.

A fun basic book on SQL: Head First SQL.