SQL Cheatsheet

Common SQL with notes on where results surprise people, and how Postgres, MySQL and SQLite differ.

By BoltQuickTools team · Updated November 8, 2026

Examples use a users and orders schema. Syntax is portable unless a comment names a database. To tidy a long query, use the SQL formatter.

Order of evaluation

You write SELECT first, but a query logically runs in this order:

FROM / JOIN  >  WHERE  >  GROUP BY  >  HAVING  >  window functions
             >  SELECT  >  DISTINCT  >  ORDER BY  >  LIMIT

This explains why, in standard SQL, WHERE cannot use a SELECT alias or an aggregate, and why window functions cannot appear in WHERE: those values do not exist yet. To filter on a window function, wrap the query in a CTE.

Filtering

WHERE age >= 18 AND country IN ('US', 'CA')
WHERE name LIKE 'Al%'                  -- % any run, _ exactly one character
WHERE name ILIKE 'al%'                 -- Postgres only: case-insensitive
WHERE email IS NULL
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE id = ANY('{1,2,3}'::int[])       -- Postgres array form
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id)

BETWEEN includes both ends. On a timestamp column, BETWEEN '2026-01-01' AND '2026-12-31' stops at midnight at the start of December 31 and misses that day. The half-open range above does not.

NULL rules

col = NULL                  -- never true; use col IS NULL
col <> 5                    -- NULL rows are excluded too
COALESCE(nickname, name)    -- first non-NULL value
NULLIF(a, 0)                -- NULL if a = 0; avoids divide-by-zero: b / NULLIF(a, 0)
a IS NOT DISTINCT FROM b    -- NULL-safe equals (Postgres; SQLite 3.39+)
a <=> b                     -- NULL-safe equals (MySQL)
a IS b                      -- NULL-safe equals (SQLite)

Aggregates skip NULLs, so COUNT(col) counts non-NULL values while COUNT(*) counts rows. NOT IN (subquery) returns no rows at all if the subquery yields a NULL; use NOT EXISTS. For sorting, Postgres treats NULLs as largest, and MySQL and SQLite as smallest.

Joins

INNER JOIN

SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

Plain JOIN means INNER JOIN. Users with no orders disappear.

LEFT JOIN

SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

Keeps every user. Count a column from the right-hand table: COUNT(*) would report 1 for users with no orders.

Filtering a LEFT JOIN

-- Keeps all users, joining only paid orders:
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'

-- Silently becomes an INNER JOIN:
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'

Rows with no match

SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Self-join

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Grouping and aggregation

SELECT
  country,
  COUNT(*) AS users,
  AVG(age) AS avg_age,
  SUM(total_spent) AS revenue
FROM users
WHERE active = true        -- filters rows before grouping
GROUP BY country
HAVING COUNT(*) > 10       -- filters groups after aggregating
ORDER BY revenue DESC;

Conditional totals

SELECT
  COUNT(*) FILTER (WHERE status = 'paid') AS paid,              -- Postgres, SQLite 3.30+
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_too  -- works everywhere
FROM orders;

Window functions

They compute across related rows without collapsing them. Available in Postgres, MySQL 8.0+ and SQLite 3.25+.

Ranking

SELECT name, country, total_spent,
  ROW_NUMBER() OVER w AS row_num,   -- 1,2,3,4 (ties broken arbitrarily)
  RANK()       OVER w AS rnk,       -- 1,2,2,4
  DENSE_RANK() OVER w AS dense      -- 1,2,2,3
FROM users
WINDOW w AS (PARTITION BY country ORDER BY total_spent DESC);

Top N per group

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;   -- latest order per user

Running totals and moving averages

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 avg_7d
FROM daily_stats;

With ORDER BY and no frame, the default frame is RANGE, which groups rows with equal sort values together. If day can repeat, write ROWS UNBOUNDED PRECEDING explicitly.

Compare with the previous row

SELECT day, views,
  views - LAG(views, 1) OVER (ORDER BY day) AS delta
FROM analytics;

CTEs

WITH recent AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'  -- Postgres
),
high_value AS (
  SELECT user_id, SUM(total) AS spent FROM recent GROUP BY user_id HAVING SUM(total) > 500
)
SELECT u.name, hv.spent
FROM users u
JOIN high_value hv ON hv.user_id = u.id;

CTEs split a query into named steps. See the dialect table for date arithmetic in other databases.

Recursive CTE (org chart)

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, org.level + 1
  FROM employees e
  JOIN org ON e.manager_id = org.id
  WHERE org.level < 20      -- safety limit against cycles in the data
)
SELECT * FROM org;

Changing data

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

-- Upsert (Postgres, SQLite 3.24+)
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = excluded.name;

-- Upsert (MySQL 8.0.19+; older versions use VALUES(name))
INSERT INTO users (id, name) VALUES (1, 'Alice') AS new
ON DUPLICATE KEY UPDATE name = new.name;

UPDATE users SET active = false WHERE last_login < '2025-01-01';
DELETE FROM users WHERE active = false AND created_at < '2020-01-01';

Before a risky UPDATE or DELETE, run the same WHERE as a SELECT, or work inside BEGIN; ... ROLLBACK; until the row count looks right. MySQL DDL statements commit implicitly, so they cannot be rolled back.

Indexes

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email_uq ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial index (Postgres, SQLite)
CREATE INDEX idx_active ON users(email) WHERE active = true;

-- Expression index (Postgres, SQLite; MySQL 8.0.13+ needs extra parentheses)
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- Postgres: build without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

A multi-column index helps queries that filter on its leftmost columns. (user_id, created_at) serves user_id = ? but not created_at > ? alone. CREATE INDEX CONCURRENTLY cannot run inside a transaction, and if it fails it leaves an invalid index to drop.

Dialect differences

WhatPostgresMySQLSQLite
Auto-incrementGENERATED ALWAYS AS IDENTITY or SERIALAUTO_INCREMENTINTEGER PRIMARY KEY
UUID typeNative UUIDBINARY(16) or CHAR(36)TEXT or BLOB
String concata || bCONCAT(a,b); || is OR by defaulta || b
Limit + offsetLIMIT n OFFSET mLIMIT n OFFSET m or LIMIT m, nLIMIT n OFFSET m or LIMIT m, n
RETURNINGYesNo (MariaDB has it for INSERT and DELETE)3.35+
BooleanNativeBOOLEAN is an alias for TINYINT(1)INTEGER 0/1; TRUE and FALSE since 3.23
LIKE caseCase-sensitive; use ILIKEFollows collation; default is case-insensitiveCase-insensitive for ASCII
30 days agoNOW() - INTERVAL '30 days'NOW() - INTERVAL 30 DAYdatetime('now', '-30 days')
Aggregate stringsSTRING_AGG(x, ',')GROUP_CONCAT(x)GROUP_CONCAT(x); STRING_AGG since 3.44
JSON field as textdoc->>'name'doc->>'$.name'doc->>'$.name' (3.38+)
FULL OUTER JOINYesNo; combine LEFT and RIGHT with UNION3.39+
Quote identifiers"name"`name`"name"
Query planEXPLAIN ANALYZEEXPLAIN ANALYZE (8.0.18+)EXPLAIN QUERY PLAN

Performance notes

Last updated