SQL Outer Join

This page covers both the left outer join, the right outer join, and the full outer join, and explains the differences between them.

There are some occasions where you would need to use a left outer join or a right outer join, and others where you would need a full outer join. The join type you use will depend on the situation and what data you need to return.

Left Outer Join

Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.

Example SQL statement

Source Tables

Left Table

IndividualIdFirstNameLastNameUserName
1FredFlinstonefreddo
2HomerSimpsonhomey
3HomerBrownnotsofamous
4OzzyOzzbournesabbath
5HomerGainnoplacelike

Right Table

IndividualIdAccessLevel
1Administrator
2Contributor
3Contributor
4Contributor
10Administrator

Result

IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel
1FredFlinstonefreddo1Administrator
2HomerSimpsonhomey2Contributor
3HomerBrownnotsofamous3Contributor
4OzzyOsbournesabbath4Contributor
5HomerGainnoplacelikeNULLNULL

Note the following:

Right Outer Join

Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.

Example SQL statement

Source Tables

Left Table

IndividualIdFirstNameLastNameUserName
1FredFlinstonefreddo
2HomerSimpsonhomey
3HomerBrownnotsofamous
4OzzyOzzbournesabbath
5HomerGainnoplacelike

Right Table

IndividualIdAccessLevel
1Administrator
2Contributor
3Contributor
4Contributor
10Administrator

Result

IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel
1FredFlinstonefreddo1Administrator
2HomerSimpsonhomey2Contributor
3HomerBrownnotsofamous3Contributor
4OzzyOsbournesabbath4Contributor
NULLNULLNULLNULL10Administrator

This time we get a row for the IndividualId of 10, but not for the IndividualId of 5.

Full Outer Join

Use this when you want to all rows, even if there's no matching rows in the right table.

Example SQL statement

Source Tables

Left Table

IndividualIdFirstNameLastNameUserName
1FredFlinstonefreddo
2HomerSimpsonhomey
3HomerBrownnotsofamous
4OzzyOzzbournesabbath
5HomerGainnoplacelike

Right Table

IndividualIdAccessLevel
1Administrator
2Contributor
3Contributor
4Contributor
10Administrator

Result

IndividualIdFirstNameLastNameUserNameIndividualIdAccessLevel
1FredFlinstonefreddo1Administrator
2HomerSimpsonhomey2Contributor
3HomerBrownnotsofamous3Contributor
4OzzyOsbournesabbath4Contributor
5HomerGainnoplacelikeNULLNULL
NULLNULLNULLNULL10Administrator

This time we get a row for the IndividualId of 10 and another row for the IndividualId of 5.

See my SQL joins tutorial over at Database.Guide for an overview to joins, all on a single page..