Writing inner joins and outer joins in SQL
Part four of a six-part series of blogs

An in-depth SQL tutorial on how to create inner joins, left outer joins, right outer joins, full outer joins, cross joins and self-joins!

  1. Inner Joins, Outer Joins and Shake-it-all-about Joins
  2. Creating Joins using your Mouse - the Query Editor
  3. Writing Inner Joins in SQL
  4. Full, Left and Right Outer Joins in SQL (this blog)
  5. Cross Joins using SQL
  6. Self-Joins in SQL (joining a table to itself)

This online training on joins is part of a longer tutorial on the whole of SQL.  Naturally, Wise Owl also run a range of SQL training courses for individualas and (above all) for businesses.

Posted by Andy Brown on 30 November 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Full, Left and Right Outer Joins in SQL

The previous part of this blog showed all of the matching rows in two tables - but what about if you want to show unmatching ones too?

Author with no book Two books with no author
An author with no books, and ... ... books with no valid author

Here's how to create a query to show all of the authors (as an example), along with any books that the author has written:

Authors and their books

The left column comes from table tblAuthor; the right one from table tblBook.  Notice that Oliver Owl has NULL next to him, as he hasn't got any corresponding books in the tblBook table.

 

Deciding on the Order of the Join

For an inner join, it doesn't make any difference which way round you create a link (linking authors to books is just the same as linking books to authors).  For an outer join, however, the order does matter.

In our example, we want to show all of the authors (and alongside that, any books each author has written), so the tblAuthor table is the main focus of our query and should be listed first:

Authors linked to books

A sensible layout for the two tables.

 

We'll therefore create a left outer join from the tblAuthor table to the tblBook table.

The term LEFT OUTER JOIN is a bit silly, as you're writing SQL into a text editor, and the concepts of left and right aren't defined there!

Writing the SQL for an Outer Join

Having decided on an order for our tables, it's time to write the join.  The syntax is nearly identical to that for an inner join:

SELECT

-- show name of author ...

a.FirstName + ' ' + a.LastName AS 'Author name',

 

-- and any book they've written

b.BookName

 

FROM

tblAuthor AS a

LEFT OUTER JOIN tblBook AS b

ON a.AuthorId=b.AuthorId

ORDER BY

'Author Name'

This is what this will produce:

Table of books by author in author order

The output from our query, showing each author and the books they've written.

 

If you wanted, you could change the order of the tables for this query and rewrite it to do exactly the same thing, but as a right outer join:

SELECT

-- show name of author ...

a.FirstName + ' ' + a.LastName AS 'Author name',

 

-- and any book they've written

b.BookName

FROM

tblBook AS b

RIGHT OUTER JOIN tblAuthor AS a

ON a.AuthorId=b.AuthorId

ORDER BY

'Author Name'

Showing Unmatched Records

If you want to show all of the records in one table which have no corresponding entries in another, you can do so by writing an outer join query with a criteria to pick out all of the records in one table where the corresponding records in the other table don't exist:

-- show all the authors who haven't written books

SELECT

a.FirstName + ' ' + a.LastName AS 'Author name'

FROM

tblAuthor AS a

LEFT OUTER JOIN tblBook AS b

ON a.AuthorId=b.AuthorId

WHERE

b.BookId is null

ORDER BY

'Author Name'

Here's what this query would produce:

Author with no books

The only author who doesn't have any corresponding books.

 

Alternatively, you could do this the other way round, to show a list of the books whose AuthorId columns don't have corresponding values in the tblAuthor table:

-- show all the books for which we can't find authors

SELECT

b.BookName

FROM

tblBook AS b

LEFT OUTER JOIN tblAuthor AS a

ON a.AuthorId=b.AuthorId

WHERE

a.AuthorId is null

ORDER BY

BookName

Here are the two records this query would show:

Two books without authors

The two books for which we can't find a matching author in the authors table.

 

Full Outer Joins

A left or right outer join will focus on showing all of the records from one table (with corresponding rows from another table where they exist).  A full outer join will show all of the records from all of the tables:

-- show every book and every author

SELECT

a.FirstName + ' ' + a.LastName

AS 'Author name',

b.BookName

FROM

tblAuthor AS a

FULL OUTER JOIN tblBook AS b

ON a.AuthorId=b.AuthorId

ORDER BY

'Author Name',

b.BookName

Here are the rows that this query would return:

Authors, books and null values

The query will show all authors (including those who don't have corresponding books) and all books (including those with no corresponding authors).

 

It's hard to see the point of a full outer join.  I've certainly never used one in anger! 

Outer Joins with Multiple Tables

You can mix outer and inner joins as you like.  The following query would show a list of all of the film directors from a table called tblDirector, along with the films, characters and actors for each director:

SELECT

d.DirectorName,

f.FilmName,

c.CastCharacterName,

a.ActorName

FROM

tblFilm AS f

INNER JOIN tblCast AS c

ON f.FilmID = c.CastFilmID

INNER JOIN tblActor AS a

ON c.CastActorID = a.ActorID

RIGHT OUTER JOIN tblDirector As d

ON f.FilmDirectorID = d.DirectorID

This query mixes a right outer join with two inner joins.

This blog has 0 threads Add post