MySQL LEFT JOIN

MySQL LEFT JOIN returns every row from the left table and matching rows from the right table. If a left-side row has no match, MySQL returns NULL for the right-side columns. A left row appears once for each matching right row.

For a focused explanation of matching rows only, see the MySQL INNER JOIN tutorial. The MySQL JOIN guide compares all join types.

Example: keep every customer

Run the following statements in the same session to create sample tables:

CREATE TEMPORARY TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);

CREATE TEMPORARY TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (customer_id, customer_name)
VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Noah');

INSERT INTO orders (order_id, customer_id, status)
VALUES
    (101, 1, 'shipped'),
    (102, 1, 'pending'),
    (103, 2, 'pending');

Return all customers along with their orders:

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

The result contains four rows: Ada appears twice because she has two orders, Lin appears once with her pending order, and Noah appears once with NULL in the order columns because he has no order.

Find rows with no match

Test a right-side column that cannot be NULL, such as the orders primary key, to find customers with no orders:

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

This returns Noah. See the MySQL IS NULL tutorial for more NULL checks.

Put right-table filters in the correct clause

To keep every customer but match only shipped orders, put the status filter in ON:

SELECT c.customer_name, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
   AND o.status = 'shipped'
ORDER BY c.customer_id;

Customers without a shipped order remain in the result, with NULL in the order columns. If you instead put o.status = 'shipped' in WHERE, rows with no right-side match are removed because NULL = 'shipped' is not true. That can make the result behave like an inner join for this condition.

Use USING for a shared column name

When both tables use the same name for the join column, USING is a shorter alternative to ON:

SELECT customer_id, customer_name, order_id, status
FROM customers
LEFT JOIN orders USING (customer_id)
ORDER BY customer_id, order_id;

The USING form returns one copy of the shared customer_id column in SELECT *. For full syntax and behavior, see MySQL’s official JOIN documentation.