What a left join does
A left join is a way to combine rows from two database tables based on a matching condition. It keeps all rows from the first (left) table and adds matching data from the second (right) table wherever it exists. If no match is found, the columns from the right table show as empty.
Think of it like a contact list where you have names and phone numbers in one table, and email addresses in another. A left join would show every name from your original list, and fill in the email address next to it if you have one on file — but it would not drop anyone just because their email is missing.
Left joins are one of the most common operations in database work because they let you preserve your original data while enriching it with information from somewhere else.
Key Takeaways
- A left join keeps every row from the left table and adds matching rows from the right table, leaving empty cells where no match exists.
- The order of tables matters — the left table is the one you start with, and the right table is the one you are pulling data from.
- You specify the matching condition using an ON clause, which tells the database which columns in each table should be compared.
- Left joins are useful when you want to keep all your original records and add extra information only where it is available.
The basic structure of a left join
A left join query has a specific format that tells the database which tables to combine and how to match them. The basic pattern looks like this: you name your left table, state that you want a LEFT JOIN, name your right table, and then specify the matching condition with an ON clause.
The ON clause is where you tell the database which columns should be compared. For example, if both tables have a customer ID column, you would write ON left_table.customer_id = right_table.customer_id. The database then walks through every row in the left table, looks for matching rows in the right table using that condition, and combines them on the same output row.
If a row in the left table has no match in the right table, the database still includes that row in the results — it just leaves the columns from the right table blank (these blank values are called NULL in database language).
Left join versus inner join
The difference between a left join and an inner join comes down to what happens when there is no match. An inner join only returns rows where a match exists in both tables. A left join returns all rows from the left table, whether or not a match exists on the right.
Using the contact list example again: an inner join would show only people who have both a phone number and an email address. A left join would show everyone, with blank email fields for people who do not have one on file. If you need to find gaps in your data — customers with no email, orders with no shipping address — a left join is the tool that reveals them.
Inner joins are useful when you only care about complete records. Left joins are useful when you want to see the full picture, including what is missing.
A practical example with real columns
Imagine you have a customers table with columns for customer_id, name, and city. You also have an orders table with columns for order_id, customer_id, and order_date. You want to see each customer's name and city alongside their most recent order date, if they have one.
A left join would start with the customers table (the left side) and pull in the order_date from the orders table (the right side) wherever the customer_id values match. Every customer appears in the output, even those who have never placed an order — their order_date column would be NULL.
If you had used an inner join instead, only customers with at least one order would appear. You would lose visibility into which customers have never ordered, which is often important information for business decisions.
When to use a left join
Use a left join when your primary interest is the left table and you want to add information from the right table without losing any rows. Common situations include finding missing data, auditing records, or creating reports that show the full roster of something with optional details filled in.
For example, a manager might want a list of all employees with their department assignments. If some employees have not been assigned to a department yet, a left join keeps them in the report with a blank department field. An inner join would hide those unassigned employees entirely.
Left joins are also the right choice when you are troubleshooting data problems. If you suspect that some records in one table have no corresponding records in another, a left join will show you exactly which ones.
Common mistakes when writing left joins
The most frequent error is getting the table order backwards. Remember that the left table is the one you list first in the FROM clause, and the right table is the one you name after LEFT JOIN. Swapping them changes which rows are preserved and which are optional.
Another common mistake is writing the ON condition incorrectly. The condition must compare columns that actually exist in both tables and that represent the same kind of data. If you accidentally compare a customer ID to an order ID, the database will find no matches and return all rows with NULL values on the right side.
A third mistake is forgetting that NULL values (blanks) in the output do not mean the data is wrong — they mean no match was found. When you filter results afterward, remember to account for these NULL values or you may accidentally exclude the rows you were trying to find.
Frequently Asked Questions
Can I use a left join with more than two tables?
Yes. You can chain multiple left joins together, with each one adding another table to the result. The order matters — each join is processed left to right, so the first join uses the original left table, and each subsequent join uses the result of the previous join as its left side.
What does NULL mean in a left join result?
NULL means no matching row was found in the right table for that particular row in the left table. It is not an error — it is the expected behavior of a left join. When you filter or count results, you often need to decide whether to include or exclude NULL values.
Is a left join slower than an inner join?
Not necessarily. The performance depends on the size of the tables, the indexes available, and how the database engine processes the query. A left join may be slightly slower in some cases because it has to preserve all left table rows, but the difference is usually small enough that you should choose the join type based on what data you need, not on speed assumptions.
Can I use a left join to find rows that do not match?
Yes. Write your left join normally, then add a WHERE clause that filters for NULL values in a column from the right table. This shows you every row in the left table that has no match in the right table — a useful technique for finding missing data or orphaned records.