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 to 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 the 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:

SELECT column1, column2... FROM table_A LEFT JOIN table_B ON join_condition WHERE row_condition

SQL OUTER JOIN – left outer join example

The following query selects all customers and their orders:

SELECT c.customerid, c.companyName, orderid FROM customers c LEFT JOIN orders o ON o.customerid = c.customerid ORDER BY orderid
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 the left table. The syntax of the SQL right outer join is as follows:

SELECT column1, column2... FROM table_A RIGHT JOIN table_B ON join_condition WHERE row_condition

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:

SELECT c.customerid, c.companyName, orderid FROM customers c RIGHT JOIN orders o ON o.customerid = c.customerid ORDER BY orderid

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:

SELECT column1, column2... FROM table_A FULL OUTER JOIN table_B ON join_condition WHERE row_condition

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 emulate the SQL full outer join using SQL left join and SQL right join with UNION operator as follows:

SELECT column1, column2... FROM table_A LEFT JOIN table_B ON join_condition UNION SELECT column1, column2... FROM table_A RIGHT JOIN table_B ON join_condition

SQL OUTER JOIN –  full outer join example

The following query demonstrates the SQL full outer join:

SELECT c.customerid, 
       c.companyName,
       orderid
FROM customers c
FULL OUTER JOIN orders o ON o.customerid = c.customerid
ORDER BY orderid
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.