On this page

SQL Cheat Sheet

Quick reference for SQL query patterns, joins, window functions, CTEs, indexing strategies, and reading EXPLAIN output. Examples use PostgreSQL syntax (PostgreSQL 18 is the current major as of November 2026; 19 is in release candidate; 14 reaches end-of-life on November 12, 2026).

Deep dive: Database Section | Query Optimization


Tables & Constraints

CREATE TABLE users (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- prefer over SERIAL
  public_id   uuid NOT NULL DEFAULT uuidv7(),                   -- PG 18+: time-ordered UUID
  email       text NOT NULL UNIQUE,
  name        text NOT NULL,
  status      text NOT NULL DEFAULT 'active'
              CHECK (status IN ('active', 'inactive', 'deleted')),
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz,
  email_lower text GENERATED ALWAYS AS (lower(email))           -- PG 18: virtual by default; add STORED to persist
);

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  total      numeric(12, 2) NOT NULL CHECK (total >= 0),
  status     text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);           -- FKs are NOT indexed automatically

ALTER TABLE users ADD COLUMN phone text;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE orders ADD CONSTRAINT orders_total_ck CHECK (total < 1000000) NOT VALID;  -- no full scan now
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_ck;                              -- scan later, lighter lock
TypeUse forAvoid
bigint GENERATED ... AS IDENTITYSurrogate keysserial (legacy), int keys that can overflow
uuid + uuidv7()Public / distributed IDs (index-friendly ordering)uuidv4()/gen_random_uuid() as a clustered PK on huge tables
textStringsvarchar(n) unless the limit is a real rule
timestamptzPoints in timetimestamp (no time zone) for events
numeric(p, s)Moneyfloat / real / money
jsonbSemi-structured datajson (unless you need exact text/key order)

Basic Queries

-- Select with filtering
SELECT id, name, email
FROM users
WHERE status = 'active'
  AND created_at > '2025-01-01'
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

-- Insert
INSERT INTO users (name, email, status)
VALUES ('Alice', 'alice@example.com', 'active')
RETURNING id;

-- Insert multiple rows
INSERT INTO users (name, email) VALUES
  ('Alice', 'alice@ex.com'),
  ('Bob', 'bob@ex.com'),
  ('Charlie', 'charlie@ex.com');

-- Upsert (INSERT ... ON CONFLICT)
INSERT INTO users (email, name)
VALUES ('alice@ex.com', 'Alice Updated')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();

-- Update
UPDATE users
SET status = 'inactive', updated_at = NOW()
WHERE last_login < NOW() - INTERVAL '90 days';

-- Delete
DELETE FROM users WHERE status = 'deleted';

-- Truncate (fast delete all rows)
TRUNCATE TABLE logs;

-- Insert, ignore duplicates
INSERT INTO users (email, name) VALUES ('alice@ex.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

-- Insert from a query
INSERT INTO archived_orders SELECT * FROM orders WHERE created_at < '2024-01-01';

-- Update from another table
UPDATE orders o
SET status = 'vip'
FROM users u
WHERE u.id = o.user_id AND u.tier = 'gold';

-- Return old and new values (PG 18+)
UPDATE accounts SET balance = balance + 10 WHERE id = 1
RETURNING old.balance AS before, new.balance AS after;

-- MERGE (PG 15+; RETURNING since PG 17)
MERGE INTO inventory i
USING incoming s ON s.sku = i.sku
WHEN MATCHED AND s.qty = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET qty = i.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty)
RETURNING merge_action(), i.*;

-- Standard row limiting (same as LIMIT; WITH TIES keeps equal last values)
SELECT * FROM scores ORDER BY points DESC FETCH FIRST 3 ROWS WITH TIES;

Conditionals, NULLs & Common Functions

SELECT
  CASE WHEN total >= 100 THEN 'large'
       WHEN total >= 10  THEN 'medium'
       ELSE 'small' END                     AS bucket,
  COALESCE(nickname, name, 'anonymous')     AS display_name,   -- first non-NULL
  NULLIF(discount, 0)                       AS discount_or_null, -- avoid divide-by-zero
  total / NULLIF(qty, 0)                    AS unit_price,
  a IS DISTINCT FROM b                      AS changed,         -- NULL-safe <>
  GREATEST(a, b), LEAST(a, b)
FROM t;

-- Strings
lower(s), upper(s), length(s), trim(s), substring(s FROM 1 FOR 3), left(s, 3),
replace(s, 'a', 'b'), split_part('a,b,c', ',', 2) /* 'b' */, s || ' suffix', concat_ws('-', a, b),
s ILIKE 'al%' /* case-insensitive LIKE */, s ~ '^[A-Z]' /* regex */, string_agg(name, ', ' ORDER BY name)

-- Dates & times
now(), current_date, date_trunc('month', ts), extract(year FROM ts), ts + interval '7 days',
age(now(), birth_date), to_char(ts, 'YYYY-MM-DD'), ts AT TIME ZONE 'UTC', ts::date

-- Casting
'42'::int, CAST('2026-11-09' AS date), price::text

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
SAVEPOINT before_fee;
UPDATE accounts SET balance = balance - 1 WHERE id = 1;
ROLLBACK TO SAVEPOINT before_fee;   -- undo only the fee
COMMIT;                             -- or ROLLBACK;

-- Lock rows you are about to change; SKIP LOCKED makes a job queue
SELECT id FROM jobs WHERE status = 'queued'
ORDER BY id LIMIT 10
FOR UPDATE SKIP LOCKED;

Joins

Join Types Visual Reference

INNER JOIN       LEFT JOIN        RIGHT JOIN       FULL OUTER JOIN
 A [AB] B        [A AB] B         A [AB B]         [A AB B]
   ██               ███              ███             █████

Join Syntax

-- INNER JOIN: rows that match in both tables
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- LEFT JOIN: all rows from left, matched from right
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Multiple joins
SELECT u.name, o.id, p.name AS product
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

-- Self join (hierarchical data)
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- Cross join (cartesian product)
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

-- Lateral join (per-row subquery)
SELECT u.name, latest.total
FROM users u
CROSS JOIN LATERAL (
  SELECT total
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) latest;

Anti-Join (rows NOT in other table)

-- Method 1: LEFT JOIN + IS NULL
-- (PostgreSQL plans this and NOT EXISTS as the same Hash/Merge Anti Join)
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Method 2: NOT EXISTS (recommended: clearest intent, NULL-safe, never duplicates rows)
SELECT u.*
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Method 3: NOT IN (avoid if subquery can return NULL)
SELECT u.*
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

Aggregations

-- Basic aggregates
SELECT
  status,
  COUNT(*) AS total,
  COUNT(DISTINCT email) AS unique_emails,
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount,
  MIN(amount) AS min_amount,
  MAX(amount) AS max_amount
FROM orders
GROUP BY status
HAVING COUNT(*) > 10
ORDER BY total DESC;

-- Grouping sets (multiple groupings in one query)
SELECT
  COALESCE(country, 'ALL') AS country,
  COALESCE(city, 'ALL') AS city,
  COUNT(*)
FROM users
GROUP BY GROUPING SETS (
  (country, city),
  (country),
  ()
);

-- Filter aggregate (PostgreSQL)
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  COUNT(*) FILTER (WHERE status = 'inactive') AS inactive
FROM users;

Window Functions

Window functions perform calculations across a set of rows related to the current row, without collapsing rows.

Syntax

function_name() OVER (
  [PARTITION BY column]
  [ORDER BY column]
  [ROWS/RANGE frame_spec]
)

Common Window Functions

SELECT
  name,
  department,
  salary,

  -- Ranking
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile,

  -- Ranking within partition
  ROW_NUMBER() OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS dept_rank,

  -- Aggregates as windows
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  COUNT(*) OVER (PARTITION BY department) AS dept_count,

  -- Running total
  SUM(salary) OVER (ORDER BY hire_date) AS running_total,

  -- Moving average (last 3 rows)
  AVG(salary) OVER (
    ORDER BY hire_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg,

  -- Access other rows
  LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary,
  LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS highest_paid,
  LAST_VALUE(name) OVER (
    PARTITION BY department ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_paid

FROM employees;

Ranking Functions Compared

FunctionTiesGapsExample (values: 100, 100, 90)
ROW_NUMBER()NoNo1, 2, 3
RANK()YesYes1, 1, 3
DENSE_RANK()YesNo1, 1, 2

Common Table Expressions (CTEs)

-- Basic CTE
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE status = 'active'
)
SELECT au.name, COUNT(o.id) AS order_count
FROM active_users au
LEFT JOIN orders o ON o.user_id = au.id
GROUP BY au.name;

-- Multiple CTEs
WITH
monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
  FROM orders
  GROUP BY 1
),
monthly_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND(
      (revenue - LAG(revenue) OVER (ORDER BY month))
      / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2   -- NULLIF avoids division by zero
    ) AS growth_pct
  FROM monthly_revenue
)
SELECT * FROM monthly_growth ORDER BY month;

-- Recursive CTE (hierarchical data)
WITH RECURSIVE org_tree AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  INNER JOIN org_tree ot ON ot.id = e.manager_id
)
SELECT * FROM org_tree ORDER BY depth, name;

Indexing Rules

When to Create Indexes

ScenarioIndex Type
WHERE clause equalityB-tree (default)
WHERE clause range (<, >, BETWEEN)B-tree
Full-text searchGIN
JSONB field queriesGIN
Geospatial queriesGiST
Array containmentGIN
Pattern matching (LIKE 'prefix%')B-tree (with text_pattern_ops)

Index Creation

-- Single column
CREATE INDEX idx_users_email ON users (email);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- Partial index (smaller, faster)
CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- Covering index (INCLUDE)
CREATE INDEX idx_orders_user ON orders (user_id) INCLUDE (total, status);

-- GIN index for JSONB
CREATE INDEX idx_data ON events USING GIN (payload);

-- Concurrent index creation (no table lock)
CREATE INDEX CONCURRENTLY idx_name ON table (column);

Composite Index Column Order

The leftmost prefix rule: a composite index on (A, B, C) can serve queries on:

  • A alone
  • A, B together
  • A, B, C together
  • But NOT B alone or C alone

PostgreSQL 18 skip scan

PG 18 can skip-scan a B-tree when the leading column has few distinct values, so (A, B) may now serve WHERE B = ?. It's a fallback, not a design strategy — still put the most-filtered equality columns first.

Rule of thumb for column order: equality columns first, then the range or ORDER BY column (WHERE user_id = ? AND created_at > ? ORDER BY created_at → (user_id, created_at)).

When NOT to Index

  • Small tables (< 1000 rows) -- sequential scan is faster
  • Columns with very low cardinality (e.g., boolean with 50/50 split)
  • Heavily written tables where index maintenance cost > query benefit
  • Columns rarely used in WHERE, JOIN, or ORDER BY

EXPLAIN Reading Guide

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)   -- BUFFERS is included by default with ANALYZE since PG 18
SELECT * FROM users WHERE email = 'alice@ex.com';

EXPLAIN ANALYZE executes the statement — wrap INSERT/UPDATE/DELETE in BEGIN; ... ROLLBACK;.

Key Metrics

MetricMeaning
cost=0.00..8.17Startup cost..total cost (arbitrary units)
rows=1Estimated rows returned
actual time=0.01..0.02Actual time in ms (with ANALYZE)
loops=1Times this node executed
Buffers: shared hit=3Pages read from cache
Buffers: shared read=1Pages read from disk

Scan Types (Best to Worst)

ScanMeaningPerformance
Index Only ScanAll data from indexBest
Index ScanIndex lookup + heap fetchGood
Bitmap Index ScanIndex bitmap + heap scanGood for many rows
Seq ScanFull table scanBad for large tables

Join Types

JoinMeaning
Nested LoopFor each row in outer, scan inner. Good for small sets
Hash JoinBuild hash table from smaller set. Good for equality joins
Merge JoinSort both, then merge. Good for large sorted sets

Red Flags in EXPLAIN

Red FlagMeaning
Seq Scan on large tableMissing index
High rows estimate vs actualStale statistics, run ANALYZE
Nested Loop with large innerMay need hash or merge join
Sort with large sort_method: external mergeNeed more work_mem
Many Buffers: shared readCold cache or table too large for memory

Useful Patterns

Pagination

-- Offset pagination (simple but slow for large offsets)
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 100;

-- Keyset pagination (fast for any page)
SELECT * FROM posts
WHERE id > 12345  -- last ID from previous page
ORDER BY id
LIMIT 20;

Deduplication

-- Keep latest per group
DELETE FROM events a
USING (
  SELECT DISTINCT ON (user_id)
    id, user_id
  FROM events
  ORDER BY user_id, created_at DESC
) b
WHERE a.user_id = b.user_id AND a.id != b.id;

Date Ranges

-- Generate date series
SELECT d::date
FROM generate_series('2025-01-01', '2025-12-31', '1 day'::interval) d;

-- Fill in missing dates
SELECT d.date::date, COALESCE(o.count, 0) AS orders
FROM generate_series('2025-01-01', '2025-01-31', '1 day'::interval) d(date)
LEFT JOIN (
  SELECT DATE(created_at) AS date, COUNT(*) AS count
  FROM orders
  GROUP BY 1
) o ON o.date = d.date;

JSON Operations (PostgreSQL)

-- Extract value
SELECT payload->>'name' FROM events;           -- text
SELECT payload->'address'->>'city' FROM events; -- nested text
SELECT payload#>>'{address,city}' FROM events;  -- path extraction

-- Query JSONB
SELECT * FROM events WHERE payload @> '{"type": "click"}';
SELECT * FROM events WHERE payload ? 'name';           -- key exists
SELECT * FROM events WHERE payload ?| ARRAY['a', 'b']; -- any key exists

-- Aggregate to JSON
SELECT json_agg(row_to_json(u)) FROM users u;

Troubleshooting

ProblemSolution
Query is slowRun EXPLAIN ANALYZE, check for Seq Scan
Table bloatTune autovacuum and run VACUUM (VERBOSE) table; VACUUM FULL reclaims disk but takes an exclusive lock for the whole rewrite — use pg_repack on live tables
Stale statisticsRun ANALYZE table
Lock contentionCheck pg_stat_activity for blocked queries
Connection exhaustionUse connection pooling (PgBouncer)
DeadlockCheck pg_locks, ensure consistent lock ordering
Index not being usedCheck if query pattern matches index columns
-- Check running queries
SELECT pid, query, state, wait_event, query_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Kill a query
SELECT pg_cancel_backend(pid);    -- graceful
SELECT pg_terminate_backend(pid); -- force

-- Check table sizes
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Check index usage
SELECT
  indexrelname,
  idx_scan AS scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan;

Test Yourself
  1. What SQL clause inserts a row or updates it if a unique constraint is violated (PostgreSQL)? INSERT ... ON CONFLICT ... DO UPDATE

  2. What is the difference between RANK() and DENSE_RANK() for tied values? RANK() leaves gaps after ties (1,1,3), DENSE_RANK() does not (1,1,2).

  3. How do you get the previous row's value in a window function? LAG(column, 1) OVER (ORDER BY ...)

  4. What keyword makes a CTE reference itself for hierarchical queries? RECURSIVE

  5. Which anti-join pattern should you use: LEFT JOIN + IS NULL, NOT EXISTS, or NOT IN? NOT EXISTS (PostgreSQL gives it the same anti-join plan as LEFT JOIN + IS NULL); avoid NOT IN because a single NULL in the subquery returns no rows.

  6. What command creates an index without locking the table in PostgreSQL? CREATE INDEX CONCURRENTLY

  7. What does a Seq Scan on a large table indicate in EXPLAIN output? A missing index -- the database is scanning every row.

  8. How do you perform keyset (cursor) pagination instead of OFFSET? WHERE id > last_seen_id ORDER BY id LIMIT 20

  9. What function generates a series of dates in PostgreSQL? generate_series('start'::date, 'end'::date, '1 day'::interval)

  10. What is the leftmost prefix rule for composite indexes? An index on (A, B, C) can serve queries on A, A+B, or A+B+C, but NOT B alone or C alone.

Common Gotchas

  • NOT IN returns zero rows if the subquery contains a NULL. Use NOT EXISTS instead -- it handles NULLs correctly.
  • Applying functions on indexed columns kills index usage. WHERE DATE(created_at) = '2025-01-01' cannot use an index on created_at. Rewrite as a range query.
  • OFFSET pagination gets slower on every page. The database still scans and discards all skipped rows. Use keyset/cursor pagination for large datasets.
  • Forgetting ANALYZE after bulk data loads. Stale statistics cause the query planner to pick bad plans. Run ANALYZE table_name after large inserts.
  • = NULL is never true. Use IS NULL / IS DISTINCT FROM. COUNT(col) skips NULLs; COUNT(*) doesn't.
  • Integer division truncates. 1 / 2 is 0; write 1.0 / 2 or cast to numeric.
  • BETWEEN on timestamps includes the end. BETWEEN '2025-01-01' AND '2025-01-31' misses most of Jan 31; use >= start AND < next_day.
  • Foreign keys aren't indexed automatically in PostgreSQL — index the referencing column or deletes on the parent table scan the child.
  • UPDATE/DELETE without WHERE hits every row. Run the SELECT first, or work inside BEGIN so you can ROLLBACK.

One-Liner Summary

SQL is the universal language for querying relational data -- master JOINs, window functions, CTEs, and EXPLAIN ANALYZE and you can answer any data question efficiently.