LEFT JOIN is a type of join that returns all rows from the left table, even if there are no matching rows in the right table. If a match exists in the right table, its data is included. If there is no match, the columns from the right table will contain NULL.
Let’s look at an example
We have two tables: users and orders. Our goal is to get all users along with their orders. If a user has no orders, we still want to include them in the result, so we use a LEFT JOIN.
-- users
id | name
------------
1 | Alice
2 | Bob
3 | Charlie
-- orders
id | user_id | product
------------------------
1 | 1 | Laptop
2 | 1 | Mouse
3 | 2 | Keyboard
The query will look like this. Let’s break it down in more detail:
- Select the columns we want to see in the result:
-- name — from the users table
-- product — from the orders table
SELECT
users.name,
orders.product
FROM
users
-- We do a LEFT JOIN, meaning we take ALL rows from the `users` table (the left table)
-- and add data from the `orders` table (the right table) if a match is found.
LEFT JOIN
orders
-- Define the condition that links rows from both tables:
-- if `users.id` matches `orders.user_id`, the rows are joined.
ON
users.id = orders.user_id;
As a result of running the query, we’ll get the following output.
name | product
-------------------
Alice | Laptop
Alice | Mouse
Bob | Keyboard
Charlie | NULL -- even though Charlie has no matches in the right table, the LEFT JOIN still returns him, but the columns from the right table will be NULL.