SQL Learning Post: Understanding “JOIN”, “WHERE”, and “IN” Through the Moneyball Challenge

[Rajeev Bagra]

Today’s SQL discussion covered one of the most important beginner topics:

How tables connect, how rows are filtered, and why “IN” is useful.

Using the CS50 Moneyball project as context, let’s break it down clearly.

---

1. “JOIN” Connects Tables

Suppose we have three tables:

• “players”
• “salaries”
• “performances”

To combine their data:

FROM players
JOIN salaries
ON players.id = salaries.player_id
JOIN performances
ON players.id = performances.player_id

This means:

• Match each player with their salary row
• Match each player with their performance row

So “JOIN … ON …” tells SQL how rows relate.

Think of it as connecting puzzle pieces.

---

1. “WHERE” Filters Rows

After tables are joined, we often want only certain rows:

WHERE salaries.year = 2001
AND performances.year = 2001
AND performances.H > 0

This means:

• Only year 2001 data
• Only players with more than 0 hits

So:

• “JOIN” = connect rows
• “WHERE” = keep only rows matching conditions

---

1. Sometimes “WHERE” Feels Like Joining

Example:

WHERE salaries.player_id = performances.player_id

This compares two joined tables and keeps only matching rows.

So while written in “WHERE”, it behaves like an extra join condition.

Modern style often places such logic inside “ON”.

---

1. What Does “IN” Mean?

Example:

WHERE id IN (3, 7, 12)

Means:

«Keep rows where id is 3, 7, or 12.»

Equivalent to:

WHERE id = 3
OR id = 7
OR id = 12

---

1. “IN” With a Subquery

Very powerful usage:

WHERE id IN (
SELECT player_id
FROM salaries
)

Meaning:

«Keep players whose IDs appear in the salary table.»

---

1. Why “IN” Was Useful in Problem 12

The task asked:

Find players who are:

• Among the 10 cheapest per hit
  and
• Among the 10 cheapest per RBI

So:

WHERE id IN (cheap_per_hit_list)
AND id IN (cheap_per_rbi_list)

This finds the intersection of both groups.

---

1. Is “IN” Mandatory?

No.

You could also use:

• “JOIN”
• “EXISTS”
• “INTERSECT”

But “IN” is often easier to read.

---

1. Common Beginner Mistakes

Typo in table names

peformances

instead of:

performances

Alias mismatch

Created:

AS “dollars per hit”

Ordered by:

“dollar per hit”

Even one missing letter matters.

---

1. Golden Rule for SQL Beginners

Use:

• “JOIN … ON …” → relationships
• “WHERE” → filtering
• “IN” → value exists in a list/set

---

1. Final Thought

SQL is less about memorizing syntax and more about asking:

• Which tables do I need?
• How do they connect?
• Which rows should remain?
• How should results be sorted?

Master those four questions, and SQL becomes much easier.

[Author]

Posts