← SQL Foundations for Data Engineering

SQL0 Cheat sheet — foundations for data engineering

Semantics verified against the PostgreSQL documentation; Redshift differences against the AWS Redshift Developer Guide. Verified 2026-08-12.

NULL — three-valued logic

NULL is the absence of knowledge, not a value. WHERE keeps rows where the condition is true — unknown rows are dropped silently.

🚨 NOT IN with a NULL returns zero rows

x NOT IN (a, b, NULL) → x<>a AND x<>b AND x<>NULL → true AND unknown → never true. IN is fine (true OR unknown = true). NOT IN is catastrophic.

-- Always prefer this:
WHERE NOT EXISTS (SELECT 1 FROM blocked b WHERE b.id = o.id)

IS DISTINCT FROM — NULL-safe comparison

Use for change detection (SCD/CDC). <> misses NULL→value transitions silently.

Joins — cardinality arithmetic

The three checks — before you trust a join

-- 1. Is the key unique on the "one" side?  (must return 0 rows)
SELECT k, COUNT(*) FROM dim GROUP BY k HAVING COUNT(*) > 1;
-- 2. Did the row count change?  (many:1 → must be equal)
SELECT (SELECT COUNT(*) FROM fact) , (SELECT COUNT(*) FROM fact JOIN dim USING (k));
-- 3. Does the aggregate survive?
SELECT SUM(amount) FROM fact;   -- vs the joined version

Chasm trap

Two independent 1:many branches on the same parent → cross product. 3 shipments × 2 returns = 6 rows, both SUMs wrong by different factors. Fix: aggregate each branch in its own subquery, then join.

LEFT JOIN rules

• ON filters what gets joined. WHERE filters what survives. A predicate on the right table in WHERE silently makes it an INNER JOIN (unmatched → NULL → unknown → dropped).
• After a LEFT JOIN, COUNT(right.col), never COUNT(*) (which returns 1 for unmatched).

🚨 SELECT DISTINCT does not fix fan-out. Find the duplicate.

Counting

Over zero rows: COUNT → 0. Everything else (SUM, AVG, MAX, MIN) → NULL. ⇒ COALESCE(SUM(x),0) is right; COALESCE(COUNT(x),0) is pointless.

-- Conditional counts, one pass. No ELSE needed: non-matches become NULL, COUNT skips them.
COUNT(CASE WHEN status='paid' THEN 1 END)                    AS paid,
COUNT(DISTINCT CASE WHEN status='paid' THEN customer_id END) AS paying_customers

Order of operations: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT WHERE = rows (no aggregates). HAVING = groups. Row predicates in HAVING are a smell.

Dedup and top-N

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY updated_at DESC, event_id DESC   -- tie-breaker is NOT optional
  ) rn
  FROM events
) t WHERE rn = 1;          -- top-N: rn <= 3

• Can't filter a window function in WHERE — it's evaluated after. Wrap in a subquery/CTE.
• No tie-breaker ⇒ non-deterministic winner. Different output from identical input, doesn't reproduce.

🚨 Dedup with RANK() leaves duplicates when there's a tie.

Assert the dedup

-- unique by key (0 rows) AND no keys lost (counts equal)
SELECT k, COUNT(*) FROM deduped GROUP BY k HAVING COUNT(*) > 1;
SELECT COUNT(DISTINCT k) FROM src;   SELECT COUNT(*) FROM deduped;

Redshift ≠ PostgreSQL

"Do not assume that the semantics of elements that Amazon Redshift and PostgreSQL have in common are identical."

🚨 Constraints are informational only

"Unique, primary key, and foreign key constraints are permitted, but they are informational only. They are not enforced by the system, but they are used by the query planner."

⇒ A false PK declaration can produce wrong results, not just slow ones. Declare them (planner

• documentation), but enforce uniqueness yourself and assert after every load.

Not supported

Table partitioning · Indexes · Tablespaces · Constraint enforcement · Inheritance · Triggers · Sequences · Collations · Full text search · Table functions · SQL/MED · psql (use RSQL) · Array/row constructors · NULLS clause in window functions · VALUES as constant tables

Instead of indexes: sort keys · zone maps · distribution style.

⚠️ Python UDFs — end of support was 2026-06-30 (passed)

Enforcement in phases. Inventory now; see the AWS migration blog post.