SQL

Constraints — let the database enforce it

CREATE TABLE orders (
  id           BIGSERIAL   PRIMARY KEY,
  customer_id  BIGINT      NOT NULL REFERENCES customers(id)
                             ON DELETE RESTRICT,
  order_no     TEXT        NOT NULL UNIQUE,
  status       TEXT        NOT NULL DEFAULT 'pending'
                             CHECK (status IN ('pending','paid','shipped','cancelled')),
  total_cents  BIGINT      NOT NULL CHECK (total_cents >= 0),
  currency     CHAR(3)     NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  shipped_at   TIMESTAMPTZ,
  CHECK (shipped_at IS NULL OR shipped_at >= created_at)
);

-- Every constraint here removes a class of bug permanently. An
-- application-level check protects one code path; a CHECK constraint
-- protects every path, including the migration script someone runs at
-- 2am and the manual UPDATE in a psql session.

-- Customers with no orders. Two idioms; prefer the first.
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;          -- the "left join and test for NULL" trick

-- Self join: every employee with their manager's name
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- LEFT, not INNER — otherwise the CEO (no manager) disappears.
-- Forgetting that is the classic mistake in this question.

-- BROKEN: rows with no 2026 order have status NULL, and
-- NULL = 'paid' is UNKNOWN, so those rows are filtered out.
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'            -- ← now effectively an INNER JOIN

-- CORRECT: filter the right table as part of the join condition
FROM customers c LEFT JOIN orders o
       ON o.customer_id = c.id AND o.status = 'paid'

── Written order ──────────  ── EXECUTION order ─────────────────
SELECT     …                 1. FROM / JOIN   build the row source
FROM       …                 2. WHERE          filter individual rows
WHERE      …                 3. GROUP BY       form groups
GROUP BY   …                 4. HAVING         filter GROUPS
HAVING     …                 5. SELECT         compute expressions, apply aliases
ORDER BY   …                 6. DISTINCT
LIMIT      …                 7. ORDER BY       sort the result
                             8. LIMIT / OFFSET

Consequences that answer real questions:
· WHERE cannot use a SELECT alias — WHERE runs before SELECT exists.
  (ORDER BY CAN, because it runs after. That asymmetry confuses people.)
· WHERE cannot use an aggregate — groups don't exist yet. Use HAVING.
· HAVING CAN use aggregates, because groups now exist.
· A non-aggregated column in SELECT must be in GROUP BY, because the
  database cannot know which of the group's values you meant.

-- Departments where the average salary exceeds 50,000,
-- counting only current employees, highest first.
SELECT   d.name,
         COUNT(*)          AS headcount,
         AVG(e.salary)     AS avg_salary
FROM     employees e
JOIN     departments d ON d.id = e.department_id
WHERE    e.active                      -- filters ROWS, before grouping
GROUP BY d.id, d.name                 -- group by the PK, select the name
HAVING   AVG(e.salary) > 50000       -- filters GROUPS, after
ORDER BY avg_salary DESC              -- alias works here, not in WHERE
LIMIT    10;

-- Conditional aggregation — a pattern worth having ready.
-- One pass, several answers, no self-joins:
SELECT customer_id,
       COUNT(*)                                        AS total,
       COUNT(*) FILTER (WHERE status = 'paid')        AS paid,
       SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded
FROM orders GROUP BY customer_id;
-- FILTER is the standard form (Postgres); the CASE trick is portable.

-- If the subquery returns ANY NULL, this returns NO ROWS. Ever.
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);   -- nullable column!

-- Because `id NOT IN (1, 2, NULL)` expands to
--   id != 1 AND id != 2 AND id != NULL
-- and `id != NULL` is UNKNOWN, so the whole AND can never be TRUE.

-- Use NOT EXISTS, which is NULL-safe and usually faster:
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

CTEs, including recursive

-- Readable multi-step query. Each CTE is a named intermediate result.
WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month,
         customer_id, SUM(total_cents) AS spend
  FROM orders WHERE status = 'paid'
  GROUP BY 1, 2
), ranked AS (
  SELECT *, RANK() OVER (PARTITION BY month ORDER BY spend DESC) AS rk
  FROM monthly
)
SELECT month, customer_id, spend FROM ranked WHERE rk <= 3;

-- RECURSIVE CTE: walk a hierarchy of unknown depth.
-- This is the standard answer to "find all reports under a manager".
WITH RECURSIVE subordinates AS (
  -- anchor: the starting row
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE id = 42
  UNION ALL
  -- recursive term: joins back to the CTE itself
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
  WHERE s.depth < 10          -- depth guard against cycles
)
SELECT * FROM subordinates;

-- Two things to say about recursive CTEs:
-- · UNION ALL not UNION — UNION deduplicates on every iteration, which
--   is slow and usually not what you want.
-- · Always bound the depth, or a cycle in the data loops forever.

function() OVER (
    PARTITION BY col     -- reset the window per group (optional)
    ORDER BY col         -- order within the window (required for ranking/offset)
    ROWS BETWEEN …       -- the frame (optional)
)

-- ── The single most-asked pattern: TOP N PER GROUP ──────────────
-- "The 3 highest-paid employees in each department"
WITH ranked AS (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (PARTITION BY department_id
                            ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;
-- You cannot filter on a window function in WHERE — windows are computed
-- after WHERE — so it must be wrapped in a CTE or subquery. That
-- restriction is itself a favourite follow-up question.

-- ── Nth highest value (the classic "second highest salary") ─────
SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
  FROM employees) t
WHERE rk = 2;
-- DENSE_RANK, not ROW_NUMBER: if two people share the top salary,
-- the "second highest salary" should be the next distinct value.

-- ── Running total and moving average ───────────────────────────
SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day)                     AS running_total,
       AVG(revenue) OVER (ORDER BY day
                            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_revenue;

-- ── Change from the previous row ───────────────────────────────
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev,
       revenue - LAG(revenue) OVER (ORDER BY month) AS delta
FROM monthly_revenue;

-- ── Detail alongside its own group aggregate ───────────────────
SELECT name, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY department_id) AS vs_avg
FROM employees;
-- Impossible with GROUP BY without a self-join. This is the whole point.

-- ── Deduplication: keep the newest row per key ─────────────────
DELETE FROM events WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY event_key
                                ORDER BY created_at DESC) AS rn
    FROM events) t
  WHERE rn > 1);

Views are also the practical vehicle for logical data independence: split a table in two and redefine the view, and queries against the view keep working. Note a view is only updatable if the database can unambiguously map a row back to one base row — so simple single-table views usually are, and anything with joins, aggregates or DISTINCT usually isn't.

The balanced position: use procedures for data-intensive operations where moving the logic to the data avoids many round trips, and keep business rules in the application. Note the distinction that gets asked: a function returns a value and can be used in a query; a procedure is invoked for its effects and can manage transactions.

Three-valued logic

SQL has TRUE, FALSE and UNKNOWN. Any comparison with NULL is UNKNOWN,
and WHERE only keeps rows where the condition is TRUE.

NULL = NULL          → UNKNOWN   (not TRUE!)
NULL != NULL         → UNKNOWN
NULL = 5             → UNKNOWN
NULL IS NULL        → TRUE      ← the only way to test for NULL
NULL IS NOT DISTINCT FROM NULL → TRUE   (NULL-safe equality)

TRUE  AND UNKNOWN  → UNKNOWN
FALSE AND UNKNOWN  → FALSE     ← short-circuits
TRUE  OR  UNKNOWN  → TRUE
NOT UNKNOWN       → UNKNOWN

NULL + 5             → NULL      -- arithmetic propagates
'abc' || NULL       → NULL      -- so does concatenation
COALESCE(a, b, 0)   → first non-NULL  ← the fix
NULLIF(a, 0)        → NULL if a = 0    -- guards division by zero

Other traps

-- VULNERABLE: the input becomes part of the query TEXT
query = "SELECT * FROM users WHERE email = '" + input + "'"
-- input: ' OR '1'='1  →  returns every user
-- input: '; DROP TABLE users; --  →  exactly what you fear

-- SAFE: parameterised. The driver sends the query and the values
-- SEPARATELY, so the value can never be parsed as SQL.
cursor.execute("SELECT * FROM users WHERE email = %s", (input,))