SQL OUTER JOIN

Summary: in this tutorial, you will learn how to use SQL outer join including left outer join, right outer join and full outer join.

If you want to learn about SQL INNER JOIN, check it out the SQL INNER JOIN tutorial.

There are three kinds of OUTER JOIN: left outer join, right outer join and full outer join. Let’s examine each kind of join in more detail.

SQL OUTER JOIN – left outer join

SQL left outer join is also known as SQL left join. Suppose, we want join two tables: A and B. SQL left outer join returns all rows in the left table (A) and all the matching rows found in right table (B). It means the result of the SQL left join always contains the rows in the left table.

The following illustrate SQL left outer syntax of joining 2 tables: table_A and table_B:

SQL OUTER JOIN – left outer join example

The following query selects all customers and their orders:

SQL OUTER JOIN - LEFT JOIN Example

All rows in the customers table are listed. In case, there is no matching row in the orders table found for the row in the customers table, the orderid column in the orders table is populated with NULL values.

We can use Venn diagram to visualize how SQL LEFT OUTER JOIN works.

SQL Left Outer Join - Venn Diagram

SQL OUTER JOIN – right outer join

SQL right outer join returns all rows in the right table and all the matching rows found in left table. The syntax of the SQL right outer join is as follows:

SQL right outer join is also known as SQL right join.

SQL OUTER JOIN – right outer join example

The following example demonstrates the SQL right outer join:

The query returns all rows in the orders table and all matching rows found in the customers table.

The following Venn diagram illustrates how the SQL right outer join works:

SQL right outer join - Venn Diagram

SQL OUTER JOIN – full outer join

The syntax of the SQL full outer join is as follows:

SQL full outer join returns:

  • all rows in the left table table_A.
  • all rows in the right table table_B.
  • and all matching rows in both tables.

Some database management systems do not support SQL full outer join syntax e.g., MySQL. Because SQL full outer join returns a result set that is a combined result of both SQL left join and SQL right join. Therefore you can easily emulates the SQL full outer join using SQL left join and SQL right join with UNION operator as follows:

SQL OUTER JOIN -  full outer join example

The following query demonstrates the SQL full outer join:

SQL OUTER JOIN - full outer join example

The following Venn diagram illustrates how SQL full outer join works:

SQL full outer join - Venn Diagram

In this tutorial, you’ve learned about various SQL OUTER JOIN including SQL left join, SQL right join and SQL full outer join.