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:

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:

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:

Grace now appears with an order count of 0. All seven customers are accounted for.

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:

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:

The logic works like this:

1. LEFT JOIN includes all customers, even those with no orders
2. For customers without orders, o.id is NULL
3. WHERE o.id IS NULL filters 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:

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:

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:

The new "Toys" category exists but has no products assigned to it.

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:

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:

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:

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:

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.

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;

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;

7 rows. Grace appears with 0 orders.

When to Use Which

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:

Grace appears with zeros across all metrics. Both LEFT JOIN operations preserve her row.

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:

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:

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:

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:

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:

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:

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:

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.

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:

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:

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:

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;

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 JOIN returns all rows from the left table, filling in NULL for unmatched right table columns
• RIGHT JOIN does the same but for the right table. In practice, always use LEFT JOIN and swap the table order if needed
• INNER JOIN excludes unmatched rows from both sides. LEFT JOIN preserves 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 JOIN with COUNT, always count a column from the right table (COUNT(o.id)), never COUNT(*)
• Use COALESCE to replace NULL aggregate results with meaningful defaults like 0
• Filters on the right table must go in the ON clause, not WHERE. Placing them in WHERE converts the LEFT JOIN into an INNER JOIN
• Filters on the left table can safely go in WHERE
• Use COUNT(DISTINCT ...) when chaining multiple LEFT JOIN operations to avoid inflated counts from row multiplication
• Use INNER JOIN when the relationship is mandatory. Use LEFT JOIN when the relationship is optional
• LEFT JOIN is 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.