Understanding SQL JOIN: Fixing a Common Beginner Mistake

[Rajeev Bagra]

If you’re learning SQL (especially through courses like Harvard’s CS50 Introduction to SQL), one of the most common mistakes is trying to combine tables using multiple SELECT statements.

Let’s walk through a real example and understand the right way to do it.

---

🎯 The Problem

You want to:

• Get the school name
• From the school table
• Where the graduation rate is 100%
• Graduation data is stored in another table called graduation_rates

A beginner might write something like this:

SELECT name
FROM school

SELECT school_id
FROM graduation_rates

WITH school.id = graduation_rates.school_id
AND graduation_rates.graduated = 100;

❌ This will NOT work.

Let’s understand why.

---

🧠 Understanding the Database Structure

Imagine your tables look like this:

📘 school table

id	name
1	Green Valley HS
2	Sunrise Academy
3	Blue Ridge High

📗 graduation_rates table

school_id	graduated
1	95
2	100
3	87

Notice:

• school.id connects to graduation_rates.school_id
• This is called a foreign key relationship

---

❌ Why the Original Query Fails

1️⃣ SQL Does Not Allow Multiple SELECT Statements Like That

You cannot stack independent SELECT statements to combine data.

2️⃣ WITH Is Used for CTEs (Common Table Expressions)

WITH is NOT used to join tables. It’s used like this:

WITH example AS (
    SELECT ...
)
SELECT * FROM example;

So the usage was incorrect.

---

✅ The Correct Solution: Using JOIN

To combine data from two related tables, use JOIN.

SELECT school.name
FROM school
JOIN graduation_rates
ON school.id = graduation_rates.school_id
WHERE graduation_rates.graduated = 100;

---

🔍 Step-by-Step Breakdown

Step 1: FROM school

Start with the school table.

Step 2: JOIN graduation_rates

Tell SQL you want to combine another table.

Step 3: ON school.id = graduation_rates.school_id

Define how the tables are related.

Step 4: WHERE graduated = 100

Filter only schools with 100% graduation rate.

---

🧮 What SQL Internally Does

SQL temporarily creates something like this:

id	name	school_id	graduated
1	Green Valley HS	1	95
2	Sunrise Academy	2	100
3	Blue Ridge High	3	87

Then applies:

WHERE graduated = 100

Final result:

name
Sunrise Academy

---

🆚 Alternative (Old Style JOIN)

Older SQL syntax allowed this:

SELECT school.name
FROM school, graduation_rates
WHERE school.id = graduation_rates.school_id
AND graduation_rates.graduated = 100;

It works — but modern SQL prefers explicit JOIN.

---

🔄 Bonus: What If Some Schools Have No Graduation Data?

Use LEFT JOIN:

SELECT school.name
FROM school
LEFT JOIN graduation_rates
ON school.id = graduation_rates.school_id;

This includes all schools, even if they don’t have graduation data.

---

🧠 Key Concepts to Remember

Concept	Meaning
Primary Key	Unique ID in a table (school.id)
Foreign Key	Reference to another table (graduation_rates.school_id)
JOIN	Combines rows from two tables
WHERE	Filters rows

---

🚀 Practice Exercise

Try modifying the query to:

• Show schools with graduation rate greater than 90
• Show both school name and graduation rate
• Count how many schools have 100% graduation

Example:

“`sql
SELECT school.name, graduation_rates.graduated
FROM school
JOIN graduation_rates
ON school.id = graduation_rates.school_id
WHERE graduation_rates.graduated > 90;

[Author]

Posts