Lesson 3 of 15 · 16 min
LEFT JOIN: keep every row on the left
By the end of this lesson you can write a LEFT JOIN that keeps every row of the main table, read the NULLs it produces, and count matches correctly with COUNT(column) and COALESCE.
The inner join has one property that causes more wrong reports than any other: rows without a match disappear. Ask “how many orders did each customer place?” with an inner join and the customers who placed none are not in the answer at all. The report doesn't say 0; it says nothing, and nobody notices the missing line.
A LEFT JOIN (in full, LEFT OUTER JOIN) changes one thing: every row of the left table is kept, whether or not it finds a match.
- A left row with matches behaves exactly as in an inner join: one result row per match.
- A left row with no match still produces one result row. The columns that would have come from the right table are filled with NULL.
“Left” simply means the table written first, before the LEFT JOIN keyword. It is the table whose rows you promise to keep.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons