INTERMEDIATE ⏱ 45 minutes

LEFT/RIGHT JOIN: Including Non-Matching Rows

While INNER JOIN shows only matches, LEFT JOIN and RIGHT JOIN (collectively called outer joins) include rows even when there's no match. LEFT JOIN returns all rows from the left table plus matches from the right, filling gaps with NULL. This is essential for questions like "Which customers have never ordered?" or "Show all products, including those with no sales." Understanding outer joins and how to work with NULL values unlocks powerful analytical queries that reveal gaps, missing data, and incomplete relationships in your database.

📚 What You'll Learn

How LEFT JOIN differs from INNER JOIN
When to use LEFT JOIN vs RIGHT JOIN
How NULL values appear in outer joins
How to find rows with no matches
How to filter NULL values correctly
LEFT JOIN vs RIGHT JOIN equivalence
Common LEFT JOIN patterns and use cases
How to handle optional relationships
Performance considerations for outer joins

1. LEFT JOIN: All Rows from Left Table

LEFT JOIN returns ALL rows from the left table, plus matching rows from the right table. If no match exists, the right side is filled with NULL values.

Visual Representation

LEFT Table      RIGHT Table
┌───────┐        ┌───────┐
│████████        │       │
│█████████───────┼───────┘
│████████        │       
└───────┘        └───────┘

LEFT JOIN = Everything from LEFT + matches from RIGHT
          = Left table completely included

Example Dataset

customers (LEFT table)
+-------------+---------------+------------------+
| customer_id | name          | email            |
+-------------+---------------+------------------+
| 1           | Alice Johnson | alice@email.com  |
| 2           | Bob Smith     | bob@email.com    |
| 3           | Carol Davis   | carol@email.com  |  ← No orders
| 4           | David Lee     | david@email.com  |  ← No orders
+-------------+---------------+------------------+

orders (RIGHT table)
+----------+-------------+------------+----------+
| order_id | customer_id | order_date | total    |
+----------+-------------+------------+----------+
| 101      | 1           | 2024-01-15 | 299.99   |
| 102      | 1           | 2024-02-20 | 49.99    |
| 103      | 2           | 2024-03-10 | 199.99   |
+----------+-------------+------------+----------+

LEFT JOIN Result:
+-------------+---------------+----------+------------+----------+
| customer_id | name          | order_id | order_date | total    |
+-------------+---------------+----------+------------+----------+
| 1           | Alice Johnson | 101      | 2024-01-15 | 299.99   |
| 1           | Alice Johnson | 102      | 2024-02-20 | 49.99    |
| 2           | Bob Smith     | 103      | 2024-03-10 | 199.99   |
| 3           | Carol Davis   | NULL     | NULL       | NULL     |  ← No match
| 4           | David Lee     | NULL     | NULL       | NULL     |  ← No match
+-------------+---------------+----------+------------+----------+

All customers included, even those without orders!

2. LEFT JOIN Syntax

Basic Form

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

Complete Example

-- Show all customers and their orders (if any)
SELECT 
    c.customer_id,
    c.name,
    c.email,
    o.order_id,
    o.order_date,
    o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- Returns all customers, even those without orders

INNER JOIN vs LEFT JOIN Comparison

-- INNER JOIN: Only customers with orders (excludes Carol & David)
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Returns 3 rows (Alice appears twice, Bob once)

-- LEFT JOIN: All customers, orders if available
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Returns 5 rows (includes Carol and David with NULL orders)

3. Understanding NULL in LEFT JOINs

⚠️ Key Concept: When LEFT JOIN finds no match, columns from the right table are filled with NULL. This is how you identify rows that don't have related records.

Finding Rows Without Matches

-- Find customers who have NEVER ordered
SELECT 
    c.customer_id,
    c.name,
    c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Result: Carol and David (customers with no orders)
+-------------+-------------+------------------+
| customer_id | name        | email            |
+-------------+-------------+------------------+
| 3           | Carol Davis | carol@email.com  |
| 4           | David Lee   | david@email.com  |
+-------------+-------------+------------------+

Counting Matches

-- Show all customers with order count (including 0)
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY order_count DESC;

-- Result shows everyone, even those with 0 orders
+-------------+---------------+-------------+
| customer_id | name          | order_count |
+-------------+---------------+-------------+
| 1           | Alice Johnson | 2           |
| 2           | Bob Smith     | 1           |
| 3           | Carol Davis   | 0           |  ← COUNT handles NULL correctly
| 4           | David Lee     | 0           |
+-------------+---------------+-------------+

💡 Pro Tip: COUNT(column) ignores NULL values, so COUNT(o.order_id) returns 0 for customers without orders. This is different from COUNT(*) which counts all rows including NULLs!

4. Filtering NULL Values Correctly

IS NULL vs IS NOT NULL

-- Find customers WITHOUT orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Find customers WITH orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NOT NULL;

Common Mistake: Using = NULL

❌ WRONG - This doesn't work!

-- ❌ Will return no results (NULL = NULL is always false)
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id = NULL;  -- WRONG!

✅ CORRECT - Use IS NULL

-- ✅ This works correctly
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

5. RIGHT JOIN: All Rows from Right Table

RIGHT JOIN returns ALL rows from the right table, plus matching rows from the left table. It's the mirror image of LEFT JOIN.

Visual Representation

LEFT Table      RIGHT Table
┌───────┐        ┌───────┐
│       │        │████████
└───────┼────────┼████████
        │        │████████
        └───────┘└───────┘

RIGHT JOIN = Everything from RIGHT + matches from LEFT
           = Right table completely included

RIGHT JOIN Syntax

SELECT columns
FROM left_table
RIGHT JOIN right_table
    ON left_table.column = right_table.column;

Example: All Orders with Customer Info

-- Show all orders, even if customer record is missing
SELECT 
    o.order_id,
    o.order_date,
    o.total,
    c.name,
    c.email
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- Returns all orders, with NULL for missing customers

6. LEFT JOIN vs RIGHT JOIN: They're Equivalent

Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping table order. Most developers prefer LEFT JOIN for consistency and readability.

These Are Identical

-- Option 1: LEFT JOIN (preferred)
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Option 2: RIGHT JOIN (same result)
SELECT c.name, o.order_id
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

-- Both return all customers with their orders (if any)

✅ Best Practice: Stick with LEFT JOIN for consistency. It's more intuitive to read "start with customers, add orders" than "start with orders, add customers." Most SQL developers rarely use RIGHT JOIN.

7. Common LEFT JOIN Patterns

Pattern 1: Find Unused/Orphaned Records

-- Find products that have never been ordered
SELECT 
    p.product_id,
    p.name,
    p.price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

-- Products with no sales history

Pattern 2: Show All Items with Optional Details

-- All employees with optional department info
SELECT 
    e.employee_id,
    e.name,
    e.salary,
    COALESCE(d.name, 'No Department') AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Shows all employees, even those not assigned to a department
-- COALESCE replaces NULL with a default value

Pattern 3: Completeness Reports

-- Show which users have completed their profile
SELECT 
    u.user_id,
    u.username,
    u.email,
    CASE 
        WHEN p.profile_id IS NULL THEN 'Incomplete'
        ELSE 'Complete'
    END AS profile_status
FROM users u
LEFT JOIN profiles p ON u.user_id = p.user_id;

Pattern 4: Activity Analysis

-- Show all customers with order count and total spent
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

-- Includes customers with $0 spent (no orders)

8. Handling NULL with COALESCE

COALESCE() returns the first non-NULL value from a list. It's perfect for replacing NULL with default values in LEFT JOINs.

Basic COALESCE Usage

-- Replace NULL with default values
SELECT 
    c.name,
    COALESCE(o.order_id, 0) AS order_id,
    COALESCE(o.total, 0.00) AS total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Customers without orders show 0 instead of NULL

Multiple Fallback Values

-- Try multiple columns, use first non-NULL
SELECT 
    e.employee_id,
    e.name,
    COALESCE(e.work_phone, e.mobile_phone, e.home_phone, 'No phone') AS contact_phone
FROM employees e;

-- Returns first available phone number

COALESCE in Calculations

-- Prevent NULL in aggregations
SELECT 
    c.customer_id,
    c.name,
    COALESCE(SUM(o.total), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- SUM returns NULL for customers with no orders
-- COALESCE converts NULL to 0

9. Advanced LEFT JOIN Filtering

Filter Before vs After JOIN

-- Filter in ON clause (affects join, not result)
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id 
    AND o.order_date >= '2024-01-01'
-- Shows all customers, but only 2024 orders

-- Filter in WHERE clause (affects final result)
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
-- Shows only customers with 2024 orders (like INNER JOIN!)

⚠️ Critical Difference: For LEFT JOIN, WHERE conditions on the right table turn it into an INNER JOIN (excluding NULLs). If you want to filter the right table but keep all left rows, put the filter in the ON clause!

Example: Show All Customers, Only Recent Orders

-- ✅ CORRECT: Filter in ON keeps all customers
SELECT 
    c.name,
    o.order_id,
    o.order_date
FROM customers c
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id 
    AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY c.name;
-- All customers shown, orders only from last 30 days

-- ❌ WRONG: Filter in WHERE excludes customers without recent orders
SELECT 
    c.name,
    o.order_id,
    o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY c.name;
-- Only customers with recent orders (effectively INNER JOIN)

10. 🎯 Practice Exercises

Exercise 1: Find Inactive Entities

Given these tables:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    stock INT
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT
);

Tasks:

Find products that have NEVER been ordered
Show all products with their order count (including 0)
List products with stock > 0 that have no sales

Show Solution

-- Task 1: Products never ordered
SELECT 
    p.product_id,
    p.name,
    p.price,
    p.stock
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL
ORDER BY p.name;


-- Task 2: All products with order count
SELECT 
    p.product_id,
    p.name,
    p.price,
    COUNT(oi.order_item_id) AS times_ordered
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name, p.price
ORDER BY times_ordered DESC;


-- Task 3: In-stock products with no sales
SELECT 
    p.product_id,
    p.name,
    p.price,
    p.stock
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE p.stock > 0
    AND oi.order_item_id IS NULL
ORDER BY p.stock DESC;

Key Concepts:

✅ LEFT JOIN + IS NULL finds unmatched rows
✅ COUNT() with LEFT JOIN includes zeros
✅ WHERE filters after join
✅ Can combine IS NULL check with other filters

Exercise 2: Customer Activity Report

Given these tables:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    registration_date DATE
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total DECIMAL(10, 2),
    status VARCHAR(20)
);

Tasks:

Find customers who registered but never ordered
Show all customers with their total lifetime spending
List customers with 0 orders in 2024 (but may have older orders)

Show Solution

-- Task 1: Registered but never ordered
SELECT 
    c.customer_id,
    c.name,
    c.email,
    c.registration_date,
    DATEDIFF(CURRENT_DATE, c.registration_date) AS days_since_registration
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.registration_date;


-- Task 2: All customers with lifetime spending
SELECT 
    c.customer_id,
    c.name,
    c.email,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total), 0) AS lifetime_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email
ORDER BY lifetime_spent DESC;


-- Task 3: Customers with no 2024 orders
SELECT 
    c.customer_id,
    c.name,
    c.email
FROM customers c
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id 
    AND o.order_date >= '2024-01-01'
    AND o.order_date < '2025-01-01'
WHERE o.order_id IS NULL
ORDER BY c.name;
-- Note: Date filter in ON clause, not WHERE!

Important Techniques:

✅ Task 1: Simple LEFT JOIN + IS NULL pattern
✅ Task 2: COALESCE handles NULL in SUM
✅ Task 3: Date filter in ON (not WHERE!) keeps all customers
💡 If date filter was in WHERE, it would exclude customers with old orders

Exercise 3: Employee Department Coverage

Given these tables:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    name VARCHAR(100),
    location VARCHAR(100),
    budget DECIMAL(12, 2)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(255),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE
);

Tasks:

Find departments with no employees
Show all departments with employee count and total payroll
List departments with budget but no employees (wasteful?)

Show Solution

-- Task 1: Empty departments
SELECT 
    d.department_id,
    d.name AS department_name,
    d.location,
    d.budget
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL
ORDER BY d.name;


-- Task 2: All departments with headcount and payroll
SELECT 
    d.department_id,
    d.name AS department_name,
    d.location,
    d.budget,
    COUNT(e.employee_id) AS employee_count,
    COALESCE(SUM(e.salary), 0) AS total_payroll,
    d.budget - COALESCE(SUM(e.salary), 0) AS budget_remaining
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.name, d.location, d.budget
ORDER BY employee_count DESC, d.name;


-- Task 3: Funded departments with no staff
SELECT 
    d.department_id,
    d.name AS department_name,
    d.budget,
    d.location
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE d.budget > 0
    AND e.employee_id IS NULL
ORDER BY d.budget DESC;

Analysis Insights:

✅ LEFT JOIN reveals organizational gaps
✅ Can combine IS NULL with other conditions
✅ Calculated columns (budget_remaining) work with COALESCE
✅ Business intelligence: empty departments may need attention

🔥 Challenge: Comprehensive User Engagement Analysis

You have a social platform database:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(255),
    registration_date DATE,
    account_status VARCHAR(20)
);

CREATE TABLE posts (
    post_id INT PRIMARY KEY,
    user_id INT,
    content TEXT,
    created_at TIMESTAMP
);

CREATE TABLE comments (
    comment_id INT PRIMARY KEY,
    post_id INT,
    user_id INT,
    content TEXT,
    created_at TIMESTAMP
);

Challenge:

Find users who never posted or commented (inactive)
Show all users with post count, comment count, and last activity date
Identify "lurkers" (users who commented but never posted)

Show Solution

-- Challenge 1: Completely inactive users
SELECT 
    u.user_id,
    u.username,
    u.email,
    u.registration_date,
    DATEDIFF(CURRENT_DATE, u.registration_date) AS days_inactive
FROM users u
LEFT JOIN posts p ON u.user_id = p.user_id
LEFT JOIN comments c ON u.user_id = c.user_id
WHERE p.post_id IS NULL 
    AND c.comment_id IS NULL
    AND u.account_status = 'active'
ORDER BY u.registration_date;


-- Challenge 2: Complete engagement metrics
SELECT 
    u.user_id,
    u.username,
    u.registration_date,
    COUNT(DISTINCT p.post_id) AS post_count,
    COUNT(DISTINCT c.comment_id) AS comment_count,
    COUNT(DISTINCT p.post_id) + COUNT(DISTINCT c.comment_id) AS total_activity,
    GREATEST(
        COALESCE(MAX(p.created_at), u.registration_date),
        COALESCE(MAX(c.created_at), u.registration_date)
    ) AS last_activity,
    CASE 
        WHEN COUNT(DISTINCT p.post_id) = 0 
         AND COUNT(DISTINCT c.comment_id) = 0 THEN 'Inactive'
        WHEN COUNT(DISTINCT p.post_id) + COUNT(DISTINCT c.comment_id) < 5 THEN 'Low'
        WHEN COUNT(DISTINCT p.post_id) + COUNT(DISTINCT c.comment_id) < 20 THEN 'Medium'
        ELSE 'High'
    END AS engagement_level
FROM users u
LEFT JOIN posts p ON u.user_id = p.user_id
LEFT JOIN comments c ON u.user_id = c.user_id
GROUP BY u.user_id, u.username, u.registration_date
ORDER BY total_activity DESC;


-- Challenge 3: Lurkers (comment but don't post)
SELECT 
    u.user_id,
    u.username,
    COUNT(c.comment_id) AS comment_count
FROM users u
INNER JOIN comments c ON u.user_id = c.user_id
LEFT JOIN posts p ON u.user_id = p.user_id
WHERE p.post_id IS NULL  -- No posts
GROUP BY u.user_id, u.username
HAVING COUNT(c.comment_id) > 0
ORDER BY comment_count DESC;

Advanced Techniques Used:

✅ Multiple LEFT JOINs to check multiple relationships
✅ COUNT(DISTINCT) prevents double-counting
✅ GREATEST() finds most recent of multiple dates
✅ CASE for engagement categorization
✅ Combining INNER and LEFT JOIN strategically
✅ HAVING clause filters aggregated results

Business Insights:

📊 Inactive users: target for re-engagement campaigns
📊 Engagement levels: segment users for personalization
📊 Lurkers: potential content contributors to encourage
📊 Last activity: identify churning users early

📝 Key Takeaways

LEFT JOIN returns all rows from left table + matches from right
RIGHT JOIN returns all rows from right table + matches from left
Non-matching rows show NULL for the other table's columns
Use IS NULL to find rows without matches (never use = NULL)
Use IS NOT NULL to find rows with matches
COUNT(column) ignores NULL, perfect for counting matches
COALESCE() replaces NULL with default values
LEFT JOIN + IS NULL finds orphaned/unused records
Filter in ON clause vs WHERE clause has different effects
WHERE on right table columns turns LEFT JOIN into INNER JOIN
Most developers prefer LEFT JOIN over RIGHT JOIN
Any RIGHT JOIN can be rewritten as LEFT JOIN
Use GROUP BY with LEFT JOIN for counts including zeros
LEFT JOIN is essential for completeness analysis
Perfect for finding "what's missing" in your data