SQL JOIN without the confusion

In brief

The lesson explains how SQL table joins work. INNER JOIN keeps only matching pairs, LEFT JOIN preserves every row from the left table, and FULL JOIN preserves rows from both sides. The main mistakes are filtering the right table in WHERE after a LEFT JOIN and multiplying rows because the join key is not unique.

Key points

• 0:35JOIN combines rows from two tables using a condition in ON.
• 1:36INNER JOIN returns only rows that have a matching pair.
• 3:08LEFT JOIN keeps every row on the left; missing matches become NULL.
• 4:50A WHERE condition on the right table can turn a LEFT JOIN into an INNER JOIN.
• 6:11FULL JOIN keeps rows from both tables.
• 7:35A non-unique key on the right multiplies rows on the left — compare row counts before and after.
• 9:50An anti-join — LEFT JOIN … WHERE right.id IS NULL — finds rows without a match.

Chapters

1. 0:00Why tables need joins
2. 1:36INNER JOIN
3. 3:08LEFT and RIGHT JOIN
4. 6:11FULL JOIN
5. 7:35Row multiplication
6. 9:50Finding rows without a match

Terms

Join key 0:35

The columns used to compare rows in the ON condition.

LEFT JOIN 3:08

A join that preserves every row from the left table.

Anti-join 9:50

A query that returns rows with no match in another table.

Takeaways

• Put filters on the right table in ON when you need LEFT JOIN semantics.
• Count rows before and after a join.
• To find “no match” rows, use LEFT JOIN + IS NULL or NOT EXISTS.

Check yourself

1. What does INNER JOIN return for a row with no match?

   A row filled with NULLNothingAn error
2. What happens to a LEFT JOIN if you add WHERE orders.status = 'paid'?

   NothingIt effectively becomes an INNER JOINIt becomes a FULL JOIN
3. Why did the result contain more rows than the left table?

   The key on the right is not uniqueLEFT JOIN always does thatBecause of NULL

Questions about the video