SQL Tutorial › Joins & Set Operators › LEFT JOIN

SQL LEFT JOIN

Q: What is the difference between LEFT JOIN and LEFT OUTER JOIN?

None. OUTER is optional; both keep every row of the left table.

Q: How do I find records that do not exist in another table?

Use LEFT JOIN other ON ... WHERE other.id IS NULL, or WHERE NOT EXISTS (SELECT 1 FROM other WHERE ...).

Q: Why does my LEFT JOIN return the same result as an INNER JOIN?

Usually a WHERE condition on the right table removes the NULL rows. Move that condition into the ON clause.

IntermediateLesson 33 of 6912 min read8 queries run on MySQL 8.4

LEFT JOIN (also LEFT OUTER JOIN) keeps every row of the left table, whether or not it has a match on the right. Where there is no match, the right table’s columns are NULL. That makes it the join for questions like “all customers, with their orders if they have any” and “which products have never been sold”.

In this lesson

Keep unmatched rows with LEFT JOIN
Find rows with no match using IS NULL
Count with zeros instead of losing rows
Avoid the WHERE clause that turns a LEFT JOIN into an INNER JOIN

LEFT JOIN syntax

MySQL

SELECT columnsFROM left_table lLEFT JOIN right_table r ON r.key = l.key;

Sample data

customers · 7 rows (some columns)

customer_id	customer_name	city
1	Ananya Sharma	Mumbai
2	Rohit Kumar	Delhi
3	Fatima Khan	Hyderabad
4	David Fernandes	Goa
5	Ishita Bose	Kolkata
6	Manoj Pillai	Chennai
7	Sara Thomas	Bengaluru

orders · 10 rows

order_id	customer_id	order_date	status
1001	1	2025-01-05	Delivered
1002	2	2025-01-17	Delivered
1003	1	2025-02-02	Delivered
1004	3	2025-02-14	Cancelled
1005	4	2025-03-08	Delivered
1006	5	2025-03-22	Shipped
1007	2	2025-04-10	Delivered
1008	1	2025-04-28	Pending
1009	3	2025-05-15	Delivered
1010	5	2025-06-01	Delivered

All customers, with their orders

SQL

SELECT c.customer_id, c.customer_name, o.order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idORDER BY c.customer_id, o.order_id;

ResultMySQL 8.4

customer_id	customer_name	order_id	status
1	Ananya Sharma	1001	Delivered
1	Ananya Sharma	1003	Delivered
1	Ananya Sharma	1008	Pending
2	Rohit Kumar	1002	Delivered
2	Rohit Kumar	1007	Delivered
3	Fatima Khan	1004	Cancelled
3	Fatima Khan	1009	Delivered
4	David Fernandes	1005	Delivered
5	Ishita Bose	1006	Shipped
5	Ishita Bose	1010	Delivered
6	Manoj Pillai	NULL	NULL
7	Sara Thomas	NULL	NULL

12 rows in set

How this query works, step by step

customers is the left table, so all 7 customers will appear.
Customers with orders get one row per order, as with an inner join.
Manoj (6) and Sara (7) have no orders: they still appear once, with NULL in order_id and status.
Result: 10 matched rows + 2 unmatched = 12 rows.

Find rows with no match (anti-join)

Keep only the rows where the right side is NULL. Customers who never ordered:

SQL

SELECT c.customer_id, c.customer_nameFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE o.order_id IS NULL;

ResultMySQL 8.4

customer_id	customer_name
6	Manoj Pillai
7	Sara Thomas

2 rows in set

How this query works, step by step

The left join adds NULL order columns to customers without orders.
WHERE o.order_id IS NULL keeps exactly those rows.
Test a column that can never be NULL in a real match, such as the primary key order_id.

Products that have never been ordered:

SQL

SELECT p.product_id, p.product_nameFROM products pLEFT JOIN order_items oi ON oi.product_id = p.product_idWHERE oi.product_id IS NULL;

ResultMySQL 8.4

product_id	product_name
9	Monitor Arm

1 row in set

Departments with no employees:

SQL

SELECT d.dept_id, d.dept_nameFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idWHERE e.emp_id IS NULL;

ResultMySQL 8.4

dept_id	dept_name
5	Finance

1 row in set

Counts that include zero

Orders per customer, including customers with none:

SQL

SELECT c.customer_name, COUNT(o.order_id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idGROUP BY c.customer_id, c.customer_nameORDER BY orders DESC, c.customer_name;

ResultMySQL 8.4

customer_name	orders
Ananya Sharma	3
Fatima Khan	2
Ishita Bose	2
Rohit Kumar	2
David Fernandes	1
Manoj Pillai	0
Sara Thomas	0

7 rows in set

How this query works, step by step

The left join keeps all 7 customers.
COUNT(o.order_id) counts non-NULL order ids, so Manoj and Sara get 0.
COUNT(*) would be wrong here: it counts their single NULL row as 1.

The WHERE trap

Goal: all customers, plus their delivered orders. Putting the status filter in WHERE silently removes the customers without orders:

SQL

SELECT c.customer_name, o.order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE o.status = 'Delivered'ORDER BY c.customer_name;

ResultMySQL 8.4

customer_name	order_id	status
Ananya Sharma	1001	Delivered
Ananya Sharma	1003	Delivered
David Fernandes	1005	Delivered
Fatima Khan	1009	Delivered
Ishita Bose	1010	Delivered
Rohit Kumar	1002	Delivered
Rohit Kumar	1007	Delivered

7 rows in set

How this query works, step by step

The left join produces NULL status for Manoj and Sara.
WHERE o.status = 'Delivered' is UNKNOWN for NULL, so those rows are thrown away.
The query now behaves like an inner join: the customers without delivered orders are gone.

Move the condition into ON, so it only decides which orders match:

MySQL

SELECT c.customer_name, o.order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'Delivered'ORDER BY c.customer_name;

ResultMySQL 8.4

customer_name	order_id	status
Ananya Sharma	1003	Delivered
Ananya Sharma	1001	Delivered
David Fernandes	1005	Delivered
Fatima Khan	1009	Delivered
Ishita Bose	1010	Delivered
Manoj Pillai	NULL	NULL
Rohit Kumar	1007	Delivered
Rohit Kumar	1002	Delivered
Sara Thomas	NULL	NULL

9 rows in set

How this query works, step by step

Only delivered orders can match now.
Manoj and Sara have no delivered order, so they still appear once, with NULLs.
Every customer is in the result.

Tip

Rule of thumb for outer joins: conditions on the right table go in ON; conditions on the left table go in WHERE.

LEFT JOIN with several tables

Every product with its total units sold, including unsold products:

SQL

SELECT p.product_name, COALESCE(SUM(oi.quantity), 0) AS units_soldFROM products pLEFT JOIN order_items oi ON oi.product_id = p.product_idGROUP BY p.product_id, p.product_nameORDER BY units_sold DESC, p.product_name;

ResultMySQL 8.4

product_name	units_sold
Notebook Pack	18
Gel Pens	6
Desk Lamp	3
Mechanical Keyboard	3
Office Chair	3
USB-C Hub	2
Wireless Mouse	2
Standing Desk	1
Monitor Arm	0

9 rows in set

COALESCE turns the NULL sum of the Monitor Arm into 0.

Exercise: Employees and departments

List all employees with their department name, including the CEO who has no department.

Expected resultMySQL 8.4

first_name	dept_name
Meera	NULL
Asha	Engineering
Rahul	Engineering
Priya	Engineering
Karan	Engineering
Vikram	Sales
Neha	Sales
Arjun	Sales
Kavya	Marketing
Rohan	Marketing
Sneha	HR
Amit	Engineering

12 rows in set

Hint 1

employees must be the left table.

SQL

SELECT e.first_name, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idORDER BY e.emp_id;

ResultMySQL 8.4

first_name	dept_name
Meera	NULL
Asha	Engineering
Rahul	Engineering
Priya	Engineering
Karan	Engineering
Vikram	Sales
Neha	Sales
Arjun	Sales
Kavya	Marketing
Rohan	Marketing
Sneha	HR
Amit	Engineering

12 rows in set

Exercise: Headcount including empty departments

For each department, show its name and the number of employees, including departments with no employees.

Expected resultMySQL 8.4

dept_name	staff
Engineering	5
Sales	3
Marketing	2
HR	1
Finance	0

5 rows in set

Hint 1

Departments on the left, COUNT(e.emp_id).

SQL

SELECT d.dept_name, COUNT(e.emp_id) AS staffFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idGROUP BY d.dept_id, d.dept_nameORDER BY d.dept_id;

ResultMySQL 8.4

dept_name	staff
Engineering	5
Sales	3
Marketing	2
HR	1
Finance	0

5 rows in set

Quick quiz

1. In A LEFT JOIN B, which rows are always kept?

2. How do you find rows in A with no match in B?

3. Why use COUNT(o.order_id) instead of COUNT(*) after a LEFT JOIN?

4. A WHERE condition on the right table of a LEFT JOIN…

LEFT JOIN FAQ

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

None. OUTER is optional; both keep every row of the left table.

How do I find records that do not exist in another table?

Use LEFT JOIN other ON ... WHERE other.id IS NULL, or WHERE NOT EXISTS (SELECT 1 FROM other WHERE ...).

Why does my LEFT JOIN return the same result as an INNER JOIN?

Usually a WHERE condition on the right table removes the NULL rows. Move that condition into the ON clause.

Can a LEFT JOIN return more rows than the left table has?

Yes. If a left row matches several right rows, it appears once per match.