Imagine you have two tables: a list of students and a list of their course enrollments. Not all students are enrolled in courses, and you want to see the full list of all students, including those who, for some reason, haven’t picked a course yet. With INNER JOIN you’ll only see those who are enrolled, but what about the rest of the students? That’s exactly what LEFT JOIN is for.
LEFT JOIN returns all rows from the left table (the one you specify first in the query) and matching rows from the right table. If there’s no match, the columns from the right table will have NULL values.
Syntax for LEFT JOIN
SELECT
table1.column1,
table1.column2,
table2.column1,
table2.column2
FROM
table1 LEFT JOIN table2
ON
table1.common_column = table2.common_column;
table1— this is your "left" table.table2— this is your "right" table.common_column— the column you’re joining on.
Simple example
If the students table looks like this:
| student_id | name |
|---|---|
| 1 | Otto |
| 2 | Anna |
| 3 | Peter |
And the enrollments table looks like this:
| enrollment_id | student_id | course |
|---|---|---|
| 1 | 1 | Mathematics |
| 2 | 1 | Physics |
| 3 | 2 | Biology |
Then the query:
SELECT
students.name,
enrollments.course
FROM
students LEFT JOIN enrollments
ON
students.student_id = enrollments.student_id;
will give you:
| name | course |
|---|---|
| Otto | Mathematics |
| Otto | Physics |
| Anna | Biology |
| Peter | NULL |
As you can see, all students are in the result, even Peter, who hasn’t enrolled in any course yet. For Peter, the course column is NULL.
Examples of using LEFT JOIN
Example 1: Getting a list of all students and their courses
Let’s say you need to get a full list of students along with the courses they’re enrolled in, if any. If a student hasn’t picked a course yet, that should be shown too.
Same query:
SELECT
students.name,
enrollments.course
FROM
students LEFT JOIN enrollments
ON
students.student_id = enrollments.student_id;
Result:
| name | course |
|---|---|
| Otto | Mathematics |
| Otto | Physics |
| Anna | Biology |
| Peter | NULL |
This is a classic example of using LEFT JOIN.
Example 2: Showing products and their sales
Let’s say you have two tables:
The products table, which has all the products:
| product_id | product_name |
|---|---|
| 1 | Smartphone |
| 2 | Tablet |
| 3 | Laptop |
The sales table, which has sales data:
| sale_id | product_id | quantity |
|---|---|---|
| 1 | 1 | 5 |
| 2 | 1 | 3 |
| 3 | 2 | 2 |
Now you want to see all products and how many times they’ve been sold, including products that haven’t been sold yet.
SELECT
products.product_name,
SUM(sales.quantity) AS total_sold
FROM
products LEFT JOIN sales
ON
products.product_id = sales.product_id
GROUP BY
products.product_name;
Result:
| product_name | total_sold |
|---|---|
| Smartphone | 8 |
| Tablet | 2 |
| Laptop | NULL |
Features and issues when using LEFT JOIN
Do you always want NULL?
Sometimes LEFT JOIN adds NULL where you didn’t expect it. In those cases, you can replace NULL with something more readable using the COALESCE() function.
SELECT
students.name,
COALESCE(enrollments.course, 'Course not selected') AS course
FROM
students LEFT JOIN enrollments
ON
students.student_id = enrollments.student_id;
Result:
| name | course |
|---|---|
| Otto | Mathematics |
| Otto | Physics |
| Anna | Biology |
| Peter | Course not selected |
Unwanted duplicates
If the data in the right table has duplicate records, your query result will have more rows than you might expect. Always check your data and use DISTINCT if you don’t want duplicates.
GO TO FULL VERSION