Databases
LEFT JOIN (keep the unmatched rows too)
O(n + m) with a hash index on the join key, the same as a normal join. O(n × m) with a simple nested-loop match.
The idea, in plain English
A plain JOIN (see the Joining Tables lesson) keeps only rows that find a match on both sides. LEFT JOIN is like a class attendance sheet. You list every student (the 'left' table), even one who never submitted an assignment (the 'right' table). A student with no submission still shows up, with blanks (NULL) where the assignment details would be.
How it works
- 1Start from every row in the 'left' table, such as all users. None of them get dropped, no matter what.
- 2For each left row, look for a matching row or rows in the right table, using the shared key. This works the same as a normal join.
- 3If a match exists, combine both rows as usual. If no match exists, keep the left row anyway, and fill the right side's columns with NULL. NULL is SQL's way of saying 'no value here'.
When you'd use it
Use LEFT JOIN whenever you need 'everyone from the main list, plus whatever extra information exists'. For example: every user, even one who never placed an order; or every product, even one nobody has reviewed yet.
Common beginner mistakes
- Do not mix up the direction. LEFT JOIN keeps every row from the left (first-named) table. RIGHT JOIN is the mirror image: it keeps every row from the right table instead.
- A row with no match comes back with NULL in the joined columns. Code that does math on it, such as amount + tax, can crash unless you check for NULL first.
Try it — edit and run
Click the code to edit · press ⌘/Ctrl+↵ to run
Editable code. Tab and Shift+Tab indent. Press Escape, then Tab, to move focus out of the editor.
SQL: SELECT users.name, orders.item, orders.amount FROM users LEFT JOIN orders ON users.id = orders.user_id
Ana Book 15
Ana Lamp 25
Bilal Pen 2
Cara NULL NULL
Dan NULL NULLNot sure this is the right topic? See the learning paths → or where this leads →