SQL LEFT JOIN and RIGHT JOIN to Include Unmatched Rows
In the previous guide, you learned that INNER JOIN returns only rows with matches in both tables. This is perfect when you want to see orders with their customers, or products with their categories. But what happens when you need the opposite perspective? What about customers who have never placed an order? Products with no reviews? Categories with no products assigned?
INNER JOIN silently drops these unmatched rows. It cannot show you what is missing, only what is present. To find gaps, absences, and unmatched records, you need outer joins.
LEFT JOIN keeps every row from the left table, even if there is no match in the right table. RIGHT JOIN does the same but for the right table. These operations are essential for finding missing data, generating complete reports, and answering "who has not" and "what does not" questions.
This guide covers both LEFT JOIN and RIGHT JOIN in depth: how they work, how they differ from INNER JOIN, why LEFT JOIN dominates real-world usage, and the powerful pattern of finding unmatched rows. Every example uses the ShopSmart sample database with full outputs (we defined it in a previous guide here, and then we extended it here).
The Problem: INNER JOIN Hides Missing Data
Let us revisit a query from the previous guide that counts orders per customer:
SELECT c.first_name || ' ' || c.last_name AS customer,
COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY order_count DESC;
Output:
| customer | order_count |
|---|---|
| Alice Johnson | 3 |
| Bob Martinez | 3 |
| Carol Singh | 2 |
| David Chen | 2 |
| Eva Brown | 1 |
| Frank Wilson | 1 |
Six customers appear. But the customers table has seven customers. Grace Taylor is completely absent because she has no orders. The INNER JOIN found no matching rows in the orders table for Grace, so it excluded her entirely.
For many reports, this is exactly what you want. But if you are building a customer management dashboard that shows all customers and their order activity, Grace's absence creates a blind spot. You need her to appear with an order count of 0.
This is the problem LEFT JOIN solves.
LEFT JOIN Explained
A LEFT JOIN (also written as LEFT OUTER JOIN) returns all rows from the left table, regardless of whether they have a match in the right table. For rows that do have matches, it works exactly like an INNER JOIN, combining columns from both tables. For rows with no match, it fills in NULL for every column from the right table.
SELECT columns
FROM left_table
LEFT JOIN right_table ON left_table.column = right_table.column;
The "left" table is the one in the FROM clause. The "right" table is the one after LEFT JOIN.
Your First LEFT JOIN
SELECT c.first_name || ' ' || c.last_name AS customer,
o.id AS order_id,
o.order_date,
o.total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
ORDER BY c.last_name, o.order_date;
Output:
| customer | order_id | order_date | total_amount |
|---|---|---|---|
| Eva Brown | 6 | 2024-03-20 | 34.99 |
| David Chen | 5 | 2024-03-12 | 155.00 |
| David Chen | 12 | 2024-04-25 | 45.00 |
| Alice Johnson | 1 | 2024-01-10 | 119.98 |
| Alice Johnson | 3 | 2024-02-20 | 77.49 |
| Alice Johnson | 9 | 2024-04-15 | 89.99 |
| Bob Martinez | 2 | 2024-01-15 | 89.99 |
| Bob Martinez | 7 | 2024-04-01 | 63.00 |
| Bob Martinez | 11 | 2024-04-22 | 174.99 |
| Carol Singh | 4 | 2024-03-05 | 129.99 |
| Carol Singh | 10 | 2024-04-18 | 63.99 |
| Grace Taylor | NULL | NULL | NULL |
| Frank Wilson | 8 | 2024-04-10 | 199.98 |
Grace Taylor now appears in the results. She has no matching orders, so order_id, order_date, and total_amount are all NULL. Every other customer appears with their actual order data, just like an INNER JOIN.
Visualizing LEFT JOIN
┌───────────────┐ ┌───────────────┐
│ │ │ │
│ customers │ │ orders │
│ ┌──┼─────┼──┐ │
│ Grace │ │ │ │ │
│ (no │ matched│ │ │
│ orders) │ rows │ │ │
│ │ │ │ │
│ INCLUDED │ INCLUDED │ excluded │
│ └──┼─────┼──┘ │
│ │ │ │
└───────────────┘ └───────────────┘
◄────── LEFT JOIN returns this area ──────►
- Left circle entirely (all customers): Every customer appears, with or without orders
- Intersection (customers with orders): Matched data from both tables
- Right circle only (orders without matching customers): Not applicable with foreign key constraints, but would be excluded
The key difference from INNER JOIN: the left circle only area (customers without orders) is now included instead of discarded.
LEFT JOIN with COUNT: The Complete Picture
Now let us fix the original counting query:
SELECT c.first_name || ' ' || c.last_name AS customer, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY order_count DESC;
Output:
| customer | order_count |
|---|---|
| Alice Johnson | 3 |
| Bob Martinez | 3 |
| Carol Singh | 2 |
| David Chen | 2 |
| Eva Brown | 1 |
| Frank Wilson | 1 |
| Grace Taylor | 0 |
Grace now appears with an order count of 0. All seven customers are accounted for.
This distinction is critical when using LEFT JOIN with aggregation:
-- Wrong: COUNT(*) counts the row itself, which exists for Grace (it is just filled with NULLs)
SELECT c.first_name, COUNT(*) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name;
-- Grace shows order_count = 1 (wrong!)
-- Correct: COUNT(o.id) counts non-NULL values in the orders table
SELECT c.first_name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name;
-- Grace shows order_count = 0 (correct!)
When using LEFT JOIN with COUNT, always count a column from the right table (like o.id), not *. COUNT(*) counts every row including those filled with NULLs, while COUNT(o.id) correctly counts only actual matches.
NULL Values in LEFT JOIN Results
Understanding how NULLs appear in LEFT JOIN results is essential for writing correct queries.
When a row from the left table has no match, every column from the right table is filled with NULL:
SELECT c.first_name,
c.last_name,
o.id AS order_id,
o.order_date,
o.total_amount,
o.status
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.first_name = 'Grace';
Output:
| first_name | last_name | order_id | order_date | total_amount | status |
|---|---|---|---|---|---|
| Grace | Taylor | NULL | NULL | NULL | NULL |
Grace's own columns (first_name, last_name) have their real values. But every column from the orders table is NULL because no matching order exists.
This NULL behavior becomes the foundation for one of the most important SQL patterns: finding unmatched rows.
Finding Unmatched Rows: The IS NULL Pattern
One of the most powerful uses of LEFT JOIN is finding rows in the left table that have no corresponding match in the right table. The pattern combines LEFT JOIN with a WHERE check for NULL on the right table's primary key.
Customers Who Have Never Ordered
SELECT c.first_name || ' ' || c.last_name AS customer,
c.email,
c.signup_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
Output:
| customer | signup_date | |
|---|---|---|
| Grace Taylor | grace@email.com | 2024-04-01 |
The logic works like this:
LEFT JOINincludes all customers, even those with no orders- For customers without orders,
o.idisNULL WHERE o.id IS NULLfilters to only those unmatched customers
This pattern is invaluable for business questions like:
- Which customers should we send a first-purchase promotion?
- Which products have never been reviewed?
- Which categories have no products?
Products That Have Never Been Ordered
SELECT p.name AS product,
p.price,
p.stock_quantity
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;
Output:
| product | price | stock_quantity |
|---|---|---|
| Data Science Handbook | 42.50 | 30 |
| Bluetooth Speaker | 65.00 | 0 |
Two products sit in the catalog but have never appeared in any order. This insight could trigger a marketing push or a pricing review.
Products With No Reviews
SELECT p.name AS product,
p.price,
cat.name AS category
FROM products p
INNER JOIN categories cat ON p.category_id = cat.id
LEFT JOIN reviews r ON p.id = r.product_id
WHERE r.id IS NULL;
Output:
| product | price | category |
|---|---|---|
| Bluetooth Speaker | 65.00 | Electronics |
Only the Bluetooth Speaker has no reviews. Notice how this query uses both INNER JOIN (to get category names, since every product has a category) and LEFT JOIN (to check for reviews, which might not exist).
Categories with No Products
-- First, let's add an empty category for demonstration
INSERT INTO categories VALUES (5, 'Toys', 'Toys and games for all ages');
SELECT cat.name AS category,
cat.description
FROM categories cat
LEFT JOIN products p ON cat.id = p.category_id
WHERE p.id IS NULL;
Output:
| category | description |
|---|---|
| Toys | Toys and games for all ages |
The new "Toys" category exists but has no products assigned to it.
-- Template for finding unmatched rows:
SELECT left_table.columns
FROM left_table
LEFT JOIN right_table ON left_table.key = right_table.foreign_key
WHERE right_table.primary_key IS NULL;
This pattern answers any "which X has no Y" question:
- Customers with no orders
- Products with no reviews
- Categories with no products
- Employees with no assigned projects
- Students not enrolled in any course
LEFT JOIN with Aggregation
LEFT JOIN combined with aggregate functions produces complete summaries that include entities with zero counts or zero totals.
Complete Customer Spending Report
SELECT c.first_name || ' ' || c.last_name AS customer,
c.city,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS total_spent,
COALESCE(ROUND(AVG(o.total_amount), 2), 0) AS avg_order_value
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.city
ORDER BY total_spent DESC;
Output:
| customer | city | order_count | total_spent | avg_order_value |
|---|---|---|---|---|
| Bob Martinez | Los Angeles | 3 | 327.98 | 109.33 |
| Alice Johnson | New York | 3 | 287.46 | 95.82 |
| David Chen | New York | 2 | 200 | 100 |
| Frank Wilson | Chicago | 1 | 199.98 | 199.98 |
| Carol Singh | Chicago | 2 | 193.98000000000002 | 96.99 |
| Eva Brown | Seattle | 1 | 34.99 | 34.99 |
| Grace Taylor | null | 0 | 0 | 0 |
COALESCE is essential here. Without it, Grace's total_spent and avg_order_value would be NULL instead of 0:
-- Without COALESCE: Grace gets NULLs
SELECT c.first_name,
SUM(o.total_amount) AS total_spent -- NULL for Grace
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name;
-- With COALESCE: Grace gets 0
SELECT c.first_name,
COALESCE(SUM(o.total_amount), 0) AS total_spent -- 0 for Grace
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name;
Complete Category Report
SELECT cat.name AS category,
COUNT(p.id) AS product_count,
COALESCE(ROUND(AVG(p.price), 2), 0) AS avg_price,
COALESCE(SUM(p.stock_quantity), 0) AS total_stock
FROM categories cat
LEFT JOIN products p ON cat.id = p.category_id
GROUP BY cat.id, cat.name
ORDER BY product_count DESC;
Output:
| category | product_count | avg_price | total_stock |
|---|---|---|---|
| Electronics | 4 | 57.49 | 425 |
| Sports | 3 | 57.66 | 340 |
| Books | 2 | 38.75 | 80 |
| Home & Kitchen | 1 | 129.99 | 25 |
| Toys | 0 | 0 | 0 |
The Toys category appears with zeros. An INNER JOIN would have hidden it completely.
Product Review Summary
SELECT p.name AS product,
p.price,
COUNT(r.id) AS review_count,
COALESCE(ROUND(AVG(r.rating), 2), 0) AS avg_rating,
CASE
WHEN COUNT(r.id) = 0 THEN 'No Reviews'
WHEN AVG(r.rating) >= 4.5 THEN 'Excellent'
WHEN AVG(r.rating) >= 4.0 THEN 'Good'
WHEN AVG(r.rating) >= 3.0 THEN 'Average'
ELSE 'Poor'
END AS rating_label
FROM products p
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name, p.price
ORDER BY avg_rating DESC, review_count DESC;
Output:
| product | price | review_count | avg_rating | rating_label |
|---|---|---|---|---|
| Wireless Mouse | 29.99 | 3 | 4.67 | Excellent |
| Running Shoes X1 | 110.00 | 3 | 4.67 | Excellent |
| USB-C Hub | 45.00 | 2 | 4.50 | Excellent |
| SQL for Beginners | 34.99 | 2 | 4.50 | Excellent |
| Coffee Maker Pro | 129.99 | 2 | 4.50 | Excellent |
| Yoga Mat Premium | 38.00 | 2 | 4.50 | Excellent |
| Stainless Water Bottle | 24.99 | 3 | 4.33 | Good |
| Mechanical Keyboard | 89.99 | 3 | 4.00 | Good |
| Data Science Handbook | 42.50 | 1 | 3.00 | Average |
| Bluetooth Speaker | 65.00 | 0 | 0 | No Reviews |
Every product appears, including the Bluetooth Speaker with zero reviews.
RIGHT JOIN Explained
A RIGHT JOIN (also written as RIGHT OUTER JOIN) is the mirror image of LEFT JOIN. It returns all rows from the right table, regardless of whether they have matches in the left table. Unmatched rows from the right table get NULL values for the left table's columns.
SELECT columns
FROM left_table
RIGHT JOIN right_table ON left_table.column = right_table.column;
RIGHT JOIN Example
SELECT o.id AS order_id,
o.order_date,
c.first_name || ' ' || c.last_name AS customer
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id
ORDER BY c.last_name;
Output:
| order_id | order_date | customer |
|---|---|---|
| 6 | 2024-03-20 | Eva Brown |
| 5 | 2024-03-12 | David Chen |
| 12 | 2024-04-25 | David Chen |
| 1 | 2024-01-10 | Alice Johnson |
| 3 | 2024-02-20 | Alice Johnson |
| 9 | 2024-04-15 | Alice Johnson |
| 2 | 2024-01-15 | Bob Martinez |
| 7 | 2024-04-01 | Bob Martinez |
| 11 | 2024-04-22 | Bob Martinez |
| 4 | 2024-03-05 | Carol Singh |
| 10 | 2024-04-18 | Carol Singh |
| NULL | NULL | Grace Taylor |
| 8 | 2024-04-10 | Frank Wilson |
Grace appears with NULL order data. The right table (customers) is fully preserved.
Visualizing RIGHT JOIN
┌───────────────┐ ┌───────────────┐
│ │ │ │
│ orders │ │ customers │
│ ┌──┼─────┼──┐ │
│ │ │ │ │ Grace │
│ excluded │ matched│ │ (no │
│ │ rows │ │ orders) │
│ │ │ │ │
│ │ INCLUDED │ INCLUDED │
│ └──┼─────┼──┘ │
│ │ │ │
└───────────────┘ └───────────────┘
◄──── RIGHT JOIN returns this area ────►
Why LEFT JOIN Is More Common Than RIGHT JOIN
In practice, you will see LEFT JOIN far more often than RIGHT JOIN. Here is why:
Any RIGHT JOIN Can Be Rewritten as a LEFT JOIN
Every RIGHT JOIN query can be rewritten as a LEFT JOIN by simply swapping the table order:
-- RIGHT JOIN: Keep all customers
SELECT o.id, c.first_name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;
-- Equivalent LEFT JOIN: Same result, just swap the tables
SELECT o.id, c.first_name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
Both queries produce identical results. The LEFT JOIN version is more natural to read: "Start with all customers, then look for their orders."
Reading Direction
Most people read SQL from top to bottom and left to right. LEFT JOIN keeps the "primary" table (the one you want all rows from) at the start of the query, which matches the natural reading flow:
-- Natural: "Start with customers, find their orders"
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
-- Less natural: "Start with orders, but actually keep all customers"
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id
Industry Convention
The SQL community has converged on using LEFT JOIN almost exclusively. Using RIGHT JOIN is not wrong, but it makes your code less familiar to other developers. Some style guides explicitly discourage RIGHT JOIN.
Always use LEFT JOIN. If you find yourself reaching for RIGHT JOIN, swap the table order and use LEFT JOIN instead. Your queries will be more consistent and more readable.
-- Instead of RIGHT JOIN:
FROM table_a RIGHT JOIN table_b ON ...
-- Use LEFT JOIN with swapped tables:
FROM table_b LEFT JOIN table_a ON ...
LEFT JOIN vs INNER JOIN: Side by Side
Let us see the exact difference between the two join types with the same tables:
INNER JOIN Result
SELECT c.first_name || ' ' || c.last_name AS customer,
COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY order_count DESC;
| customer | order_count |
|---|---|
| Alice Johnson | 3 |
| Bob Martinez | 3 |
| David Chen | 2 |
| Carol Singh | 2 |
| Frank Wilson | 1 |
| Eva Brown | 1 |
6 rows. Grace is missing.
LEFT JOIN Result
SELECT c.first_name || ' ' || c.last_name AS customer,
COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY order_count DESC;
| customer | order_count |
|---|---|
| Alice Johnson | 3 |
| Bob Martinez | 3 |
| David Chen | 2 |
| Carol Singh | 2 |
| Frank Wilson | 1 |
| Eva Brown | 1 |
| Grace Taylor | 0 |
7 rows. Grace appears with 0 orders.
When to Use Which
| Scenario | Use | Why |
|---|---|---|
| Show only customers who have placed orders | INNER JOIN | You only want matched data |
| Show ALL customers, including those without orders | LEFT JOIN | You need the complete picture |
| Find customers who have NEVER ordered | LEFT JOIN + WHERE IS NULL | Only outer joins can reveal missing data |
| Join products with categories (every product has one) | INNER JOIN | No unmatched rows expected |
| Show products with optional review data | LEFT JOIN | Not every product has reviews |
If the relationship is mandatory (every order has a customer, every product has a category), INNER JOIN is fine. If the relationship is optional (not every customer has orders, not every product has reviews), use LEFT JOIN to preserve all rows from the primary table.
Multiple LEFT JOINs
You can chain multiple LEFT JOIN operations to preserve rows through several optional relationships:
Customer Dashboard with Orders and Reviews
SELECT c.first_name || ' ' || c.last_name AS customer,
COUNT(DISTINCT o.id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS total_spent,
COUNT(DISTINCT r.id) AS review_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN reviews r ON c.id = r.customer_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spent DESC;
Output:
| customer | order_count | total_spent | review_count |
|---|---|---|---|
| Alice Johnson | 3 | 1437.3 | 5 |
| Bob Martinez | 3 | 1311.92 | 4 |
| Carol Singh | 2 | 775.9200000000001 | 4 |
| David Chen | 2 | 600 | 3 |
| Frank Wilson | 1 | 399.96 | 2 |
| Eva Brown | 1 | 104.97 | 3 |
| Grace Taylor | 0 | 0 | 0 |
Grace appears with zeros across all metrics. Both LEFT JOIN operations preserve her row.
When you LEFT JOIN to multiple tables, rows can multiply due to the cross-product effect. Using COUNT(DISTINCT o.id) instead of COUNT(o.id) prevents inflated counts.
-- Problem: Without DISTINCT, order counts get inflated
-- If Alice has 3 orders and 5 reviews, each order gets repeated 5 times
-- COUNT(o.id) would return 15 instead of 3
-- Solution: COUNT(DISTINCT o.id) counts unique orders only
COUNT(DISTINCT o.id) AS order_count
Complete Product Overview
SELECT p.name AS product,
cat.name AS category,
p.price,
p.stock_quantity AS stock,
COUNT(DISTINCT oi.id) AS times_ordered,
COALESCE(SUM(oi.quantity), 0) AS units_sold,
COUNT(DISTINCT r.id) AS review_count,
COALESCE(ROUND(AVG(r.rating), 1), 0) AS avg_rating
FROM products p
INNER JOIN categories cat ON p.category_id = cat.id
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name, cat.name, p.price, p.stock_quantity
ORDER BY units_sold DESC, avg_rating DESC;
Output:
| product | category | price | stock | times_ordered | units_sold | review_count | avg_rating |
|---|---|---|---|---|---|---|---|
| Mechanical Keyboard | Electronics | 89.99 | 75 | 4 | 12 | 3 | 4 |
| Running Shoes X1 | Sports | 110 | 60 | 3 | 9 | 3 | 4.7 |
| Stainless Water Bottle | Sports | 24.99 | 180 | 3 | 9 | 3 | 4.3 |
| Wireless Mouse | Electronics | 29.99 | 150 | 1 | 6 | 3 | 4.7 |
| USB-C Hub | Electronics | 45 | 200 | 3 | 6 | 2 | 4.5 |
| SQL for Beginners | Books | 34.99 | 50 | 2 | 4 | 2 | 4.5 |
| Yoga Mat Premium | Sports | 38 | 100 | 2 | 4 | 2 | 4.5 |
| Coffee Maker Pro | Home & Kitchen | 129.99 | 25 | 1 | 2 | 2 | 4.5 |
| Data Science Handbook | Books | 42.5 | 30 | 0 | 0 | 1 | 3 |
| Bluetooth Speaker | Electronics | 65 | 0 | 0 | 0 | 0 | 0 |
Notice the mix of join types: INNER JOIN for categories (every product has one) and LEFT JOIN for order items and reviews (which might not exist).
WHERE Clause Placement with LEFT JOIN
The placement of WHERE conditions in a LEFT JOIN query has a critical impact on results. Getting this wrong is one of the most common LEFT JOIN mistakes.
The Trap: WHERE Turns LEFT JOIN into INNER JOIN
-- Intention: Show all customers, but only their completed orders
-- WRONG: This filters out Grace (and anyone without completed orders)
SELECT c.first_name || ' ' || c.last_name AS customer,
o.order_date,
o.status
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed';
Output:
| customer | order_date | status |
|---|---|---|
| Alice Johnson | 2024-01-10 | completed |
| Bob Martinez | 2024-01-15 | completed |
| Alice Johnson | 2024-02-20 | completed |
| Alice Johnson | 2024-04-15 | completed |
| Carol Singh | 2024-04-18 | completed |
Grace is gone. So are David, Eva, and Frank (they have no completed orders). The WHERE o.status = 'completed' condition eliminates any row where o.status is NULL, which includes all the unmatched LEFT JOIN rows. The LEFT JOIN effectively becomes an INNER JOIN.
The Fix: Move the Condition to the ON Clause
To filter the right table without eliminating unmatched left rows, place the condition in the ON clause:
-- CORRECT: Show all customers, with completed orders if they have any
SELECT c.first_name || ' ' || c.last_name AS customer,
o.order_date,
o.status
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed'
ORDER BY customer;
Output:
| customer | order_date | status |
|---|---|---|
| Alice Johnson | 2024-01-10 | completed |
| Alice Johnson | 2024-02-20 | completed |
| Alice Johnson | 2024-04-15 | completed |
| Bob Martinez | 2024-01-15 | completed |
| Carol Singh | 2024-04-18 | completed |
| David Chen | NULL | NULL |
| Eva Brown | NULL | NULL |
| Frank Wilson | NULL | NULL |
| Grace Taylor | NULL | NULL |
Now all customers appear. Those with completed orders show the details. Those without completed orders (including Grace who has no orders at all, and David/Eva/Frank who have orders but none completed) show NULL.
Aggregated Version
SELECT c.first_name || ' ' || c.last_name AS customer,
COUNT(o.id) AS completed_orders,
COALESCE(SUM(o.total_amount), 0) AS completed_revenue
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed'
GROUP BY c.id, c.first_name, c.last_name
ORDER BY completed_revenue DESC;
Output:
| customer | completed_orders | completed_revenue |
|---|---|---|
| Alice Johnson | 3 | 287.46 |
| Bob Martinez | 1 | 89.99 |
| Carol Singh | 1 | 63.99 |
| David Chen | 0 | 0 |
| Eva Brown | 0 | 0 |
| Frank Wilson | 0 | 0 |
| Grace Taylor | 0 | 0 |
When using LEFT JOIN, any filter on the right table must go in the ON clause, not the WHERE clause. Putting it in WHERE converts the LEFT JOIN into an INNER JOIN by eliminating rows with NULLs.
-- WHERE on right table: Destroys the LEFT JOIN effect
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed' -- NULLs filtered out, Grace disappears
-- ON clause condition: Preserves the LEFT JOIN effect
LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed'
-- Grace kept with NULLs, only completed orders matched
Filters on the left table are fine in WHERE because left table rows are always preserved:
-- This is fine: Filtering the LEFT table in WHERE
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.city = 'New York' -- Only New York customers, but still LEFT JOINed
Real-World LEFT JOIN Patterns
Customer Engagement Report
SELECT c.first_name || ' ' || c.last_name AS customer,
c.signup_date,
COUNT(DISTINCT o.id) AS orders,
COALESCE(SUM(o.total_amount), 0) AS revenue,
COUNT(DISTINCT r.id) AS reviews,
CASE
WHEN COUNT(o.id) = 0 AND COUNT(r.id) = 0 THEN 'Inactive'
WHEN COUNT(o.id) = 0 THEN 'Reviewer Only'
WHEN COUNT(o.id) >= 3 THEN 'Highly Active'
WHEN COUNT(o.id) >= 2 THEN 'Active'
ELSE 'New Buyer'
END AS engagement_level
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN reviews r ON c.id = r.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.signup_date
ORDER BY revenue DESC;
Output:
| customer | signup_date | orders | rvenue | reviews | engagement_level |
|---|---|---|---|---|---|
| Alice Johnson | 2023-01-15 | 3 | 1437.3 | 5 | Highly Active |
| Bob Martinez | 2023-03-22 | 3 | 1311.92 | 4 | Highly Active |
| Carol Singh | 2023-06-10 | 2 | 775.9200000000001 | 4 | Highly Active |
| David Chen | 2023-08-05 | 2 | 600 | 3 | Highly Active |
| Frank Wilson | 2024-02-28 | 1 | 399.96 | 2 | Active |
| Eva Brown | 2024-01-18 | 1 | 104.97 | 3 | Highly Active |
| Grace Taylor | 2024-04-01 | 0 | 0 | 0 | Inactive |
Inventory Gap Analysis
SELECT cat.name AS category,
COUNT(p.id) AS product_count,
COUNT(CASE WHEN p.stock_quantity = 0 THEN 1 END) AS out_of_stock,
COUNT(CASE WHEN p.stock_quantity > 0 AND p.stock_quantity < 30 THEN 1 END) AS low_stock,
COALESCE(SUM(p.stock_quantity), 0) AS total_units
FROM categories cat
LEFT JOIN products p ON cat.id = p.category_id
GROUP BY cat.id, cat.name
ORDER BY product_count DESC;
Output:
| category | product_count | out_of_stock | low_stock | total_units |
|---|---|---|---|---|
| Electronics | 4 | 1 | 0 | 425 |
| Sports | 3 | 0 | 0 | 340 |
| Books | 2 | 0 | 0 | 80 |
| Home & Kitchen | 1 | 0 | 1 | 25 |
| Toys | 0 | 0 | 0 | 0 |
Every category is visible, including Toys with zero products. This is exactly the kind of comprehensive report that management needs.
Finding Gaps in Data
-- Products that exist but have NEITHER orders NOR reviews
SELECT p.name AS product,
p.price,
cat.name AS category
FROM products p
INNER JOIN categories cat ON p.category_id = cat.id
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN reviews r ON p.id = r.product_id
WHERE oi.id IS NULL
AND r.id IS NULL;
Output:
| product | price | category |
|---|---|---|
| Bluetooth Speaker | 65.00 | Electronics |
The Bluetooth Speaker has never been ordered and never been reviewed. This is a strong signal that the product needs attention.
FULL OUTER JOIN: A Brief Mention
For completeness, SQL also offers FULL OUTER JOIN (or FULL JOIN), which returns all rows from both tables, filling in NULLs on whichever side has no match:
-- All customers and all orders, matched where possible
SELECT c.first_name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;
This returns customers without orders (NULL on the order side) AND orders without customers (NULL on the customer side, though this cannot happen with our foreign key constraint).
FULL OUTER JOIN is rarely needed in practice because most schemas enforce referential integrity with foreign keys. It is most useful for data reconciliation tasks where you are comparing two datasets that might have mismatches on either side.
FULL OUTER JOIN is supported in PostgreSQL, SQL Server, and Oracle. MySQL does not support FULL OUTER JOIN natively. In MySQL, you can simulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION.
Practical Exercises
Exercise 1
Show all products with their review count and average rating. Products with no reviews should show 0 for count and rating.
SELECT p.name AS product,
p.price,
COUNT(r.id) AS review_count,
COALESCE(ROUND(AVG(r.rating), 2), 0) AS avg_rating
FROM products p
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name, p.price
ORDER BY avg_rating DESC, review_count DESC;
Expected output:
| product | price | review_count | avg_rating |
|---|---|---|---|
| Wireless Mouse | 29.99 | 3 | 4.67 |
| Running Shoes X1 | 110 | 3 | 4.67 |
| USB-C Hub | 45 | 2 | 4.5 |
| SQL for Beginners | 34.99 | 2 | 4.5 |
| Coffee Maker Pro | 129.99 | 2 | 4.5 |
| Yoga Mat Premium | 38 | 2 | 4.5 |
| Stainless Water Bottle | 24.99 | 3 | 4.33 |
| Mechanical Keyboard | 89.99 | 3 | 4 |
| Data Science Handbook | 42.5 | 1 | 3 |
| Bluetooth Speaker | 65 | 0 | 0 |
Exercise 2
Find all categories and the number of available products in each. Include categories with no products.
SELECT cat.name AS category,
COUNT(p.id) AS available_products
FROM categories cat
LEFT JOIN products p ON cat.id = p.category_id AND p.is_available = true
GROUP BY cat.id, cat.name
ORDER BY available_products DESC;
Expected output:
| category | available_products |
|---|---|
| Electronics | 3 |
| Sports | 3 |
| Books | 2 |
| Home & Kitchen | 1 |
| Toys | 0 |
Exercise 3
Find customers who have placed orders but never written a review.
SELECT DISTINCT c.first_name || ' ' || c.last_name AS customer,
c.email
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
LEFT JOIN reviews r ON c.id = r.customer_id
WHERE r.id IS NULL;
Expected output (depends on data, but the pattern finds the gap).
Exercise 4
Show all customers with their total spending on completed orders only. Customers with no completed orders should show 0.
SELECT c.first_name || ' ' || c.last_name AS customer,
COUNT(o.id) AS completed_orders,
COALESCE(SUM(o.total_amount), 0) AS completed_spending
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed'
GROUP BY c.id, c.first_name, c.last_name
ORDER BY completed_spending DESC;
Expected output:
| customer | completed_orders | completed_spending |
|---|---|---|
| Alice Johnson | 3 | 287.46 |
| Bob Martinez | 1 | 89.99 |
| Carol Singh | 1 | 63.99 |
| David Chen | 0 | 0 |
| Eva Brown | 0 | 0 |
| Frank Wilson | 0 | 0 |
| Grace Taylor | 0 | 0 |
Exercise 5
Create a report showing every category with its product count, total revenue from orders, and average product rating. Categories with no products, no orders, or no reviews should show 0.
SELECT cat.name AS category,
COUNT(DISTINCT p.id) AS products,
COALESCE(ROUND(SUM(DISTINCT oi.quantity * oi.unit_price), 2), 0) AS revenue,
COALESCE(ROUND(AVG(r.rating), 2), 0) AS avg_rating
FROM categories cat
LEFT JOIN products p ON cat.id = p.category_id
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY cat.id, cat.name
ORDER BY revenue DESC;
| category | products | revenue | avg_rating |
|---|---|---|---|
| Electronics | 4 | 194.97 | 4.24 |
| Sports | 3 | 172.99 | 4.5 |
| Home & Kitchen | 1 | 129.99 | 4.5 |
| Books | 2 | 34.99 | 4.2 |
| Toys | 0 | 0 | 0 |
Key Takeaways
LEFT JOIN is one of the most important tools in SQL, enabling you to find missing data, build complete reports, and answer "who has not" questions. Here is what you should remember:
LEFT JOINreturns all rows from the left table, filling inNULLfor unmatched right table columnsRIGHT JOINdoes the same but for the right table. In practice, always useLEFT JOINand swap the table order if neededINNER JOINexcludes unmatched rows from both sides.LEFT JOINpreserves unmatched rows from the left- The IS NULL pattern (
LEFT JOIN+WHERE right_table.pk IS NULL) is the standard way to find missing or unmatched data - When using
LEFT JOINwithCOUNT, always count a column from the right table (COUNT(o.id)), neverCOUNT(*) - Use
COALESCEto replaceNULLaggregate results with meaningful defaults like0 - Filters on the right table must go in the
ONclause, notWHERE. Placing them inWHEREconverts theLEFT JOINinto anINNER JOIN - Filters on the left table can safely go in
WHERE - Use
COUNT(DISTINCT ...)when chaining multipleLEFT JOINoperations to avoid inflated counts from row multiplication - Use
INNER JOINwhen the relationship is mandatory. UseLEFT JOINwhen the relationship is optional LEFT JOINis essential for complete reports that must show all entities, including those with zero activity
Mastering LEFT JOIN gives you the ability to see both what exists and what is missing in your data, a capability that is essential for thorough analysis and accurate reporting.