This page has 0 threads | Add post

Full, Left and Right Outer Joins in SQL

Written by Andy Brown

In this tutorial

Deciding on the Order of the Join
Writing the SQL for an Outer Join
Showing Unmatched Records
Full Outer Joins
Outer Joins with Multiple Tables

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


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:

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:

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:

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:

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:

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.

Previous
tutorial

Dates and times

Previous
part

Writing Inner Joins in SQL

Top of page

Next
part

Cross Joins using SQL

Next
tutorial

Grouping data

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Joins

Full, Left and Right Outer Joins in SQL

Deciding on the Order of the Join

Writing the SQL for an Outer Join

Showing Unmatched Records

Full Outer Joins

Outer Joins with Multiple Tables

Head office

London

Manchester