AI Hints
Homeโ€บSQL Tutorialโ€บInner and Left Joins
SQL ยท Lesson 45 of 126

Inner Joins Drop Rows

What happens with no match.

โฑ 2 min readโ–ถ Video at 1:03:27๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Here's something important about the join you've been using. It's called an inner join. And it only keeps rows that have a match on both sides. Rows without a partner quietly disappear. Sometimes that's exactly what you want. Sometimes it hides the most interesting people.

Inner Joins Drop Rows
From the video: Inner Joins Drop Rows
  • The plain JOIN is an INNER JOIN
  • Only matched rows survive
  • Unmatched rows vanish

Matches only

Think of two circles. One holds customers, the other holds orders. An inner join keeps only the overlap. Customers who have placed at least one order. A customer who signed up, but never bought anything, has no partner in orders, so they're dropped.

Who survives an inner join?

Let's count how many different customers survive an inner join with orders. Run it. Four hundred and sixty three. But Helen has five hundred customers. So thirty seven people vanished from the result, without any warning.

query.sqlsql
SELECT COUNT(DISTINCT c.customer_id) AS customers_with_orders
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ customers_with_orders โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 463                   โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

An inner join keeps only rows with a match on both sides. Unmatched rows disappear silently. Next, the join that keeps them.

Key takeaways
  • JOIN = INNER JOIN
  • Keeps matches only
  • Unmatched rows vanish
Next up: LEFT JOIN โ€” keep every row on the left