SQL: Inner Join vs. Outer Join

A standard join in SQL is implicitly an inner join. This means that only
records that can be matched on both sides of the join will be included in
the result set.

Here is an example of that:

> select * from table_a join table_b on table_a.letter = table_b.letter;
 name  | letter |  name  | letter
-------+--------+--------+--------
 Brian | A      | Dunn   | A
 Derek | C      | Parker | C

That same query could be written with inner join.

We can alternatively write an outer join which will ensure that the
subject of the join has all of it's rows included even if there isn't a
matching row from the other table.

So, which table is the subject of the join? If it is a left outer join,
then it is the table on the left of the join. If it is a right outer

, then it is the table on the right of the join.

Here is a left outer join:

select * from table_a left outer join table_b on table_a.letter = table_b.letter;
  name  | letter |  name  | letter
--------+--------+--------+--------
 Brian  | A      | Dunn   | A
 Thomas | B      | Ø      | Ø
 Derek  | C      | Parker | C

And here is a right outer join:

select * from table_a right outer join table_b on table_a.letter = table_b.letter;
 name  | letter |  name  | letter
-------+--------+--------+--------
 Brian | A      | Dunn   | A
 Derek | C      | Parker | C
 Ø     | Ø      | Worth  | D

Notice the difference in where full result sets come from and where null
values have to be filled in depending on the left and right keywords.


For reference, here are the constituent tables:

> select * from table_a;
  name  | letter
--------+--------
 Brian  | A
 Thomas | B
 Derek  | C
> select * from table_b;
  name  | letter
--------+--------
 Dunn   | A
 Parker | C
 Worth  | D

Photo Credit: unsplash-logoWynand Uys

Leave a Reply

Your email address will not be published. Required fields are marked *