SQL cheat sheet

SELECT col1, col2, agg(col3) AS a
FROM   t
WHERE  predicate            -- rows, pre-group
GROUP BY col1, col2
HAVING agg(col3) > x        -- groups, post-group
ORDER BY a DESC
LIMIT  50 OFFSET 100;

Written in this order; the engine runs FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

x = 1        x <> 1 / x != 1
x IN (1,2,3)         x BETWEEN 1 AND 9
x LIKE 'a%'   x ILIKE 'a%'   -- ILIKE: Postgres
x IS NULL     x IS NOT NULL
a AND b       a OR b       NOT a

BETWEEN is inclusive on both ends. For timestamps prefer x >= lo AND x < hi.

SELECT DISTINCT country FROM customers;
SELECT DISTINCT country, region FROM t;  -- whole row
SELECT COUNT(DISTINCT customer_id) FROM orders;

DISTINCT applies to the entire SELECT list, not one column.

LIMIT 20 OFFSET 40           -- Postgres, MySQL, SQLite
FETCH FIRST 20 ROWS ONLY     -- standard, SQL Server, Oracle
TOP 20                       -- SQL Server (after SELECT)

Deep OFFSET is slow — page by a WHERE key > last_seen instead.

FROM a JOIN b        ON b.a_id = a.id   -- inner: matches only
FROM a LEFT JOIN b   ON b.a_id = a.id   -- all of a
FROM a FULL JOIN b   ON b.a_id = a.id   -- all of both
-- anti-join: rows of a with no b
FROM a LEFT JOIN b ON b.a_id = a.id
WHERE b.id IS NULL;

A WHERE on the right table of a LEFT JOIN silently turns it into an inner join — put that condition in ON.

WHERE id IN     (SELECT a_id FROM b)
WHERE EXISTS    (SELECT 1 FROM b WHERE b.a_id = a.id)
WHERE NOT EXISTS(SELECT 1 FROM b WHERE b.a_id = a.id)

Never NOT IN a subquery whose column is nullable — one NULL makes it return nothing. Use NOT EXISTS.

q1 UNION ALL q2     -- concat, keep dups (fast)
q1 UNION q2        -- concat, dedupe (sorts)
q1 INTERSECT q2    -- rows in both
q1 EXCEPT q2       -- in q1, not q2  (MINUS in Oracle)

Column count and types must line up. UNION ALL unless you actually need the dedupe.

WITH recent AS (
  SELECT * FROM orders WHERE order_date >= '2026-01-01'
)
SELECT customer_id, COUNT(*) FROM recent GROUP BY customer_id;

WITH RECURSIVE nums(n) AS (
  SELECT 1  UNION ALL  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

Names a subquery so the query reads top-down. RECURSIVE walks hierarchies and series.

SELECT customer_id, COUNT(*) AS n, SUM(amount) AS total
FROM   orders
WHERE  status = 'paid'      -- filter rows first
GROUP BY customer_id
HAVING SUM(amount) > 500;   -- then filter groups

Every non-aggregated SELECT column must be in GROUP BY. WHERE filters rows, HAVING filters groups.

COUNT(*)            -- all rows
COUNT(col)          -- non-NULL values of col
COUNT(DISTINCT col)
SUM(col)  AVG(col)  MIN(col)  MAX(col)   -- all skip NULL

AVG ignores NULLs (not zero). Integer / integer floors — cast one side to get decimals.

CASE WHEN amount > 100 THEN 'big'
     WHEN amount > 0   THEN 'small'
     ELSE 'zero' END

CASE status WHEN 'paid' THEN 1 ELSE 0 END   -- simple form

SUM(CASE WHEN status='paid' THEN amount ELSE 0 END)  -- conditional agg

First matching WHEN wins. No ELSE means NULL for the misses.

SELECT
  customer_id,
  SUM(CASE WHEN status='paid'     THEN amount END) AS paid,
  SUM(CASE WHEN status='refunded' THEN amount END) AS refunded
FROM orders GROUP BY customer_id;

Conditional aggregation runs the same on every engine — no dialect PIVOT needed.

func(...) OVER (
  PARTITION BY grp          -- separate window per group
  ORDER BY sort_key         -- order within the window
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW   -- frame
)

Rows are kept, not collapsed. PARTITION BY is GROUP BY without the collapse.

ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC)  -- 1,2,3,4  always unique
RANK()       OVER (...)   -- 1,1,3    ties share, then skip
DENSE_RANK() OVER (...)   -- 1,1,2    ties share, no gap

ROW_NUMBER for exactly one row per group (top-N-per-group). RANK for leaderboards.

SUM(x)  OVER (ORDER BY d)                     -- cumulative
SUM(x)  OVER (PARTITION BY g)                 -- group total on each row
LAG(x, 1, 0)  OVER (ORDER BY d)               -- previous row (default 0)
LEAD(x) OVER (ORDER BY d)                     -- next row
NTILE(4) OVER (ORDER BY x)                    -- quartiles by row count

LAG/LEAD replace a self-join for period-over-period math.

COALESCE(a, b, c)      -- first non-NULL
NULLIF(a, b)           -- NULL if a = b, else a
a IS NOT DISTINCT FROM b   -- NULL-safe equality (Postgres)
a <=> b                    -- NULL-safe equality (MySQL)

NULL = NULL is unknown, not true. Any arithmetic or comparison with NULL is NULL.

CAST(x AS INTEGER)     CAST(x AS TEXT)     CAST(x AS DECIMAL(10,2))
x::integer            -- Postgres shorthand

'10' + 5     -- 15 (MySQL/SQLite), error (Postgres), 15 (SQL Server)
'10' > 9     -- depends: string vs numeric compare

Implicit conversion rules differ per engine. Cast explicitly when types are mixed.

a || b            CONCAT(a, b, c)     -- || propagates NULL; CONCAT treats it as ''
SUBSTR(s, 2, 3)   -- 1-indexed
TRIM(s)  LTRIM(s)  RTRIM(s)   REPLACE(s, from, to)
INSTR(s, sub)     -- 1-indexed, 0 if not found  (POSITION / CHARINDEX elsewhere)
LENGTH(s)         -- LEN() on SQL Server (ignores trailing spaces)

Positions are 1-indexed everywhere. LEFT/RIGHT/LPAD/RPAD are missing from some engines.

CURRENT_DATE   CURRENT_TIMESTAMP
DATE_TRUNC('month', ts)          -- Postgres;  strftime('%Y-%m', ts) SQLite
EXTRACT(YEAR FROM ts)            -- standard;  strftime('%Y', ts)    SQLite
ts + INTERVAL '7 days'           -- Postgres
DATEADD(day, 7, ts)  DATEDIFF(day, a, b)   -- SQL Server

The least portable area of SQL — concepts transfer, function names do not.

INSERT INTO t (a, b) VALUES (1, 2);
INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);   -- multi-row
INSERT INTO t (a, b) SELECT a, b FROM staging;        -- from a query
INSERT INTO t (a, b) VALUES (1, 2) RETURNING id;      -- Postgres, SQLite 3.35+

List columns explicitly. Omitted columns get their DEFAULT or NULL.

UPDATE t SET status = 'flagged' WHERE amount > 100;
DELETE FROM t WHERE status = 'refunded';

-- no WHERE hits every row. Run the SELECT first.
SELECT COUNT(*) FROM t WHERE amount > 100;

Wrap risky changes in a transaction, verify, then COMMIT or ROLLBACK.

BEGIN;
  UPDATE accounts SET bal = bal - 100 WHERE id = 1;
  UPDATE accounts SET bal = bal + 100 WHERE id = 2;
COMMIT;              -- or ROLLBACK;

SAVEPOINT sp1;  ...  ROLLBACK TO sp1;   -- partial undo

Autocommit is the default — BEGIN is what makes several statements one unit. A failed statement usually needs an explicit ROLLBACK.

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_cust_date  ON orders (customer_id, order_date);
CREATE UNIQUE INDEX ...            -- also a constraint

A composite index only helps filters that start from its leftmost column. Function-wrapping a column (LOWER(col)) defeats a plain index.

EXPLAIN QUERY PLAN SELECT ...     -- SQLite
EXPLAIN [ANALYZE] SELECT ...      -- Postgres / MySQL 8+
SET STATISTICS PROFILE ON        -- SQL Server

SCAN / Seq Scan / type=ALL = every row checked. SEARCH / Index Seek = using an index. EXPLAIN ANALYZE actually runs the query.