LEFT JOIN in SQL: A Beginner’s Guide with Examples

1. What is a LEFT JOIN in SQL?

A LEFT JOIN (or LEFT OUTER JOIN) in SQL is a type of join operation that returns all records from the left table (the first table mentioned) and only the matching records from the right table. If no match is found, the result will contain NULL values for columns from the right table.

This join is especially useful when you need to analyze data where some records in the primary (left) table might not have corresponding entries in the secondary (right) table. For example, retrieving all customers from a Customers table, along with their orders from an Orders table—even if some customers haven’t placed any orders yet.

Unlike an INNER JOIN, which only returns matched rows, a LEFT JOIN ensures no data from the left table is lost, making it ideal for reports, analytics, and data validation tasks.

2. How to Write a LEFT JOIN Query (Basic Syntax)

The basic syntax for a LEFT JOIN in SQL is straightforward. It combines rows from two tables based on a related column, keeping all records from the left table and only matching records from the right table. Here’s the standard structure:

SELECT 
    left_table.column1, 
    left_table.column2, 
    right_table.column1, 
    right_table.column2
FROM 
    left_table
LEFT JOIN 
    right_table ON left_table.common_column = right_table.common_column;

Example: Customers and Orders LEFT JOIN

Imagine you have two tables:

• Customers (contains customer details like customer_id, name)

• Orders (contains order details like order_id, customer_id, amount)

To fetch all customers, including those who haven’t placed any orders, you’d write:

SELECT 
    Customers.name, 
    Orders.order_id, 
    Orders.amount
FROM 
    Customers
LEFT JOIN 
    Orders ON Customers.customer_id = Orders.customer_id;

Result Explanation:

• Customers with orders will show their order details.

• Customers without orders will still appear, but with NULL values for order_id and amount.

3. LEFT JOIN vs. INNER JOIN vs. RIGHT JOIN: Key Differences

When working with SQL joins, it’s essential to understand how LEFT JOIN compares to INNER JOIN and RIGHT JOIN. Each serves a distinct purpose depending on whether you need unmatched rows from the left, right, or only matching rows.

1. LEFT JOIN (LEFT OUTER JOIN)

• Returns: All records from the left table + matched records from the right table.

• Unmatched Rows: Fills with NULL for right-table columns.

• Use Case:

  • "Show all customers, even if they have no orders."

2. INNER JOIN

• Returns: Only records with matching values in both tables.

• Unmatched Rows: Excluded entirely.

• Use Case:

  • "Find only customers who placed orders."

3. RIGHT JOIN (RIGHT OUTER JOIN)

• Returns: All records from the right table + matched records from the left table.

• Unmatched Rows: Fills with NULL for left-table columns.

• Use Case:

  • "Show all orders, even if customer data is missing."

Comparison Table

When to Use Which?

• Use INNER JOIN for strict matches (e.g., "products with sales").

• Use LEFT JOIN to preserve left-table data (e.g., "all employees, even those without departments").

• Use RIGHT JOIN rarely—most queries can be rewritten with LEFT JOIN for clarity.

4. Practical LEFT JOIN Examples

Example 1: Basic LEFT JOIN with Filtering

Find all employees and their departments (including employees without a department):

SELECT 
    Employees.name,
    Departments.department_name
FROM 
    Employees
LEFT JOIN 
    Departments ON Employees.dept_id = Departments.id

Result:

• Shows all employees, with NULL for department_name if unassigned.

Example 2: LEFT JOIN with WHERE (Isolate Unmatched Rows)

Find customers who never placed an order:

SELECT 
    Customers.name
FROM 
    Customers
LEFT JOIN 
    Orders ON Customers.id = Orders.customer_id
WHERE 
    Orders.id IS NULL

Why it works:
The WHERE Orders.id IS NULL clause filters only unmatched rows.

Example 3: LEFT JOIN Across 3 Tables

Get all products, their categories, and optional supplier info:

SELECT 
    Products.name,
    Categories.category_name,
    Suppliers.supplier_name
FROM 
    Products
LEFT JOIN 
    Categories ON Products.category_id = Categories.id
LEFT JOIN 
    Suppliers ON Products.supplier_id = Suppliers.id

Key Insight:
Chaining LEFT JOINs preserves all products, even with missing categories/suppliers.

Example 4: LEFT JOIN with Aggregation

Count orders per customer (including zero counts):

SELECT 
    Customers.name,
    COUNT(Orders.id) AS order_count
FROM 
    Customers
LEFT JOIN 
    Orders ON Customers.id = Orders.customer_id
GROUP BY 
    Customers.name

SELECT * FROM Customers LEFT JOIN Orders  -- Missing ON condition

SELECT * FROM Customers 
LEFT JOIN Orders ON Customers.id = Orders.customer_id

SELECT * FROM Customers 
LEFT JOIN Orders ON Customers.id = Orders.customer_id
WHERE Orders.amount > 100  -- Filters out NULLs, converting to INNER JOIN!

SELECT * FROM Customers 
LEFT JOIN Orders ON Customers.id = Orders.customer_id 
                  AND Orders.amount > 100  -- Preserves all customers

SELECT COUNT(*) FROM table WHERE right_table.column IS NULL