MySQL LEFT JOIN

The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match in the right table, NULL is returned for the right table's columns. No rows from the left table are ever excluded.

How LEFT JOIN Differs from INNER JOIN

INNER JOIN drops rows that have no match. LEFT JOIN keeps all left table rows — even those without a corresponding row in the right table — and fills right-side columns with NULL.

Syntax

SELECT table1.column, table2.column
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

Sample Tables

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

INSERT INTO customers VALUES (1, 'Anita Roy');
INSERT INTO customers VALUES (2, 'Suresh Mehta');
INSERT INTO customers VALUES (3, 'Priya Nair');
INSERT INTO customers VALUES (4, 'Vikram Rao');

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_item VARCHAR(100),
    customer_id INT
);

INSERT INTO orders VALUES (101, 'Laptop', 1);
INSERT INTO orders VALUES (102, 'Phone', 2);
INSERT INTO orders VALUES (103, 'Tablet', 1);

Example: LEFT JOIN

SELECT c.customer_name, o.order_item
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;

Result:

customer_name | order_item
--------------+-----------
Anita Roy     | Laptop
Anita Roy     | Tablet
Suresh Mehta  | Phone
Priya Nair    | NULL
Vikram Rao    | NULL

Priya Nair and Vikram Rao have no orders, so their order_item is NULL — but they still appear in the result.

Finding Customers with No Orders

A common use of LEFT JOIN is to find rows in the left table that have no match in the right table.

SELECT 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;

Result:

customer_name
-------------
Priya Nair
Vikram Rao

This pattern — LEFT JOIN ... WHERE right_table.column IS NULL — is the standard way to find unmatched records.

LEFT JOIN with Multiple Columns

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

LEFT JOIN with WHERE Filter

SELECT c.customer_name, o.order_item
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE c.customer_name LIKE 'A%';

Returns only customers whose names begin with A, along with their orders (if any).

LEFT JOIN vs INNER JOIN Comparison

Key Points

• LEFT JOIN returns all rows from the left table regardless of a match.
• If no match exists in the right table, right-side columns return NULL.
• Adding WHERE right_table.column IS NULL finds records with no matching row.
• The left table is the one written before the LEFT JOIN keyword.
• LEFT JOIN is also written as LEFT OUTER JOIN — both are identical.