SQL cheat sheet

SQL is the language for asking a relational database for data and changing what is in it. It reads close to English, and the same core works on PostgreSQL, MySQL, SQLite, SQL Server and every other relational database. The reference below is grouped by what you are trying to do, and the filter box searches all of it at once. Type join to see every join, or mysql to see where MySQL differs.

Every snippet is standard SQL unless its notes say otherwise, and was run on PostgreSQL 18, MySQL 8.4 and SQLite 3.53. When the three disagree, the notes column names which one does what. Names like users and orders come from a small example shop; swap in your own.

The four joins, side by side

Joins are easier to see than to describe. Take three users and four orders. Linus has never ordered, and order 13 was a guest checkout with no user. This setup runs unchanged on all three databases.

CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR(50));
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total DECIMAL(10, 2));
 
INSERT INTO users VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus');
INSERT INTO orders VALUES (10, 1, 25.00), (11, 1, 40.00), (12, 2, 15.00), (13, NULL, 30.00);

Each join below is SELECT u.name, o.id, o.total FROM users u <join> orders o ON o.user_id = u.id, with only the join changed.

MySQL has no FULL OUTER JOIN. Get the same five rows by stacking a LEFT JOIN on the unmatched rows of a RIGHT JOIN:

SELECT u.name, o.id, o.total
FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION ALL
SELECT u.name, o.id, o.total
FROM users u RIGHT JOIN orders o ON o.user_id = u.id
WHERE u.id IS NULL;

UNION ALL and the WHERE u.id IS NULL together keep each row once. A plain UNION would also work here, but it would merge any rows that are genuinely identical, so the WHERE form is the safe one.

Top N per group with a window function

"The biggest order for each customer" is one of the most searched SQL questions, and GROUP BY alone cannot answer it: MAX(total) gives the amount but not the rest of the row. Number the rows within each customer, then keep the first.

WITH ranked AS (
  SELECT
    o.user_id,
    o.id AS order_id,
    o.total,
    ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.total DESC, o.id) AS rn
  FROM orders o
  WHERE o.user_id IS NOT NULL
)
SELECT u.name, r.order_id, r.total
FROM ranked r
JOIN users u ON u.id = r.user_id
WHERE r.rn = 1
ORDER BY r.total DESC;

On the tables above this returns two rows: Ada with order 11 for 40.00, and Grace with order 12 for 15.00. Change r.rn = 1 to r.rn <= 3 for the top three per customer. Use RANK() instead of ROW_NUMBER() if tied orders should all come back; the o.id in the ORDER BY only decides which tied row wins when you want exactly one.

A recursive CTE: walking an org chart

A recursive CTE starts from some rows, then joins back to itself until no new rows turn up. It is how SQL walks a tree, such as employees and their managers or categories inside categories.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name VARCHAR(50), manager_id INTEGER);
INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Grace', 1), (3, 'Alan', 1), (4, 'Linus', 2);
 
WITH RECURSIVE chain (id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL                -- start at the top
 
  UNION ALL
 
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id     -- then everyone who reports to a row found so far
)
SELECT name, depth
FROM chain
ORDER BY depth, name;

name   depth
Ada    0
Alan   1
Grace  1
Linus  2

The WHERE manager_id IS NULL part runs once. The part after UNION ALL runs again and again, each time on the rows the last round found, and stops when a round finds nothing. If the data has a loop, such as two people managing each other, it never stops: add WHERE c.depth < 20 to the second part as a guard.

Parameters, not string building

Queries in application code take values from users, and pasting those values into the SQL string is how SQL injection happens. Every database library has placeholders instead. Here it is with Python's built-in sqlite3 module:

import sqlite3
 
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)")
 
with conn:  # commits on success, rolls back on an exception
    conn.executemany(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        [("Ada", "ada@example.com"), ("Grace", "grace@example.com")],
    )
 
email = "ada@example.com' OR '1'='1"  # an attacker's input
row = conn.execute("SELECT id, name FROM users WHERE email = ?", (email,)).fetchone()
print(row)  # None: the whole string was compared as an email address
 
row = conn.execute("SELECT id, name FROM users WHERE email = ?", ("ada@example.com",)).fetchone()
print(row)  # (1, 'Ada')

The ? is filled in by the database, never by string formatting, so the quote in the attacker's input is just a character in an email address. PostgreSQL's psycopg uses %s for the same job and PHP's PDO takes ? or :name. The Python cheat sheet covers the rest of the language, and the PHP cheat sheet has the same pattern with PDO. In R, DBI takes the same ? placeholders, and the R cheat sheet shows dplyr writing the WHERE, GROUP BY and HAVING for you.

Dialect differences at a glance

The places where PostgreSQL, MySQL and SQLite most often disagree. Everything here was checked on the versions named at the top of the page.

Getting around psql, mysql and sqlite3

Each database ships with a command-line client, and each has its own shortcuts for the questions SQL itself does not answer, such as "what tables are there".

To try any of it without installing a server, sqlite3 is a single program that keeps the whole database in one file, and Python ships with SQLite built in. For PostgreSQL or MySQL, the Docker cheat sheet has a compose file that starts PostgreSQL next to an app.

Gotchas

The mistakes almost everyone makes in their first month of SQL.

When an ORDER BY cannot use an index, PostgreSQL sorts in memory with a quick sort, keeps only the rows it needs in a heap when there is a LIMIT, and switches to an external merge sort on disk when the rows do not fit in memory. EXPLAIN ANALYZE names the one it picked as the sort method. All three are on the site as step-through visualisations.

Common questions