MySQL - Left Join

MySQL: Understanding and Using LEFT JOIN

In relational databases, data is often split across multiple tables to ensure efficiency and reduce redundancy. JOIN clauses are the fundamental tools for combining rows from two or more tables based on a related column. While INNER JOIN retrieves only the matching rows from both tables, an OUTER JOIN allows you to include rows that do not have a match.

The LEFT JOIN (also written as LEFT OUTER JOIN) is one of the most common types of outer joins. It returns all records from the left table and the matched records from the right table. If there is no match, the result is NULL on the right side.

Visualizing LEFT JOIN

Imagine two sets of data, Table A (the left table) and Table B (the right table). A LEFT JOIN gives you everything in Table A, plus any overlapping data from Table B.

      Table A (Left)             Table B (Right)
+-----------------------+     +-----------------------+
| All rows from Table A |     | Matching rows from B  |
|                       |=====>|                       |
|      (even if no match in B)  |                       |
+-----------------------+     +-----------------------+

Result: All of A, with corresponding B data where a match exists, otherwise NULLs for B's columns.

Syntax

The basic syntax for a LEFT JOIN is as follows:

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

The ON clause specifies the condition used to match rows between the two tables.

Practical Example: Customers and Orders

Let’s set up two tables: customers and orders. This classic example helps illustrate how LEFT JOIN works.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
(101, 1, '2023-10-15', 99.50),
(102, 2, '2023-10-16', 150.00),
(103, 1, '2023-10-20', 45.00);

We have three customers, but only Alice (ID 1) and Bob (ID 2) have placed orders. Charlie (ID 3) has not.

Query: Show All Customers and Their Orders

If we want a list of all our customers and any orders they may have placed, LEFT JOIN is the perfect tool. customers will be our left table.

SELECT
    c.id AS customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.amount
FROM
    customers AS c
LEFT JOIN
    orders AS o ON c.id = o.customer_id;

Output

The result includes all customers. For Charlie, who has no orders, the columns from the orders table are NULL.

customer_id	name	order_id	order_date	amount
1	Alice	101	2023-10-15	99.50
1	Alice	103	2023-10-20	45.00
2	Bob	102	2023-10-16	150.00
3	Charlie	NULL	NULL	NULL

Key Use Case: Finding Unmatched Records

One of the most powerful applications of LEFT JOIN is to find rows in one table that do not have a corresponding row in another. This is achieved by adding a WHERE clause to filter for NULL values in the right table’s key.

Query: Find Customers Who Have Never Ordered

SELECT
    c.id,
    c.name
FROM
    customers AS c
LEFT JOIN
    orders AS o ON c.id = o.customer_id
WHERE
    o.customer_id IS NULL;

Output

This query effectively asks for all customers for whom the join condition found no matching order, returning only Charlie.

id	name
3	Charlie

LEFT JOIN with Multiple Tables

You can chain LEFT JOIN clauses to combine data from three or more tables. The logic remains the same: each join starts with the result set of the previous join as its “left table”.

Example: Adding Shipment Data

Let’s add a shipments table. Not all orders may have been shipped yet.

CREATE TABLE shipments (
    shipment_id INT PRIMARY KEY,
    order_id INT,
    ship_date DATE,
    carrier VARCHAR(50),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

INSERT INTO shipments (shipment_id, order_id, ship_date, carrier) VALUES
(201, 101, '2023-10-16', 'ExpressCo');

Now, let’s join all three tables to see all customers, their orders, and any associated shipment information.

SELECT
    c.name,
    o.order_id,
    s.ship_date,
    s.carrier
FROM
    customers AS c
LEFT JOIN
    orders AS o ON c.id = o.customer_id
LEFT JOIN
    shipments AS s ON o.order_id = s.order_id;

Output

name	order_id	ship_date	carrier
Alice	101	2023-10-16	ExpressCo
Alice	103	NULL	NULL
Bob	102	NULL	NULL
Charlie	NULL	NULL	NULL

Handling LEFT JOINs in Application Code

When you fetch results from a LEFT JOIN in your application, you must be prepared to handle NULL values. In strongly-typed languages like Java, this might mean using wrapper classes (e.g., Integer instead of int) or checking for null before accessing a value. In dynamically-typed languages like Python or JavaScript, you’ll check for None or null.

Python Example (Handling NULLs)

import mysql.connector

DB_CONFIG = {
    'user': 'root',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

def get_customer_orders():
    results = []
    sql = """
        SELECT c.name, o.order_id, o.amount
        FROM customers AS c
        LEFT JOIN orders AS o ON c.id = o.customer_id
        ORDER BY c.name;
    """
    with mysql.connector.connect(**DB_CONFIG) as conn:
        with conn.cursor(dictionary=True) as cursor:
            cursor.execute(sql)
            for row in cursor.fetchall():
                # Handle potential NULL values gracefully
                order_id = row['order_id'] if row['order_id'] is not None else 'No Order'
                amount = row['amount'] if row['amount'] is not None else 0.00

                print(f"Customer: {row['name']}, Order ID: {order_id}, Amount: {amount:.2f}")
                results.append(row)
    return results

get_customer_orders()