Home / SQL / LEFT JOIN

LEFT JOIN

Overview

The LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.

LEFT JOIN is useful when you want to include all records from the left table, even if there are no matches in the right table.

Basic Syntax

LEFT JOIN Syntax

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Visual Representation

LEFT JOIN Diagram

Table A (LEFT)    Table B (RIGHT)
┌─────┐         ┌─────┐
│  1  │         │  3  │
│  2  │─────────│  2  │  Returns: 1, 2, 3, 4 (all A)
│  3  │         │  4  │  (3 and 4 have matches in B)
│  4  │         │  5  │  (1 and 2 have NULLs for B columns)
└─────┘         └─────┘

Result: All rows from left table + matching rows from right
        Non-matching rows have NULL in right table columns

Examples

Example 1: Basic LEFT JOIN

All customers with their orders

SELECT 
    c.name AS customer_name,
    o.order_date,
    o.total_amount
FROM Customers c
LEFT JOIN Orders o 
ON c.customer_id = o.customer_id;

Returns: All customers, including those without orders (NULL in order columns).

Example 2: Find Customers Without Orders

Customers with no orders

SELECT 
    c.name AS customer_name,
    c.email
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Returns: Only customers who have never placed an order.

Example 3: LEFT JOIN with Aggregates

Customer order summary

SELECT 
    c.name AS customer_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Returns: All customers with their order counts and totals (0 and NULL for customers without orders).

LEFT JOIN with Sample Data

Understanding how LEFT JOIN works:

Sample Data

Customers (LEFT):
┌─────────────┬─────────────┐
│ customer_id │ name        │
├─────────────┼─────────────┤
│ 1           │ John Doe    │
│ 2           │ Jane Smith  │
│ 3           │ Bob Johnson │
└─────────────┴─────────────┘

Orders (RIGHT):
┌──────────┬─────────────┬──────────────┐
│ order_id │ customer_id │ total_amount │
├──────────┼─────────────┼──────────────┤
│ 1        │ 1           │ 100.00       │
│ 2        │ 1           │ 200.00       │
└──────────┴─────────────┴──────────────┘

LEFT JOIN Result:
┌─────────────┬──────────┬──────────────┐
│ name        │ order_id │ total_amount │
├─────────────┼──────────┼──────────────┤
│ John Doe    │ 1        │ 100.00       │
│ John Doe    │ 2        │ 200.00       │
│ Jane Smith  │ NULL     │ NULL         │ ← No orders
│ Bob Johnson │ NULL     │ NULL         │ ← No orders
└─────────────┴──────────┴──────────────┘

LEFT JOIN vs INNER JOIN

Understanding the difference:

Comparison

INNER JOIN:
- Returns only matching rows
- Excludes customers without orders

LEFT JOIN:
- Returns all customers
- Includes customers without orders (NULL values)
- More comprehensive data

Multiple LEFT JOINs

You can chain multiple LEFT JOINs:

Multiple LEFT JOINs

SELECT 
    c.name AS customer,
    o.order_date,
    p.product_name
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
LEFT JOIN OrderItems oi ON o.order_id = oi.order_id
LEFT JOIN Products p ON oi.product_id = p.product_id;

LEFT JOIN with WHERE

Be careful with WHERE clauses after LEFT JOIN:

Filtering with LEFT JOIN

-- This converts LEFT JOIN to INNER JOIN!
SELECT c.name, o.order_date
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > 100;  -- Excludes NULLs (all customers)

-- To include all customers, use OR IS NULL
SELECT c.name, o.order_date
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > 100 OR o.order_id IS NULL;

Using LEFT JOIN to Find Missing Data

LEFT JOIN is commonly used to find records in one table that don't have matches in another:

Find missing relationships

-- Products never ordered
SELECT p.product_name, p.price
FROM Products p
LEFT JOIN OrderItems oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

-- Customers without orders
SELECT c.name, c.email
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

LEFT JOIN with COALESCE

Use COALESCE to replace NULL values:

Handle NULLs with COALESCE

SELECT 
    c.name AS customer_name,
    COALESCE(COUNT(o.order_id), 0) AS order_count,
    COALESCE(SUM(o.total_amount), 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;

When to Use LEFT JOIN

When you need all rows from the left table
To find records without matches (using WHERE ... IS NULL)
For optional relationships (e.g., customers who may not have orders)
For comprehensive reports including all records
When NULL values for unmatched rows are acceptable

Performance Considerations

Index join columns: Especially the right table's join column
Filter left table first: Use WHERE on left table before JOIN
Handle NULLs efficiently: Use COALESCE or ISNULL appropriately
Consider INNER JOIN: If you don't need unmatched rows, INNER JOIN is faster

Best Practices

Understand NULL behavior: Unmatched rows have NULL in right table columns
Handle NULLs explicitly: Use COALESCE or ISNULL for calculations
Use WHERE ... IS NULL carefully: Understand it finds non-matches
Document purpose: Make it clear why LEFT JOIN is needed
Test with edge cases: Verify behavior with NULL values

Common Use Cases

Listing all customers with their orders (if any)
Finding products that have never been ordered
Identifying customers without orders
Creating reports that include all records from one table
Handling optional relationships between tables

Next Steps

Learn about RIGHT JOIN (mirror of LEFT JOIN) or FULL JOIN for both tables.

← Inner Join Right Join →