Data in Multiple Tables

Left Joins

---

In the previous chapter, we used inner joins to keep only matching pairs of rows. The natural next question is what happens if we also want to see rows that have no match. This is where LEFT JOIN becomes useful.

Start from the Question

Suppose the question is: show all exercises and the submissions to those exercises.

If we used an inner join to answer that question, the result would only show exercises that have at least one submission. If an exercise has no submissions, it would not appear in the result at all.

Since the question says “show all exercises,” that would be a problem. The question asks for all exercises, but an inner join would omit the exercises that have no submissions.

Instead of starting to write the SQL right away, it helps to read the question carefully first. The wording often points toward the join type. If the question is “show only exercises that have submissions,” an inner join is usually the better fit. If the question is “show all exercises, even if they have no submissions yet,” a left join is usually the better fit.

That habit keeps the SQL connected to the information need instead of treating the join type as a purely syntactic choice. Once the wording is clear, the join type often becomes much easier to pick.

Loading Exercise...

Left Join Keeps Unmatched Rows

A LEFT JOIN keeps every row from the left-hand table, that is, the table listed first in the query.

SELECT
  e.id,
  e.title,
  s.submitted_at,
  s.score
FROM exercises AS e
LEFT JOIN submissions AS s ON s.exercise_id = e.id;

Read this as: start from exercises, try to attach matching submissions, but keep the exercise row even if no submission exists.

The keep the exercise row even if no submission exists part is the key difference from an inner join.

A possible result of the query could look like this:

id	title	submitted_at	score
1	SQL Basics	2026-03-10 12:00:00	9
2	Filtering Queries	NULL	NULL

In the second row, the exercise appears even though there is no matching submission row. The non-matching rows are kept, but the missing submission columns are filled with NULL values.

If a matching right-side row exists, its values appear normally. If no matching right-side row exists, the left-side row still stays, but the missing right-side columns become NULL.

In LEFT JOIN queries, NULL does not mean “bad data.” It means that there was no matching row for those columns.

With an inner join, the second row would not appear at all, because there is no matching submission row to keep it in the result.

So the practical difference between an inner join and a left join is:

• an inner join removes unmatched exercises,
• a left join keeps unmatched exercises and fills the missing submission columns with NULL.

Loading Exercise...

Table Order and Left Join

The word “left” tells us which side must stay. The following two questions would be very different:

• show all exercises, even if they have no submissions,
• show all submissions, even if they had no matching exercise.

The first question suggests exercises LEFT JOIN submissions. The second question suggests the opposite order.

So the main check here is simple: which side is the query promising to keep visible even when no match exists? If the question is “show all exercises,” then exercises belongs on the left. If the question is “show all submissions,” then submissions belongs on the left instead. The two tables may be the same, but the promise made by the query wording is different.

Loading Exercise...

Build the Left Join from Blocks

At this point, it is useful to practice the whole decision at once: choose the left-side table, choose LEFT JOIN, and choose the join condition that follows the relationship.

Loading Exercise...

A Brief Note on Other Outer Joins

In everyday application work, LEFT JOIN is by far the most common outer join.

RIGHT JOIN exists, but it is often clearer to swap the table order and write a LEFT JOIN instead.

FULL OUTER JOIN also exists, but it appears less often in introductory application development. For this chapter, the important idea is understanding when a left join is needed and why NULL appears afterward.

Loading Exercise...

Check Your Understanding

1. What is the main difference between INNER JOIN and LEFT JOIN?
2. Why can the same two tables give different results if their order is swapped in a LEFT JOIN?
3. Why do NULL values often appear in the right-side columns of a left join result?

SQL Programming Exercise

The following SQL sequence focuses on the main chapter habit: choose LEFT JOIN when the question promises to keep all rows from the left-hand table visible.

Loading Exercise...