Correlated Subqueries & EXISTS

"Who scored more than their own team's average?" The average is different for each team, so it cannot be calculated once. The inner query must be re-run for each player, using that player's team. That is a correlated subquery.

Definition

A non-correlated subquery is independent of the outer query: it can run on its own, and it runs once before the outer query uses its result. A correlated subquery refers to a column of the outer query's current row, so it cannot run on its own and is (logically) evaluated once per outer row.

The link is an outer column used inside the subquery, usually through an alias: WHERE q.team = p.team — here p belongs to the outer query.

Note: "Once per row" describes the meaning. The SQL Server optimizer often rewrites correlated subqueries into joins internally, so they are not always slow — but on large tables, check the execution plan.

Syntax — correlated subquery

SELECT o.column_list
FROM table_name AS o                       -- outer alias
WHERE o.column > (
    SELECT aggregate(i.column)
    FROM table_name AS i                   -- inner alias
    WHERE i.link_column = o.link_column    -- the correlation
);

Example — above their own team's average

SELECT id, name, team, runs
FROM players AS p
WHERE runs > (
    SELECT AVG(runs)
    FROM players AS q
    WHERE q.team = p.team
);

How it works:

1. Take one player row p (e.g. Rohit, team MI).
2. Run the inner query for his team → MI average = (500 + 20) / 2 = 260.
3. Keep the row if 500 > 260 → yes.

Every other team has only one player, so each of them equals (not beats) their team average. Only Rohit survives.

Definition — EXISTS

EXISTS is a logical operator that returns TRUE if the subquery returns at least one row, and FALSE if it returns no rows. NOT EXISTS is the opposite. The values the subquery selects are ignored — only "any row or no row" matters.

That is why people write SELECT 1 inside EXISTS — the 1 is irrelevant; SELECT * works the same.

Syntax — EXISTS

SELECT columns
FROM table_a AS a
WHERE EXISTS (
    SELECT 1
    FROM table_b AS b
    WHERE b.link_column = a.link_column
);

Example — players with an Orange Cap

SELECT id, name, runs
FROM players AS p
WHERE EXISTS (
    SELECT 1
    FROM orange_cap AS o
    WHERE o.player_id = p.id
);

Step by step: take one player row → run the inner query for it → any row found? keep the player. The database can stop searching at the first match, which makes EXISTS efficient.

Example — NOT EXISTS: teams with no players

SELECT t.team, t.team_name
FROM teams AS t
WHERE NOT EXISTS (
    SELECT 1
    FROM players AS p
    WHERE p.team = t.team
);

NOT EXISTS is NULL-safe, unlike NOT IN, which is why it is the recommended way to write "rows with no match".

JOIN vs subquery — same answer

Often both work. Pick the clearer one.

-- Subquery: great for filtering / existence checks
SELECT name, runs
FROM players
WHERE id IN (SELECT player_id FROM orange_cap);

-- JOIN: needed when you want columns from BOTH tables
SELECT p.name, p.runs, o.season
FROM players AS p
INNER JOIN orange_cap AS o
    ON p.id = o.player_id;

Tip: A JOIN can duplicate outer rows if the other table has several matches (e.g. a player who won the Orange Cap twice). IN / EXISTS never duplicate.

Common mistakes

• ❌ Forgetting the correlation: WHERE EXISTS (SELECT 1 FROM orange_cap) → TRUE for every player as long as the table has any row. ✅ add WHERE o.player_id = p.id.
• ❌ Using the same table twice without aliases → SQL cannot tell which team you mean. ✅ alias both (p and q).
• ❌ Trying to run a correlated subquery on its own to debug it → error "could not be bound". ✅ replace the outer column with a literal value to test it.

Interview tip

Q: EXISTS vs IN — which is better? Both find matching rows. EXISTS stops at the first match and is NULL-safe in its NOT EXISTS form, so it is the safer choice for "has no match" checks. For small lists, modern optimizers usually produce the same plan for IN and EXISTS.