Appendix C — SQL Quick Reference

A keep-it-next-to-your-keyboard reference for PostgreSQL SQL. Organized by task. (Examples use Mercado.)

SELECT — the anatomy & logical order

SELECT   columns / expressions [AS alias]      -- π  (projection)        -- 5th
FROM     table [alias] [JOIN ...]              --     source             -- 1st
WHERE    row_condition                          -- σ  (selection)         -- 2nd
GROUP BY columns                                --     grouping           -- 3rd
HAVING   group_condition                        --     filter groups      -- 4th
ORDER BY columns [ASC|DESC] [NULLS FIRST|LAST]  --     sort               -- 6th
LIMIT n [OFFSET m];                             --     slice              -- 7th

Logical evaluation: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. (So WHERE can't see SELECT aliases; ORDER BY can.)

WHERE operators

Joins

FROM a JOIN b  ON b.a_id = a.id          -- INNER: matches in both
FROM a LEFT JOIN b ON ...                 -- all of a, NULLs where no b
FROM a LEFT JOIN b ON ... WHERE b.id IS NULL   -- ANTI-join ("a with no b")
FROM a RIGHT JOIN b / FULL OUTER JOIN b   -- mirror / both sides
FROM a CROSS JOIN b                        -- every pairing (Cartesian)
FROM emp e JOIN emp m ON m.id = e.mgr_id   -- self-join (two aliases)

Aggregation

COUNT(*) | COUNT(col) | COUNT(DISTINCT col) | SUM | AVG | MIN | MAX
SELECT g, COUNT(*) FROM t GROUP BY g HAVING COUNT(*) > 1;
COUNT(*) FILTER (WHERE cond)              -- conditional aggregate
GROUP BY ROLLUP (a) | CUBE (a,b) | GROUPING SETS (...)   -- subtotals

Aggregates skip NULLs; COUNT(*) counts rows. Across a one-to-many join use COUNT(DISTINCT key).

Subqueries

WHERE x > (SELECT AVG(x) FROM t)          -- scalar
WHERE id IN (SELECT id FROM ...)          -- list
FROM (SELECT ... ) sub                     -- derived table (alias required)
WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)        -- existence
WHERE NOT EXISTS (...)                      -- NULL-safe anti-join (prefer over NOT IN)

Set operations

q1 UNION q2        -- in either (dedup)        q1 UNION ALL q2   -- keep dups (faster)
q1 INTERSECT q2    -- in both                   q1 EXCEPT q2      -- in q1 not q2 (difference)

Union-compatible (same column count/types); one ORDER BY at the end.

CTEs & recursion

WITH step1 AS (...), step2 AS (... step1 ...) SELECT ... FROM step2;
WITH RECURSIVE t AS (
    <anchor>            UNION ALL    <recursive term referencing t>
) SELECT ... FROM t;     -- guard cycles: CYCLE col SET is_cycle USING path

Window functions

fn() OVER (PARTITION BY g ORDER BY o [frame])
ROW_NUMBER() | RANK() | DENSE_RANK() | NTILE(n)          -- ranking
SUM(x) OVER (ORDER BY t)                                  -- running total
LAG(x) / LEAD(x) OVER (ORDER BY t)                        -- adjacent rows
AVG(x) OVER (ORDER BY t ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)   -- moving avg

Top-N-per-group: ROW_NUMBER() in a subquery, then WHERE rn <= N (can't filter windows in WHERE).

Data modification (DML)

INSERT INTO t (a,b) VALUES (1,2),(3,4) RETURNING id;     -- name columns!
INSERT INTO t (...) SELECT ...;                            -- from a query
UPDATE t SET a = a+1 WHERE ...;                            -- never without WHERE
DELETE FROM t WHERE ...;          TRUNCATE t [RESTART IDENTITY];
INSERT INTO t (...) VALUES (...) ON CONFLICT (key) DO UPDATE SET ...;   -- upsert
-- safe habit: SELECT the WHERE first → BEGIN → change → verify → COMMIT/ROLLBACK

Data definition (DDL)

CREATE TABLE t (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  qty   integer NOT NULL CHECK (qty > 0) DEFAULT 0,
  fk_id integer REFERENCES other(id) ON DELETE CASCADE,
  PRIMARY KEY (a, b)             -- composite (table constraint)
);
ALTER TABLE t ADD COLUMN c text;  ALTER TABLE t ALTER COLUMN c SET NOT NULL;
DROP TABLE [IF EXISTS] t [CASCADE];
CREATE [UNIQUE] INDEX [CONCURRENTLY] idx ON t (col);
CREATE VIEW v AS SELECT ...;   CREATE MATERIALIZED VIEW mv AS ...;  REFRESH MATERIALIZED VIEW [CONCURRENTLY] mv;

Common functions (Appendix-level; see Ch. 8)

-- strings: || concat (NULL-propagating), concat(), length, upper/lower/initcap, trim,
--          substring(s from a for b), position, replace, split_part, lpad/rpad
-- numbers: round, ceil, floor, trunc, abs, mod (mind integer division: 5/2=2, use 5.0/2)
-- dates:   now(), current_date, extract(field FROM ts), date_trunc('month', ts),
--          age(a,b), ts + interval '1 day', to_char(ts,'YYYY-MM-DD')
--          (use half-open ranges: >= start AND < next_start)
-- cast:    CAST(x AS type) or x::type
-- cond:    CASE WHEN ... THEN ... ELSE ... END, COALESCE(a,b), NULLIF(x,0), GREATEST/LEAST

JSONB (Ch. 16)

data -> 'k'    -- as jsonb      data ->> 'k'   -- as text
data @> '{"k":1}'   -- contains (GIN-indexable)    data ? 'k'   -- key exists
CREATE INDEX ON t USING GIN (data);

Transactions & locking

BEGIN [ISOLATION LEVEL READ COMMITTED | REPEATABLE READ | SERIALIZABLE];
... ; COMMIT | ROLLBACK;   SAVEPOINT s; ROLLBACK TO SAVEPOINT s;
SELECT ... FOR UPDATE;     -- pessimistic row lock

Diagnostics

EXPLAIN [ANALYZE] [BUFFERS] <query>;   ANALYZE [table];   VACUUM [table];
\d t   \di   \timing   \conninfo
SELECT * FROM pg_stat_activity;        -- running queries

See also: Appendix D (data types), Appendix E (relational algebra), Appendix G (EXPLAIN/tuning), Appendix I (cookbook), Appendix J (dialect differences).