SQL JOIN for Beginners Part 3: LEFT OUTER JOIN

What is a LEFT OUTER JOIN (LEFT JOIN)?

In SQL, the JOIN clause is used to combine data from two or more tables into a single result set.
Among them, LEFT OUTER JOIN (often shortened to LEFT JOIN) is one of the most commonly used types of JOINs in real-world projects.

The concept is straightforward:
Retrieve all rows from the left table, and if there’s a matching row in the right table, bring that data too. If there’s no match, fill it with NULL.

Why use LEFT JOIN?

In practice, we rarely need all rows from both tables.
For example, if you have a customer table and an orders table, you might want to display "all customers" along with "their orders".

If a customer has no orders, you still want them to appear in the list — in this case, LEFT JOIN is the right choice.

Basic LEFT JOIN Example

Let’s start with a simple dataset:

A   |   B
-------------
a       c
b       d
e       e
f       f

Performing a LEFT JOIN with A as the left table:

A   |   B
-------------
a       NULL
b       NULL
e       e
f       f

• a and b have no matches in B, so they get NULL.

• e and f match values in B, so they’re paired together.

Real Example – Computer and Software Tables

Let’s use a more realistic scenario:
We will use the tables we created last time to look at a real example.

• computer: Stores basic computer information

• software: Stores software installed on each computer

Goal: Show every computer and the software installed on it, even if some computers have no software installed.

SELECT *
FROM computer
LEFT JOIN software
ON computer.software_id = software.software_id;

Understanding the Results

• The LEFT table (computer) provides all rows in the output.

• If a computer_id (e.g., 7 or 8) doesn’t exist in software, the software-related columns will be NULL.

• If both tables have a column with the same name (like software_id), it will appear twice in the result set. Use aliases (AS) to rename them if needed.

This is the result obtained by running the actual query.

Selecting Specific Columns Instead of Using SELECT *

While SELECT * is convenient, it’s generally a good practice to explicitly specify the columns you need in your query.

Example:

SELECT 
    computer.computer_id, 
    computer.computer_name, 
    computer.manufacturer, 
    computer.software_id, 
    software.software_name
FROM computer
LEFT JOIN software
ON computer.software_id = software.software_id;

This has two main benefits:

1. Avoiding Duplicate Columns – If both tables have columns with the same name (e.g., software_id), they will appear twice in the results. By explicitly selecting the columns you want, you can prevent this duplication.

2. Improved Performance and Readability – Fetching only the required columns reduces the amount of data transferred and makes the result set easier to read.

LEFT JOIN on Multiple Tables

You can chain multiple LEFT JOINs to combine data from more than two tables.
For example, if we also have an operating_system table that stores OS information, we can join it after the software table:

SELECT *
FROM computer
LEFT JOIN software
    ON computer.software_id = software.software_id
LEFT JOIN operating_system
    ON software.os_id = operating_system.os_id;

How it works:

1. The first LEFT JOIN merges computer with software, keeping all computers and adding software details where available.

2. The second LEFT JOIN merges that result with operating_system, keeping all rows from the previous join and adding OS details where they exist.

3. If a computer has no software, the software columns will be NULL, and so will the OS columns.

When to Use Multi-Table LEFT JOIN

• To build comprehensive reports combining multiple related datasets.

• When you want to ensure no loss of the main table’s data even if secondary or tertiary tables have missing entries.

• To gradually enrich your data step-by-step while still keeping unmatched data.

---

How is RIGHT OUTER JOIN Different?

RIGHT OUTER JOIN works exactly like LEFT OUTER JOIN but uses the right table as the base.
In practice, LEFT JOIN is far more common.

If you reverse the table order in a LEFT JOIN, you’ll get the same result as a RIGHT JOIN.

Summary

• LEFT JOIN → Keep all rows from the left table, fill unmatched right table values with NULL.

• Great for showing “all main data + related optional data”.

• Most frequently used JOIN in SQL queries.

• RIGHT JOIN is rare but equivalent to reversing a LEFT JOIN’s table order.

👉Check out the JOIN SQL queries example here

👉Check out the spreadsheet JOIN example here