Lesson · ⏱ 7 min read

🧩 SQL JOINs: combining tables

Q: You need every product, including ones that never sold. Which join?

LEFT JOIN from products to sales. A LEFT JOIN keeps all products even when no sales row matches. An INNER JOIN would drop them.

🏠 Real-life example

Your school kept one register for student names and roll numbers, and another for marks by roll number. To print 'name + marks', you match the two registers on roll number. A JOIN does that matching for database tables.

💡 In simple words

Databases split data into separate tables to avoid repetition, such as customers and orders, linked by a key like customer_id. A JOIN brings them back together. An INNER JOIN keeps only rows that match in both tables. A LEFT JOIN keeps every row from the left table, with blanks where there's no match, which is perfect for questions like 'customers who never ordered'. Joining on the wrong key, or getting duplicate matches, is the most common source of wrong numbers.

🎬 See how it works

INNER JOIN keeps only customers who have orders (the overlap). LEFT JOIN keeps every customer, with or without orders.

🪜 Step by step

Find the shared key: orders.customer_id matches customers.id.
INNER JOIN keeps only matches: customers who have orders.
LEFT JOIN keeps every row from the left table; missing matches become NULL. Customers with no orders show NULL order columns.
Find what's missing: LEFT JOIN ... WHERE orders.id IS NULL lists customers who never ordered.
Check row counts before and after a join. If they jump unexpectedly, your key isn't unique and rows are being duplicated.

🔑 Key terms

Primary / foreign key: An ID that uniquely identifies a row, and the column in another table that points to it.
INNER JOIN: Only rows that match in both tables.
LEFT JOIN: All rows from the left table, plus matches from the right if there are any.

👀 See it in action

sql

-- Customers who have never placed an order
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

A LEFT JOIN plus 'IS NULL' finds the rows with no match.

⚠️ Common mistakes to avoid

Joining on a non unique key and double counting revenue. Aggregate first, or join on a truly unique key.
Putting a filter on the right table in WHERE after a LEFT JOIN, which silently turns it into an INNER JOIN. Put that condition in the ON clause.

✅ Quick check

You need every product, including ones that never sold. Which join?

LEFT JOIN from products to sales
INNER JOIN products with sales
No join is needed

Show answer

A. LEFT JOIN from products to sales: A LEFT JOIN keeps all products even when no sales row matches. An INNER JOIN would drop them.

🙋 Questions learners ask

When do I use RIGHT or FULL OUTER JOIN?

RIGHT JOIN is just a LEFT JOIN with tables swapped, so most people use LEFT. FULL OUTER keeps unmatched rows from both sides, useful for reconciling two systems.

Are joins slow?

Not with indexes on the join keys and sensible filters. In warehouses, joining huge tables can be costly, so filter early.

📌 Remember

INNER = only matches. LEFT = keep everything on the left.

Learn this as part of a full course

Track your progress, take the quick checks and follow the lessons in the right order. This lesson is part of:

AI Foundations Path · Data Engineer (AI focus)

Continue in the course