(Lessons from the CS50 DESE Query)

While working on 7.sql in
CS50’s Introduction to Databases with SQL, we encountered two extremely common SQL issues:

1. Incorrect logical grouping with AND and OR
2. The “ambiguous column name” error after a JOIN

Both are real-world problems faced by data analysts daily. Let’s break them down clearly.

---

ߎ The Goal

We wanted to find:

Names of public or charter schools in the Cambridge district.

Initial query:

SELECT name
FROM schools
JOIN districts
ON schools.district_id = districts.id
WHERE schools.type = "Public School"
OR schools.type = "Charter School"
AND districts.name = "Cambridge";

Two separate problems appeared.

---

ߚ Problem 1: AND vs OR Precedence

❌ What We Wrote

WHERE schools.type = 'Public School'
OR schools.type = 'Charter School'
AND districts.name = 'Cambridge';

ߧ How SQL Interprets This

SQL evaluates:

AND before OR

So it reads this as:

schools.type = 'Public School'
OR (
schools.type = 'Charter School'
AND districts.name = 'Cambridge'
)

ߔ What That Means

• ALL Public Schools (from any district) are returned.
• PLUS Charter Schools in Cambridge.

That’s not what we wanted.

---

✅ The Correct Logical Grouping

We must control evaluation using parentheses:

WHERE (schools.type = 'Public School'
OR schools.type = 'Charter School')
AND districts.name = 'Cambridge';

Even better:

WHERE s.type IN ('Public School', 'Charter School')
AND d.name = 'Cambridge';

ߎ Lesson #1

When mixing AND and OR, always use parentheses.

Never rely on implicit operator precedence.

---

ߚ Problem 2: Ambiguous Column Name

After fixing logic, we ran:

SELECT name
FROM schools
JOIN districts
ON schools.district_id = districts.id
WHERE (schools.type = 'Public School'
OR schools.type = 'Charter School')
AND districts.name = 'Cambridge';

SQLite responded:

Parse error: ambiguous column name: name

ߧ Why This Happened

Both tables contain a column called name.

After the JOIN, SQL sees:

• schools.name
• districts.name

So when you write:

SELECT name

SQL doesn’t know which one you mean.

That’s what “ambiguous” means.

---

✅ The Fix: Qualify the Column

Specify the table:

SELECT schools.name

Or better, use aliases:

SELECT s.name
FROM schools s
JOIN districts d
ON s.district_id = d.id
WHERE s.type IN ('Public School', 'Charter School')
AND d.name = 'Cambridge';

---

ߏ Final Correct Query

SELECT s.name
FROM schools s
JOIN districts d
ON s.district_id = d.id
WHERE s.type IN ('Public School', 'Charter School')
AND d.name = 'Cambridge';

Now:

✔ Logic is correct
✔ Columns are unambiguous
✔ Query is production-ready

---

ߓ Key Takeaways

1️⃣ AND Has Higher Precedence Than OR

Always use parentheses when combining them.

2️⃣ After a JOIN, Fully Qualify Columns

If two tables share column names (like id, name, date), ambiguity will occur.

3️⃣ Use Aliases

They make queries shorter and clearer.

4️⃣ Read Error Messages Carefully

SQLite told us exactly what was wrong.

---

ߒ Why This Matters in Real Systems

In real analytics work:

• Dashboards depend on correct logic
• Financial reports rely on precise filtering
• Multiple tables often share common column names

Small SQL mistakes can distort entire datasets.

Learning to debug both logical errors and ambiguity errors is a major milestone in becoming a confident SQL practitioner.

---