SQL query guide and searchable reference

Read specific columns from a table.

Rename a column in the output, or shorten a table name for the rest of the query.

Every column of every table in the FROM clause.

Collapse duplicate rows in the result.

Sort the result. The only way to guarantee row order.

Return at most N rows, optionally skipping the first M.

If/else inside a query: bucket, relabel, or aggregate conditionally.

First non-NULL value, and turning a sentinel value back into NULL.

Convert a value from one type to another, explicitly.

Line and block comments, and the one that shows up in your slow-query log.

Keep only the rows matching a condition.

Match against a list of values or the result of a subquery.

Inclusive range test. Careful with timestamps.

Wildcard string matching: % for any run of characters, _ for exactly one.

The only way to test for NULL. Equals NULL is always unknown.

Filter with a regex when LIKE is not expressive enough.

Rows that match on both sides. Everything unmatched disappears.

Every row on the left, with NULLs where the right side has no match.

Every row from both sides, NULL-padded wherever there is no match.

Every combination of both sides. Deliberate, or a bug.

Join a table to itself to compare rows within it.

Find rows in A that have nothing matching in B.

Three ways to state the join condition, only two of which are safe.

A subquery on the right that can refer to columns from the left.

Collapse rows into one row per distinct combination of the grouping columns.

The five aggregate functions behind most reporting queries.

They are not the same, and only one of the differences matters.

Filter groups after aggregation, the way WHERE filters rows before it.

How many different values, not how many rows.

Count or sum only the rows matching a condition, in one pass.

Collapse a group of rows into one delimited string.

Several levels of aggregation, including subtotals, in one query.

Aggregate across a group while keeping every individual row.

A 1, 2, 3 sequence within each partition. The workhorse of dedupe and top-N.

Three ways to number rows, differing only in how they treat ties.

Read a value from the previous or next row without a self join.

Which rows around the current one the window actually covers.

Split rows into N buckets, or ask for a percentile directly.

The first, last or Nth row of the window, projected onto every row.

A column that accumulates down the ordered result.

A subquery returning exactly one row and one column, used as a value.

A subquery that acts as a table for the outer query.

Does at least one matching row exist? Stops at the first hit.

A subquery that references the outer row, so it runs per row.

Name a subquery at the top and use it like a table below.

Walk a hierarchy or generate a series by repeating a query until it stops producing rows.

Stack two result sets. UNION deduplicates, UNION ALL does not.

Rows in both results, or rows in the first and not the second.

Add rows, one or many, and get their generated keys back.

Populate a table from a query instead of from literals.

Change values in existing rows.

Set a column from a joined table, in every dialect.

Remove rows, optionally using a join to decide which.

Insert a row, or update it if the key already exists, atomically.

Insert, update and delete in one statement, driven by a source query.

Get the affected rows back from an INSERT, UPDATE or DELETE.

Empty a table fast. Not a DELETE with a shortcut.

Group statements so they all take effect, or none do.

What concurrent transactions are allowed to see of each other.

Lock the rows you read so nobody else can change them until you commit.

Define a table: columns, types, defaults and constraints.

The column (or columns) that uniquely identify a row.

Point a column at another table and say what happens when the parent goes.

The constraints that keep bad rows out in the first place.

Add, drop, rename or retype a column on a live table.

A saved query that behaves like a table.

A view whose result is stored on disk and refreshed on demand.

A real table scoped to your session, useful for staging a multi-step job.

The single biggest lever on query speed, and not free.

Equality columns first, then the range or sort column. Order decides everything.

Index only the rows you actually query.

Put every column the query needs in the index so the table is never touched.

Ask the database what it plans to do, or what it actually did.

An index is only used when the indexed column sits bare on one side of the comparison.

Name your columns: less I/O, stable contracts, and index-only scans stay possible.

Three ways to ask the same question, with different NULL behaviour and different plans.

Page through a large table in constant time, instead of OFFSET getting slower every page.

One query for the list, then one more per row. The commonest ORM performance bug.

The missing index nobody notices until a parent delete locks the table.

The planner chooses from estimates. Stale estimates produce bad plans.

Round a timestamp down to a day, week or month so you can group by it.

Add, subtract and difference dates without an off-by-one.

Pull the year, month, hour or day-of-week out of a timestamp.

Store UTC, convert at the edges, and be careful what a day means.

Concatenate, slice, trim, pad, replace, change case.

Turn a comma-separated column, or a parameter, into one row per value.

Reach into a JSON document stored in a column, and index the path you filter on.

Return a nested document from one query instead of stitching it in application code.

Which values appear more than once, and how many times.

Keep the oldest (or newest) row per key and remove the rest.

The classic interview question, and what ties do to it.

The three most recent orders for every user, in one query.

The most recent order per user, the current status per ticket, the last reading per sensor.

Each row as a share of the whole, without a second query.

This period next to the last one, and the change between them.

Show every day in the range, including the days with no rows.

One row per entity, one column per category.

Turn a wide table into a long one: one row per (entity, metric).

Group consecutive rows into runs: streaks, sessions, uninterrupted periods.

The middle value, which is usually a better summary than the average.

Count rows per value range, with empty buckets included.

Pull a handful of random rows without sorting the whole table.

Rows only in A, only in B, and rows present in both but different.

Child rows pointing at a parent that is gone, and parents nothing points at.

Smooth a noisy daily series into a 7-day trend line.

Group users by their signup month and track how many come back each month after.

Word-aware search with ranking, instead of LIKE with wildcards.

What is actually using the disk, and which indexes are dead weight.

One NULL in the subquery and your result set is empty. No error, no warning.

Filtering the right table in WHERE turns an outer join back into an inner one.

Joining two one-to-many tables multiplies rows, and every SUM afterwards is wrong.

If you needed DISTINCT to get the right row count, the join was wrong.

count_a / count_b returns 0 when both are integers.

Ties break arbitrarily, so paginated results repeat and skip rows.

Comparing a string column to a number makes the engine convert every row.

The mistake everyone makes once. Make it recoverable.

Bind parameters. Never build a query by concatenating user input.