DEV Community

Mohamed Idris
Mohamed Idris

Posted on

LEFT JOIN, Explained Like You Are Five

#beginners #database #sql

Ever run a query and some rows just... vanished? You had 100 students, but the
result only showed 60. Where did the other 40 go? They did not get deleted.
They got dropped by your join. Let's fix that and finally understand what
LEFT JOIN really means.

The idea in one line

A join glues two tables together into one result, and LEFT JOIN says
"keep everything from the first table, even if the second one has nothing to match."

First, what is a join? (the English version)

Forget SQL for a second. Imagine you have two notebooks.

  • Notebook A: a list of students.
  • Notebook B: a list of homework they handed in.

A join is just you sitting at a desk, looking at a student in Notebook A, and
flipping through Notebook B to find their homework. You staple the two pages
together. That stapled page is one row in your result.

Notebook A (students)        Notebook B (homework)
  1. Sara                      Sara  -> "essay.pdf"
  2. Omar                      Omar  -> "math.pdf"
  3. Lina                      (Lina handed in nothing)
Enter fullscreen mode Exit fullscreen mode

The big question is: what do you do with Lina? She has no homework. Do you
throw her page away, or keep it with a blank?

That single choice is the whole difference between join types.

Left and right: which is which?

The word JOIN sits between two table names. Whatever is before JOIN is the
left table. Whatever is after is the right table.

SELECT *
FROM students          <-- LEFT table (before JOIN)
LEFT JOIN homework      <-- RIGHT table (after JOIN)
  ON students.id = homework.student_id;
Enter fullscreen mode Exit fullscreen mode

Read it out loud in plain English:

"Give me every student. If they handed in homework, staple it on.
If they did not, still keep the student and leave the homework part blank."

The word LEFT is a promise: the left table is the boss. Nobody on the left
gets left behind.

Why the order matters

Order decides who is allowed to go missing.

students LEFT JOIN homework
   ▲                  ▲
 boss            optional
(keep all)     (match if you can)
Enter fullscreen mode Exit fullscreen mode
  • students LEFT JOIN homework means: keep all students. Homework is optional.
  • homework LEFT JOIN students means: keep all homework. The student is optional.

Same two tables, but you flipped who is the boss. So the order is not just style.
It changes your answer.

See it happen

A plain INNER JOIN (the default) is strict. It only keeps rows that match on
both sides. Poor Lina gets dropped.

students INNER JOIN homework
-------------------------------
Sara  | essay.pdf
Omar  | math.pdf
        (Lina is gone! she had no homework)
Enter fullscreen mode Exit fullscreen mode

Now LEFT JOIN keeps her, with a blank (SQL calls that blank NULL):

students LEFT JOIN homework
-------------------------------
Sara  | essay.pdf
Omar  | math.pdf
Lina  | NULL          <-- she stayed, homework is just empty
Enter fullscreen mode Exit fullscreen mode

That NULL is the hero of the story. It means "this student exists, but the right
table had nothing for them."

A real case

Say your boss asks: "Which students did NOT hand in homework?"

With an INNER JOIN you literally cannot answer this. The students with no
homework already disappeared before you could see them.

With a LEFT JOIN, it is easy. Keep everyone, then look for the blanks:

SELECT students.name
FROM students
LEFT JOIN homework
  ON students.id = homework.student_id
WHERE homework.student_id IS NULL;  -- the blanks = the ones who skipped
Enter fullscreen mode Exit fullscreen mode

This is the classic "find what is missing" trick, and you will use it a lot:
users with no orders, products with no reviews, employees with no manager.

Gotchas juniors hit

  1. Putting a filter in the wrong place. If you write WHERE homework.grade > 80, you accidentally throw away the NULL rows and your LEFT JOIN acts like an INNER JOIN again. Put right-table filters in the ON part instead when you want to keep the blanks.
  2. Forgetting which table is the boss. If a row count looks too small, ask: "did I put the table I care about on the LEFT?"
  3. Thinking RIGHT JOIN is special. It is just LEFT JOIN with the tables flipped. Most people pick one side and stick with LEFT to keep their brain calm.

Recap

  • A join staples two tables together, one matching row at a time.
  • The table before JOIN is the left (the boss). The table after is the right.
  • LEFT JOIN keeps every row from the left, even with no match, filling gaps with NULL.
  • Order matters because it decides who is allowed to go missing.
  • LEFT JOIN is your go-to for "show me what is missing" questions.

Your turn

Picture a customers table and an orders table. Write one LEFT JOIN that lists
every customer who has never placed an order. Which table goes on the left,
and why? If you can explain your answer to a friend, you have got it.

Top comments (0)