Choosing the Right Technique

You now have seven tools that can all "hide complexity". Choosing the wrong one leads to slow dashboards, stale data or code nobody can find. This topic puts them side by side.

Definition — the key questions

Choosing an advanced SQL technique comes down to three questions: Does it need to store data? How long must it live? Who needs to reuse it?

The big picture

Example — same question, five ways

"Which teams scored 700+ runs?"

-- 1. Subquery
SELECT * FROM (SELECT team, SUM(runs) AS total FROM players GROUP BY team) AS t
WHERE total >= 700;

-- 2. CTE
WITH t AS (SELECT team, SUM(runs) AS total FROM players GROUP BY team)
SELECT * FROM t WHERE total >= 700;

-- 3. View (saved for everyone)
CREATE VIEW v_team_totals AS
SELECT team, SUM(runs) AS total FROM players GROUP BY team;
SELECT * FROM v_team_totals WHERE total >= 700;

-- 4. CTAS (SQL Server: SELECT … INTO) — a stored snapshot
SELECT team, SUM(runs) AS total INTO team_totals_snapshot FROM players GROUP BY team;
SELECT * FROM team_totals_snapshot WHERE total >= 700;

-- 5. Temp table — private to your session
SELECT team, SUM(runs) AS total INTO #team_totals FROM players GROUP BY team;
SELECT * FROM #team_totals WHERE total >= 700;

All five return:

The difference is not the answer — it is lifetime, storage, freshness and who can reuse it.

Decision guide

Freshness vs speed

Advanced SQL cheat sheet

Common mistakes

• ❌ Using a view to "speed up" a slow query → it still runs the query each time. ✅ CTAS / indexed view.
• ❌ Using a CTAS table for data that must always be current → stale numbers. ✅ view.
• ❌ Creating permanent tables for scratch work and forgetting to drop them. ✅ #temp tables clean themselves up.
• ❌ Hiding business rules in many triggers → surprising side effects. ✅ prefer stored procedures for explicit jobs.

Interview tip

Q: CTE vs temp table vs view — when would you use each? A CTE to make one complex query readable (or recursive); a temp table when an intermediate result is large or reused by several statements in your session; a view when the logic must be shared and reused by many users, possibly to hide columns for security.

Next up: Performance Optimization — make your SQL fast.