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

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 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 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

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

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