SQL LEFT JOIN: Keep All Rows from the Left Table (Examples)

SQL Tutorial · Chapter 21 of 32

The SQL LEFT JOIN returns every row from the left (first) table together with the matching rows from the right table. When a left row has no match, the right-hand columns are filled with NULL instead of the row being dropped. LEFT JOIN is the tool for questions like “show all customers, including those who have never ordered”.

SQL LEFT JOIN Syntax

SELECT columns
FROM left_table
LEFT JOIN right_table
    ON left_table.key = right_table.key;

LEFT OUTER JOIN is the same thing; the word OUTER is optional. The table written first, after FROM, is the one whose rows are all kept.

Sample Tables

Example 1: All Customers and Their Orders

SELECT c.Name, o.OrderID, o.Product, o.Amount
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
ORDER BY c.CustomerID, o.OrderID;

Six rows: the five matches an INNER JOIN would give, plus Emma Brown with NULLs. Order 105 does not appear because its customer is not in the left table.

Example 2: Find Customers with No Orders

The classic LEFT JOIN trick: keep only the rows where the right side is NULL.

SELECT c.Name, c.Country
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL;

Example 3: Count Orders per Customer, Including Zero

COUNT(o.OrderID) counts non-NULL values, so customers without orders correctly show 0. COUNT(*) would show 1 for Emma Brown.

SELECT c.Name, COUNT(o.OrderID) AS Orders, COALESCE(SUM(o.Amount), 0) AS Total
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
GROUP BY c.Name
ORDER BY Total DESC;

Example 4: Filter in ON, Not WHERE

To list all customers with only their laptop orders, the product test must go in ON. In WHERE it would remove every customer without a laptop order, turning the LEFT JOIN into an INNER JOIN.

SELECT c.Name, o.OrderID
FROM Customers c
LEFT JOIN Orders o
    ON o.CustomerID = c.CustomerID AND o.Product = 'Laptop'
ORDER BY c.CustomerID;

Works in MySQL, SQL Server, PostgreSQL, SQLite

• LEFT JOIN syntax and behaviour are identical in all four systems.
• MySQL and SQLite lack RIGHT JOIN in older versions (SQLite added it in 3.39), so LEFT JOIN with the tables swapped is the portable way to write any outer join.
• Old Oracle syntax WHERE c.CustomerID = o.CustomerID(+) and SQL Server’s *= are obsolete; use the ANSI form shown here.

When to Choose LEFT JOIN

Reach for LEFT JOIN whenever the second table’s data is optional: customers with or without orders, products with or without reviews, employees with or without a manager. If the result must contain only complete pairs, INNER JOIN is both simpler and faster. A quick test: if you would be surprised to see NULLs in the right-hand columns, you want INNER JOIN.

Common Mistakes

• Putting a right-table condition in WHERE and losing the unmatched rows.
• Using COUNT(*) instead of COUNT(right_column) and getting 1 for customers with no orders.
• Choosing the wrong table as the left one; the table after FROM is the one fully preserved.
• Summing a right-table column without COALESCE and showing NULL instead of 0.

Practice Exercise

1. List every customer’s Name with the date of their most recent order (NULL if none).
2. How many customers have never ordered a Laptop?

-- 1
SELECT c.Name, MAX(o.OrderDate) AS LastOrder
FROM Customers c LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
GROUP BY c.Name;

-- 2
SELECT COUNT(*) AS NoLaptop
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID AND o.Product = 'Laptop'
WHERE o.OrderID IS NULL;
-- 3 (John Smith, Maria Garcia, Emma Brown)

Related Chapters

• SQL INNER JOIN – matched rows only
• SQL RIGHT JOIN – the mirror image of LEFT JOIN
• SQL NULL Values – handling the NULLs a LEFT JOIN creates
• SQL from Scratch: full course overview

FAQ

What is the difference between LEFT JOIN and INNER JOIN?

INNER JOIN returns only matching rows. LEFT JOIN returns every row from the left table as well, filling the right-hand columns with NULL where there is no match.

Is LEFT JOIN the same as LEFT OUTER JOIN?

Yes. OUTER is an optional keyword; both spellings produce identical results in every major database.

How do I find rows in one table that are missing from another?

LEFT JOIN the second table and add WHERE second_table.key IS NULL. Only the rows without a match survive.

Chapter 21 of 32 · SQL from Scratch: all 32 chapters