A LEFT JOIN returns all rows from your left table, plus matching rows from your right table
A LEFT JOIN is a way to combine data from two tables in SQL. It keeps every row from the first table (called the "left" table) and adds columns from the second table (called the "right" table) wherever there is a match. If there is no match, the columns from the right table show as empty or NULL.
Think of it like a guest list. Your left table is the people you invited. Your right table is the people who confirmed they are coming. A LEFT JOIN shows everyone you invited, and marks who confirmed. People who did not confirm still appear on the list — they just have no confirmation next to their name.
This is different from an INNER JOIN, which only shows rows that exist in both tables. A LEFT JOIN is useful when you want to keep all your original data and see what additional information you can match to it.
Key Takeaways
- A LEFT JOIN keeps all rows from the left table and adds matching data from the right table, leaving blanks where there is no match.
- The order matters: the table you name first is the left table, and the table you name second is the right table.
- You specify the match condition using an ON clause, which tells SQL which columns should be equal between the two tables.
- LEFT JOIN is useful when you want to keep all your original records and see which ones have related data in another table.
- If a row in the left table has multiple matches in the right table, that row will appear multiple times in your results.
The basic syntax and how to read it
The structure of a LEFT JOIN looks like this:
SELECT columns FROM left_table LEFT JOIN right_table ON left_table.column = right_table.column;
The FROM clause names your left table. The LEFT JOIN clause names your right table. The ON clause tells SQL which columns to match between them. You use the table name, a dot, and the column name to be specific about which column you mean.
Here is a real example. Say you have a customers table with customer_id and customer_name, and an orders table with customer_id and order_amount. You want to see every customer and how much they ordered, if anything:
SELECT customers.customer_name, orders.order_amount FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;
This query returns every customer. If a customer has orders, you see the order amounts. If a customer has no orders, the order_amount column is empty for that row.
What happens when there is no match
When a row in the left table has no matching row in the right table, SQL fills those columns with NULL. NULL means "no value" or "unknown" — it is not zero, not a blank space, and not the word "NULL". It is the absence of data.
This matters because NULL behaves differently in calculations and comparisons. If you add a number to NULL, the result is NULL. If you check whether a column equals NULL, you cannot use = NULL; you must use IS NULL or IS NOT NULL.
In the customer example above, a customer with no orders would show their name but have NULL in the order_amount column. This tells you the customer exists in your system but has not placed an order, rather than the data being missing or wrong.
LEFT JOIN with multiple matches
If a customer has placed three orders, that customer's row appears three times in your results — once for each order. This is called a Cartesian product or a multiplication effect. One row on the left can become many rows in the output.
This is correct behavior, but it can surprise you if you are not expecting it. If you count rows or sum amounts, you need to be aware that one customer might contribute multiple rows. Using COUNT(DISTINCT customer_id) instead of COUNT(*) can help you count unique customers rather than unique rows.
LEFT JOIN versus INNER JOIN versus RIGHT JOIN
An INNER JOIN returns only rows where both tables have a match. If you use INNER JOIN on customers and orders, you see only customers who have placed at least one order. Customers with no orders disappear from the results.
A RIGHT JOIN does the opposite of LEFT JOIN. It keeps all rows from the right table and adds matching data from the left table. RIGHT JOIN is less common because you can usually rewrite it as a LEFT JOIN by switching the table order.
A FULL OUTER JOIN keeps all rows from both tables. If a customer has no orders and an order has no matching customer, both still appear in the results. Not all SQL databases support FULL OUTER JOIN — MySQL does not, but PostgreSQL and SQL Server do.
| Join Type | Keeps All Rows From | Shows Unmatched Rows As |
|---|---|---|
| INNER JOIN | Neither table (only matches) | Excluded |
| LEFT JOIN | Left table | NULL in right columns |
| RIGHT JOIN | Right table | NULL in left columns |
| FULL OUTER JOIN | Both tables | NULL in unmatched columns |
Common reasons to use LEFT JOIN
LEFT JOIN is the right choice when you want to see all records from one table and check whether they have related data in another. Common situations include: showing all employees and their department assignments (some might not be assigned yet), showing all products and their sales (some might not have sold), or showing all users and their account status (some might be inactive).
LEFT JOIN is also useful for finding missing data. If you want to find customers who have never placed an order, you can use a LEFT JOIN and then filter for rows where the order columns are NULL. This is often faster and clearer than using a NOT IN or NOT EXISTS clause.
Another use is combining data from a main table with optional related data. If you have a users table and a preferences table, and not every user has set preferences, LEFT JOIN lets you show all users with their preferences where they exist.
Frequently Asked Questions
Can I use LEFT JOIN with more than two tables?
Yes. You can chain multiple LEFT JOINs together. Each one adds another table to your results. The order matters — each LEFT JOIN keeps all rows from the combined result so far and adds data from the next table. This can create many rows if each join has multiple matches.
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
They are the same thing. LEFT OUTER JOIN is the full formal name, but most people and databases accept LEFT JOIN as shorthand. You will see both in real code.
How do I find rows that only exist in the left table?
Use a LEFT JOIN and filter for NULL values in a column from the right table. For example: WHERE orders.order_id IS NULL. This shows customers with no orders.
Does the order of tables in a LEFT JOIN matter?
Yes, completely. The first table (in the FROM clause) is the left table and keeps all its rows. The second table (in the LEFT JOIN clause) is the right table and only contributes matching rows. Switching them changes your results.
What happens if the ON condition matches multiple rows?
The left table row repeats once for each match in the right table. If a customer has three orders and you join customers to orders on customer_id, that customer appears three times in the output — once per order.