LEFT JOIN
In this page:
What is a LEFT JOIN?
A LEFT JOIN keeps all rows from the left table regardless of whether a match exists. It matches them with rows from the right table where possible. If there is no match, the right side returns NULL instead of dropping the row entirely.
Example: What is a LEFT JOIN?
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1);
SELECT customers.name, orders.order_id
FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;Finding Unmatched Rows
You can find rows in the left table that have no matches in the right table, such as customers who have never placed an order. To do this, use a LEFT JOIN with a WHERE IS NULL check on a column from the right table.
Example: Finding Unmatched Rows
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1);
SELECT customers.name
FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;Filtering Left vs Right Sides
Filtering in the ON clause behaves differently from filtering in the WHERE clause when using LEFT JOIN. The ON clause filters before joining, preserving left-side rows even when the extra condition fails, while a WHERE filter on the right table can silently turn your LEFT JOIN into an INNER JOIN.
Example: Filtering Left vs Right Sides
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT, total INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1, 500);
-- Filter in ON: keeps all customers
SELECT customers.name, orders.total
FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id AND orders.total > 100;
-- Filter in WHERE: turns it into an INNER JOIN
SELECT customers.name, orders.total
FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.total > 100;Multiple LEFT JOINs
You can chain multiple LEFT JOIN statements to pull in several optional relationships at once. This is helpful for pulling optional details from several different tables, like a customer's most recent order and their loyalty tier, without excluding customers missing either.
Example: Multiple LEFT JOINs
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
CREATE TABLE loyalty (customer_id INT, tier TEXT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1);
INSERT INTO loyalty VALUES (1, 'Gold');
SELECT customers.name, orders.order_id, loyalty.tier
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN loyalty ON customers.customer_id = loyalty.customer_id;LEFT JOIN with COALESCE / IFNULL
When a join returns NULL because no match was found, you can replace it with a fallback value using COALESCE or IFNULL. This keeps your reports looking neat and clean, showing something like 'No orders yet' instead of a blank cell.
Example: LEFT JOIN with COALESCE / IFNULL
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1);
SELECT customers.name, COALESCE(orders.order_id, 'No orders yet') AS latest_order
FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: