SQL Style & Best Practices

Writing a correct query and writing a query that reads as senior-level are different skills — this page is the second one.

Formatting & Naming

-- Good: uppercase keywords, descriptive lowercase aliases, aligned columns
SELECT
  u.name,
  COUNT(o.id)  AS order_count,
  SUM(o.total) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC;

Avoid meaningless aliases (t1, t2, n, c) — users u / orders o costs nothing extra and makes a multi-join query traceable at a glance months later.

Avoid SELECT *

-- Fragile: transfers unnecessary columns, silently breaks if the schema changes
SELECT * FROM orders JOIN users ON orders.user_id = users.id;

-- Explicit: stable, self-documenting
SELECT orders.id AS order_id, orders.total, users.name AS customer_name
FROM orders JOIN users ON orders.user_id = users.id;

This is the SQL-level version of [[FastAPI]]'s response_model discussion — an explicit column list is both a performance choice (don't transfer unused data) and a stability contract (a later ALTER TABLE ADD COLUMN doesn't silently change what every SELECT * consumer receives).

Prefer CTEs Over Deeply Nested Subqueries

-- Hard to trace
SELECT * FROM (
  SELECT user_id, COUNT(*) AS order_count
  FROM (SELECT * FROM orders WHERE status = 'delivered') d
  GROUP BY user_id
) o WHERE order_count > 5;

-- Named, readable, independently testable — see SQL Subqueries & CTEs
WITH delivered_orders AS (SELECT user_id FROM orders WHERE status = 'delivered'),
     order_counts AS (SELECT user_id, COUNT(*) AS order_count FROM delivered_orders GROUP BY user_id)
SELECT * FROM order_counts WHERE order_count > 5;

Be Explicit About NULL Handling

-- Silent behavior a reader has to already know about
SELECT AVG(score) FROM results;   -- quietly ignores NULLs

-- Explicit intent, either direction
SELECT AVG(COALESCE(score, 0)) FROM results;              -- treat NULL as 0
SELECT AVG(score) FROM results WHERE score IS NOT NULL;   -- exclude NULLs, and say so

Never leave a reader to independently rediscover that an aggregate silently drops NULLs — either handle it explicitly or comment the intended behavior.

Prefer Explicit JOIN Syntax

-- Old implicit-join syntax — the WHERE clause silently acts as the join condition
SELECT * FROM users u, orders o WHERE u.id = o.user_id;

-- Explicit — the join condition is visually distinct from filtering
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

Window Functions Over Correlated Subqueries

-- Correlated subquery — conceptually re-evaluated per outer row (see SQL Subqueries & CTEs)
SELECT name, salary,
  (SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e1.dept) AS dept_avg
FROM employees e1;

-- Window function — one pass over the data
SELECT name, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

Pre-Submission Checklist

[ ] Uppercase keywords, lowercase identifiers
[ ] Meaningful table aliases (never t1/t2)
[ ] Explicit column list — no SELECT *
[ ] WHERE for row filters, HAVING for post-aggregation filters — not confused
[ ] No function wrapped around an indexed column in WHERE
[ ] UNION ALL instead of UNION unless dedup is actually required
[ ] NULL handling made explicit, not left implicit
[ ] A CTE for any subquery logic used more than once

Interview Drills

1. Why is SELECT * considered a code smell in production query code, beyond just "it's less readable"? — It's fragile to schema drift (a later ALTER TABLE ADD COLUMN silently changes what every SELECT * consumer receives) and transfers more data than the caller actually needs — the same reasoning as an API's response model deliberately narrowing what's returned, rather than passing through a full internal object.
2. Why does a correlated subquery for "average salary per department, shown alongside each employee" scale worse than the window-function equivalent? — The correlated subquery is conceptually re-evaluated once per outer row (once per employee), while the window function computes each partition's aggregate in a single pass over the data — on a large table this is a meaningful, not just stylistic, performance difference.

Cross-links

[[SQL Subqueries & CTEs]] · [[SQL Indexes & Query Performance]] · [[FastAPI]]