SQL Cheatsheet

Select all columns from a table

Select columns with an alias

Unique values only

Sort by multiple columns

Pagination (skip 20, take 10)

Standard SQL pagination

First N rows

Conditional expression

First non-NULL value

Return NULL if values are equal (avoid division by zero)

Combine conditions

Match any value in a list

Inclusive range

Pattern match (% any chars, _ one char)

Case-insensitive LIKE

Test for NULL (never use = NULL)

Rows that have related rows

Regular-expression match

Regular-expression match

Only rows that match in both tables

All rows from A, matching rows from B (NULL if none)

All rows from B, matching rows from A

All rows from both tables (not in MySQL: UNION a LEFT and RIGHT join)

Rows in A with no match in B (anti-join)

Every combination of rows (Cartesian product)

Self join

Join on same-named columns

Top-N per group with a lateral join

Count rows

Count unique values

Common aggregate functions

Aggregate per group

Filter groups after aggregation

Concatenate values of a group

Concatenate values of a group

Conditional aggregate

Conditional aggregate (portable)

Subtotals and a grand total

Number rows within each group

Rank with gaps on ties

Rank without gaps

Running total

7-row moving average

Value from the previous row

Value from the next row

First value in the window

Split rows into N buckets (quartiles)

Latest row per group

Subquery in WHERE

Correlated scalar subquery

Common table expression (CTE)

Recursive CTE (walk a hierarchy)

Combine results, removing duplicates

Combine results, keeping duplicates (faster)

Rows present in both results

Rows in the first result but not the second

Insert a row

Insert multiple rows

Insert from a query

Update matching rows

Delete matching rows

Remove all rows quickly

Upsert

Upsert

Standard MERGE (upsert)

Return modified rows

Create a table with identity key and defaults

Create a table with auto-increment key

Create only if missing

Add a column

Remove a column

Rename a column

Add a foreign key

Add a check constraint

Delete a table

Create a view

Create an index

Unique expression index

Composite index (column order matters)

Partial index

Build an index without locking writes

Remove an index

Show the execution plan with real timings

Show the query plan

Update planner statistics

Run statements atomically

Undo the current transaction

Partial rollback inside a transaction

Strictest isolation level

Lock selected rows until commit

Queue pattern: grab an unlocked row

Concatenate strings

Concatenate with the standard operator

Common string functions

Substring

Replace text

Convert a type

Current timestamp and date

Truncate to month

Date arithmetic

Date arithmetic

Extract part of a date

Read a JSON field / containment

Read a JSON field

Create a database user

Grant privileges

Revoke privileges

psql: list tables, describe table, list DBs, connect

MySQL: list tables, describe table, list DBs

sqlite3 CLI: list tables, show schema