Tips 17–20: Aggregations & Subqueries

Reports like "total runs per team per season" run aggregations over huge tables, and analysts love subqueries. These four tips keep both fast.

Tip 17 — Columnstore for heavy aggregates

Use columnstore indexes for large analytic aggregations.

-- ❌ Bad: SUM over 100M rows stored as rowstore - reads every column of every row
SELECT team, SUM(runs) FROM ball_by_ball GROUP BY team;

-- ✅ Good
CREATE CLUSTERED COLUMNSTORE INDEX CCI_ball_by_ball ON ball_by_ball;

After this, the same SUM reads only the compressed team and runs segments in batch mode. (See the Rowstore vs Columnstore topic.)

Tip 18 — Pre-aggregate results

Store summaries in a separate table for fast reporting, instead of recalculating SUM() on every report run.

-- ❌ Bad: dashboard recalculates totals from raw innings every page load
SELECT team, SUM(runs) FROM innings GROUP BY team;

-- ✅ Good (SQL Server): build a summary table once (e.g. nightly)
SELECT team, COUNT(*) AS innings_played, SUM(runs) AS total_runs
INTO team_totals
FROM innings
GROUP BY team;

SELECT … INTO creates the new table and fills it in one step (SQL Server only). The portable form is CREATE TABLE + INSERT INTO … SELECT:

The report now reads 4 rows instead of aggregating millions. The trade-off: the summary is only as fresh as its last refresh. (SQL Server also offers indexed views, which keep a summary automatically up to date.)

Tip 19 — JOIN, EXISTS or IN?

Know when to use each, and avoid IN with very large lists.

-- ❌ Bad: a huge literal list
SELECT name FROM players WHERE id IN (1, 2, 3, /* ... 50,000 ids ... */ 99999);

-- ✅ Good: check existence against a table
SELECT p.name
FROM players AS p
WHERE EXISTS (
    SELECT 1
    FROM innings AS i
    WHERE i.player = p.name
);

The same question with INNER JOIN innings returns 12 rows (each player once per innings) — you would need DISTINCT to fix it, which costs a sort. EXISTS returns each player once with no extra work.

Trap — NOT IN with NULLs: WHERE x NOT IN (subquery) returns no rows at all if the subquery returns even one NULL. In our data, David Warner's profile has team = NULL, so SELECT name FROM players WHERE team NOT IN (SELECT team FROM player_profiles WHERE country IN ('Australia','England')) returns 0 rows, while the NOT EXISTS version correctly returns 5. Prefer NOT EXISTS.

Tip 20 — Simplify with a CTE

Use a CTE (WITH … AS) to remove repeated logic and make the query readable.

-- ❌ Bad: the same subquery pasted twice
SELECT team, total_runs
FROM (SELECT team, SUM(runs) AS total_runs FROM innings GROUP BY team) AS x
WHERE total_runs > (
    SELECT AVG(total_runs)
    FROM (SELECT team, SUM(runs) AS total_runs FROM innings GROUP BY team) AS y
);

-- ✅ Good: define it once
WITH team_totals AS (
    SELECT team, SUM(runs) AS total_runs
    FROM innings
    GROUP BY team
)
SELECT team, total_runs
FROM team_totals
WHERE total_runs > (SELECT AVG(total_runs) FROM team_totals)
ORDER BY total_runs DESC;

The average team total is about 161, so only RR and RCB are above it.

Note: in SQL Server a CTE is not stored/cached — referencing it twice may run its query twice. The main gain is readability and fewer copy-paste bugs. If a heavy CTE is reused many times, materialise it into a #temp table.

Common mistakes

• ❌ JOIN + DISTINCT to answer "does a match exist?" ✅ EXISTS.
• ❌ NOT IN against a nullable column. ✅ NOT EXISTS.
• ❌ A pre-aggregated table nobody refreshes. ✅ Schedule the refresh (SQL Agent job) and show "last updated".

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: EXISTS or IN — which is faster? A: For a subquery, modern optimizers often produce the same plan. The real differences: EXISTS stops at the first match and handles NULLs correctly with NOT EXISTS, while NOT IN returns nothing if the subquery has a NULL. Use IN for short fixed lists, EXISTS for existence checks against tables, and JOIN when you need columns from both sides.