Combining Data: JOINs vs SET

Real data is never in one table. In our IPL database, players knows that Virat Kohli plays for RCB, but only teams knows that RCB means Royal Challengers Bengaluru from Bengaluru. To answer "Which city does each player play for?" you must combine the two tables. SQL gives you exactly two tools for this.

The two methods of combining data

Combining data means producing one result set from two or more tables (or queries). SQL does it in two directions: JOINs combine columns (tables placed side by side), and SET operators combine rows (query results stacked on top of each other).

A simple way to remember: JOIN = wider result, SET = longer result.

What is a SQL JOIN?

A JOIN is an operation in the FROM clause that combines rows from two tables into one wider row, whenever a join condition (written after ON) is true — usually when a key column in one table equals a key column in the other.

• Key column — a column whose values identify the same "thing" in both tables. Here it is the team code: players.team and teams.team.
• Join condition — the rule that decides which rows belong together: ON p.team = t.team.
• Combined row — the columns of the player row plus the columns of the matching team row, in one line.

Example — combine players with teams

SELECT p.name, p.team, t.team_name, t.city
FROM players AS p
INNER JOIN teams AS t
    ON p.team = t.team;

How it works: for every player, SQL looks into teams for the row whose team equals the player's team, and glues the two rows together. p and t are table aliases — short nicknames so we can write p.name instead of players.name.

Why do we join tables? (three reasons)

Join possibilities — which rows do you want?

Think of two overlapping circles: A (players) on the left, B (teams) on the right. The overlap is the rows that match.

No JOIN — when you don't need to combine

No JOIN means querying each table separately: SELECT * FROM A; SELECT * FROM B;. You get two independent result sets, nothing is matched.

SELECT * FROM players;
SELECT * FROM teams;

The first returns all 6 players (A), the second all 7 teams (B). Use this when you just want to look at the tables, or when the two results answer two different questions. Don't join "just because" — every join costs work and can change row counts.

Note: Before writing any join, always look at both tables separately first (No JOIN). Knowing that teams has 7 rows and players has 6 helps you sanity-check every join result.

Common mistakes

• ❌ Thinking UNION "joins" tables. ✅ UNION stacks rows; it never matches on a key.
• ❌ Joining on columns that only look related (e.g. p.name = t.team_name). ✅ Join on a real key that means the same thing in both tables.
• ❌ Forgetting the alias prefix when both tables have a column with the same name (team) — SQL Server raises "Ambiguous column name 'team'". ✅ Write p.team or t.team.

Interview tip

Q: What is the difference between a JOIN and a UNION? A: A JOIN combines columns from two tables by matching rows on a condition, so the result gets wider. A UNION combines the rows of two queries with the same column structure, so the result gets longer — no matching condition is involved.