LEFT OUTER JOIN – Join Multiple Tables in SQL

SQL
Jul 29 2023
LEFT OUTER JOIN is a type of OUTER JOIN which fetches you all the rows from the LEFT table and matching rows from the RIGHT table based on the provided ON condition.

Table of Contents

Overview Left Outer Join

LEFT OUTER JOIN is a type of OUTER JOIN which fetches you all the rows from the LEFT table and matching rows from the RIGHT table based on the provided ON condition. It means that rows from the LEFT table are always fetched and columns (from the RIGHT table) where matching data is not available, NULL will be fetched. LEFT OUTER JOIN is also written as LEFT JOIN.

For convenience, we will use the same 3 tables as defined in the previous post SQL JOIN Explained with examples.

LEFT OUTER JOIN with 2 tables

Let’s try to join 2 tables first i.e. customer and orders.

SELECT 
    c.customer_id, c.name, o.order_id 
FROM 
    customer c 
        LEFT JOIN 
    orders o ON c.customer_id = o.customer_id;

Result

+-------------+---------+----------+
| customer_id | name    | order_id |
+-------------+---------+----------+
|         100 | Michael |       11 |
|         100 | Michael |       10 |
|         101 | Robert  |       12 |
|         102 | John    |     NULL |
|         103 | Grey    |       13 |
+-------------+---------+----------+
5 rows in set (0.00 sec)

Observation

  • In the query customer is LEFT table and orders is RIGHT table.
  • All the rows from customer have been fetched and only matching rows from orders have been fetched.
  • Since customer “John” does not have an order, its order_id is showing up as NULL.
  • Customer “Michael” has 2 orders hence 2 rows for “Michael”.

LEFT OUTER JOIN with 3 tables

Lets join the 3rd table also i.e. invoice.

SELECT 
    c.customer_id, c.name, o.order_id, i.invoice_id 
FROM 
    customer c 
        LEFT JOIN 
    orders o ON c.customer_id = o.customer_id 
        LEFT JOIN 
    invoice i ON o.order_id = i.order_id;

Result

+-------------+---------+----------+------------+
| customer_id | name    | order_id | invoice_id |
+-------------+---------+----------+------------+
|         100 | Michael |       11 |          1 |
|         100 | Michael |       10 |       NULL |
|         101 | Robert  |       12 |          2 |
|         102 | John    |     NULL |       NULL |
|         103 | Grey    |       13 |          3 |
+-------------+---------+----------+------------+
5 rows in set (0.00 sec)

Observation

  • Rows are fetching from all 3 tables.
  • Since we already have result of joining 2 tables, we can see that this result has played the role of LEFT and invoice is the RIGHT table.
  • All the rows from LEFT table are fetching, but only matching rows are coming from RIGHT table i.e. invoice. where matching data is not found, NULL is showing up.
  • Customer “Michael” has 2 orders but invoice is available for 1 order only (order_id = 11).

This is how JOIN works for multiple tables. You can join 4th, 5th or as many tables as you want but principle remains the same for all. Remember that for (n) number of joining tables there will be (n-1) JOIN conditions.

For this example MySQL has been used.