ZipKit
Cheatsheet

SQL Cheatsheet

This cheatsheet is a lookup for everyday SQL: the statements, the join types, aggregate and window functions, and the NULL rules that cause most wrong results. It is for developers who write queries a few times a week and want working syntax fast. Every query below was run on SQLite 3.45.1 through the Python sqlite3 module, and places where PostgreSQL behaves differently are flagged. The SQL glossary page explains what the language is.

Quick reference

Statements

Task Statement
Read rows `SELECT cols FROM t WHERE cond`
Add rows `INSERT INTO t (a, b) VALUES (1, 2)`
Change rows `UPDATE t SET a = 1 WHERE cond`
Remove rows `DELETE FROM t WHERE cond`
Insert or update `INSERT ... ON CONFLICT (key) DO UPDATE SET ...`
Create a table `CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT NOT NULL)`
Add an index `CREATE INDEX idx_name ON t (col)`
Partial index `CREATE INDEX idx ON t (col) WHERE cond`
Return changed rows `INSERT/UPDATE/DELETE ... RETURNING cols`

RETURNING needs SQLite 3.35.0 (March 2021) or newer. Upsert with ON CONFLICT arrived in SQLite 3.24.0 (June 2018).

Order a SELECT is evaluated

You write SELECT first, but the engine works in a different order. Knowing the real order explains why WHERE cannot contain an aggregate and HAVING can.

Step Clause What it does
1 `FROM`, `JOIN` Builds the working set of rows
2 `WHERE` Drops rows that fail the condition
3 `GROUP BY` Combines rows into groups
4 `HAVING` Drops groups that fail the condition
5 `SELECT` Computes output columns
6 `DISTINCT` Removes duplicate rows
7 `UNION`, `INTERSECT`, `EXCEPT` Combines result sets
8 `ORDER BY` Sorts the result
9 `LIMIT`, `OFFSET` Cuts the result

Joins

Join Rows returned
`INNER JOIN` Only rows with a match on both sides
`LEFT JOIN` All left rows, with NULL where the right side has no match
`RIGHT JOIN` All right rows, with NULL on the left
`FULL JOIN` All rows from both sides
`CROSS JOIN` Every left row paired with every right row

SQLite added RIGHT and FULL joins, and IS DISTINCT FROM, in version 3.39.0 (2022), so older builds reject them.

Aggregates and set operators

Expression Result
`COUNT(*)` Number of rows, NULLs included
`COUNT(col)` Number of rows where `col` is not NULL
`COUNT(DISTINCT col)` Number of different non-NULL values
`SUM`, `AVG`, `MIN`, `MAX` Skip NULL values
`GROUP_CONCAT(col)` Joins values into one string (SQLite)
`UNION` Combined rows with duplicates removed
`UNION ALL` Combined rows, duplicates kept, faster

NULL rules

Expression Result
`x = NULL` NULL, so the row is never kept
`x IS NULL` True when x is NULL
`x <> 'UK'` NULL when x is NULL, so those rows drop out
`x IS NOT 'UK'` Keeps NULL rows in SQLite. The portable spelling is `x IS DISTINCT FROM 'UK'`
`COALESCE(x, '?')` First non-NULL argument
`NULL AND false` false
`NULL OR true` true

Common patterns

The queries use two small tables. Customer 3 (Grace) and customer 4 (Alan) have no orders, and order 4 has no customer.

CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT NOT NULL, country TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total REAL, placed TEXT);
INSERT INTO customers VALUES (1,'Ada','UK'),(2,'Linus','FI'),(3,'Grace',NULL),(4,'Alan','UK');
INSERT INTO orders VALUES (1,1,30.0,'2026-01-05'),(2,1,19.5,'2026-02-11'),(3,2,12.25,'2026-02-12'),(4,NULL,8.0,'2026-03-01'),(5,2,40.0,'2026-03-09');

Find rows with no match (anti-join)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
('Grace',)
('Alan',)

Use NOT EXISTS or LEFT JOIN ... WHERE o.id IS NULL to list customers without orders. Both gave the same two rows here.

Total per group with a filter on the total

SELECT customer_id, COUNT(*) n, SUM(total)
FROM orders GROUP BY customer_id HAVING SUM(total) > 20;
(1, 2, 49.5)
(2, 2, 52.25)

WHERE filters rows before grouping, and HAVING filters the groups. The order with no customer forms its own NULL group, and it is dropped here because its sum is 8.0.

Running total and ranking with window functions

SELECT customer_id, total,
  SUM(total) OVER (PARTITION BY customer_id ORDER BY placed) AS running
FROM orders WHERE customer_id IS NOT NULL;
(1, 30.0, 30.0)
(1, 19.5, 49.5)
(2, 12.25, 12.25)
(2, 40.0, 52.25)

A window function keeps every row and adds a computed column, unlike GROUP BY, which collapses rows. RANK() OVER (ORDER BY total DESC) numbers rows the same way. SQLite supports window functions since 3.25.0 (September 2018).

Name an intermediate result with a CTE

WITH big AS (SELECT customer_id, SUM(total) s FROM orders GROUP BY customer_id)
SELECT c.name, big.s FROM big JOIN customers c ON c.id = big.customer_id
WHERE big.s > 30;
('Ada', 49.5)
('Linus', 52.25)

A WITH clause reads top to bottom and avoids deeply nested subqueries.

Insert or update in one statement, and return the row

INSERT INTO stock VALUES ('A', 5)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty RETURNING *;
('A', 5)
('A', 10)

The statement ran twice on a stock table with sku TEXT PRIMARY KEY. The first run inserted, and the second hit the conflict and added 5. excluded is the row that was rejected. PostgreSQL uses the same ON CONFLICT and RETURNING syntax.

Page through results

SELECT id, name FROM customers ORDER BY id LIMIT 2 OFFSET 1;
(2, 'Linus')
(3, 'Grace')

Always pair LIMIT with ORDER BY. The PostgreSQL manual warns that without it you get an unpredictable subset of rows.

Pitfalls

  • NOT IN with a NULL in the list: WHERE id NOT IN (SELECT customer_id FROM orders) returned zero rows because order 4 has a NULL customer_id. The same intent written with NOT EXISTS returned Grace and Alan. Prefer NOT EXISTS, or filter NULLs out of the subquery.
  • Filtering the right table in WHERE after a LEFT JOIN: WHERE o.total > 20 removed Grace and Alan, which turns the query into an inner join. Move the condition into ON to keep unmatched left rows.
  • *COUNT() against COUNT(col):* on the customers table, COUNT() gave 4, COUNT(country) gave 3 and COUNT(DISTINCT country) gave 2. Pick the one that matches the question.
  • UNION hiding duplicates: SELECT id FROM customers UNION SELECT customer_id FROM orders returned 5 rows, while UNION ALL returned 9. UNION has to do extra work to remove duplicates, so use UNION ALL when duplicates cannot occur or do not matter.
  • NULL sort position differs by database: SQLite sorted NULL first for ORDER BY x and last for DESC. PostgreSQL treats NULL as larger than any value, so it is last for ascending order. Add NULLS FIRST or NULLS LAST to get the same order in both.
  • Selecting a column that is not grouped: SQLite ran SELECT customer_id, total ... GROUP BY customer_id without an error, and the SQLite docs call such a column a bare column. PostgreSQL rejects it unless the column is functionally dependent on the grouped columns.
  • FETCH FIRST on SQLite: SELECT name FROM customers FETCH FIRST 2 ROWS ONLY failed with near "FIRST": syntax error. PostgreSQL accepts both LIMIT and FETCH FIRST. Use LIMIT for portability to SQLite and MySQL.
  • Building SQL from strings: concatenating user input into a statement allows injection. Pass values as bound parameters, such as WHERE name = ?.

Related ZipKit tools

  • SQL to Prisma Schema — converts CREATE TABLE statements into Prisma ORM models
  • JSON to MySQL — generates CREATE TABLE statements from a JSON array
  • JSON to CSV Converter — flattens a JSON array of objects into CSV for loading into a table
  • CSV Viewer — inspects a CSV file in a sortable table before you import it

Related cheatsheets