Rank archive
Guides grouped by the expected difficulty and amount of SQL context they assume.
Enforce referential integrity by linking a column to the primary key of another table so orphaned rows cannot be inserted.
Calculate a future or past date by adding or subtracting an interval from an existing date.
Use `GROUP BY ROLLUP` to produce subtotals and a grand total alongside regular grouped rows in one query.
Count or sum only the rows that match a specific condition without a WHERE clause — using `FILTER (WHERE ...)` in PostgreSQL and SQLite, or `CASE WHEN` inside the aggregate for other engines.
Format several known values into one compact `key=value` string for transport or handoff.
Use `EXISTS` when you only need to test whether a related row exists, not return one output row per match.
Use `NTILE(n)` to divide an ordered result set into `n` roughly equal buckets and assign each row a bucket number.
Use COUNT as a window function to number how many rows have appeared up to the current row.
Divide the sum of value times weight by the sum of weights instead of averaging pre-aggregated values.
Find how many days separate two date values using each engine's date difference function.
Modify the data type, default value, or nullability of an existing column without recreating the table.
`UNION` removes duplicates, while `UNION ALL` keeps them. Picking the wrong one silently changes row counts.
Merge result sets from two `SELECT` statements into one, removing duplicate rows by default.
Append result sets together without removing duplicates.
Match rows where two values are equal, treating two NULLs as equal instead of unknown.
Measure how spread out values are with `STDDEV_SAMP` / `VAR_SAMP` in MySQL, MariaDB, and PostgreSQL, or `STDEV` / `VAR` in SQL Server.
Use `NULLS FIRST` or `NULLS LAST` to explicitly position NULL values at the top or bottom of a sorted result set.
Shift a stored UTC timestamp to a local time zone for display using engine-specific conversion functions.
Normalize empty or whitespace-only text values to `NULL` with `NULLIF(TRIM(...), '')`.
Convert strings to numbers (or other types) while returning `NULL` for values that cannot be converted, instead of raising an error that aborts the query.
Return the number of items stored in a JSON array value.
Measure how many unique users completed each stage of a multi-step flow using `COUNT(DISTINCT CASE WHEN event_name = '...' THEN user_id END)` per step.
Save a `SELECT` statement as a named view so it can be queried like a table.
Materialize a query's output as a new table in one step using `CREATE TABLE AS SELECT` or `SELECT INTO`.
Build a table that exists only for the current session and is discarded automatically when the connection closes.
Find rows whose date or timestamp intervals intersect by checking that neither range ends before the other begins — using the `OVERLAPS` operator in PostgreSQL or a manual comparison in other engines.
Turn one plain-text payload into a base64 string for transport or lookup.
Create a unique index so the database rejects duplicate values in one or more columns.
Turn a list of values into individual rows — using `UNNEST` for native arrays in PostgreSQL, `JSON_TABLE` in MySQL, `OPENJSON` in SQL Server, or `json_each` in SQLite.
Return only part of a text value by position and length.
Return the year component from a date or timestamp value.
Return all rows from the left table and matching rows from the right, filling NULL where no match exists.
Use an inclusive start and exclusive end to match a whole date range without missing rows that include times.
Use `HAVING` to filter aggregate results after `GROUP BY`, the same way `WHERE` filters individual rows before grouping.
Put right-table filters in the `ON` clause when you want to keep unmatched left-side rows.
Use each engine's JSON path syntax in a `WHERE` clause to keep only rows whose JSON column contains a matching value.
Reference the outer query's current row inside a subquery to compute a per-row comparison value.
Group rows by the candidate key and keep only values that appear more than once.
Find child rows whose foreign key value does not match any row in the parent table.
Return only the rows that appear in both result sets using `INTERSECT`.
Return rows from the first query that do not appear in the second query using `EXCEPT`.
Return rows from one table that have no corresponding row in a second table using a LEFT JOIN with a NULL check.
Use a `LEFT JOIN` and filter for `NULL` on the joined table to find missing relationships.
Identify the value that appears most often in a column using `GROUP BY` with `ORDER BY COUNT(*) DESC LIMIT 1`, or with `MODE() WITHIN GROUP` in PostgreSQL.
Locate where a substring first appears inside a string, returning its 1-based character position.
Convert a date value to a formatted string using a custom pattern such as MM/DD/YYYY.
Convert a numeric value into a formatted string with a fixed number of decimal places, optionally including thousands separators.
Define an identity or auto-increment column so new rows receive numeric IDs without the insert supplying them.
Return the weekday number or name for a date using engine-specific date functions.
Aggregate rows by category with `GROUP BY` and count how many rows fall into each group.
Bucket dates into month starts, then aggregate counts or totals per month.
Use more than one column in `GROUP BY` when each combination defines a separate aggregate bucket.
Use `BEGIN`, `COMMIT`, and `ROLLBACK` to execute multiple statements as an atomic unit.
Add `WITH TIES` when limiting rows to also return every row that shares the same ORDER BY value as the last included row, preventing arbitrary tie-breaking at the result boundary.
Insert rows without raising an error when a unique constraint is violated, using each engine's conflict-ignore syntax.
Ask the database how it plans to execute a SELECT so you can understand scans, joins, and index usage.
Join two tables using a comparison other than equality — such as `BETWEEN` or `<` — to match rows based on ranges rather than exact values.
Combine rows from two tables when matching keys exist in both tables.
Use `(col_a, col_b) IN ((v1, v2), (v3, v4))` to filter on combinations of columns simultaneously — cleaner and less error-prone than chaining `AND`/`OR` conditions.
Use `WITH` to define a named temporary result set that can be referenced in the main query, improving readability over inline subqueries.
Define a window specification once with a `WINDOW` alias and reference it in multiple `OVER` clauses — avoiding repetition when several window functions share the same partition and ordering.
Embed a `SELECT` inside another query to filter, compute, or supply values that depend on aggregated or derived data.
Left-pad or right-pad a string to a fixed width by filling with a repeated character.
Skip earlier rows and return the next slice of a sorted result set.
Convert a text string in a known format into a native DATE value using engine-specific parsing functions.
Turn distinct row values into separate columns using `CASE WHEN` inside an aggregate.
Permanently delete a column and all its data from a table using ALTER TABLE.
Delete every row in a table quickly with TRUNCATE TABLE, which is faster than DELETE with no WHERE clause.
Drop an index that is no longer useful so writes no longer have to maintain it.
Change a table name while keeping its existing rows and columns.
Use `REGEXP_REPLACE` to substitute every match of a pattern in a string with a replacement value.
Evaluate conditions row-by-row inside a `SELECT` to produce computed columns based on branching logic.
Use `NULLIF` to convert a specific value to `NULL`, most commonly to prevent division-by-zero errors.
Get the affected rows back in the same statement — using `RETURNING` in PostgreSQL, MariaDB, and SQLite, or `OUTPUT` in SQL Server.
Return a random sample by ordering rows with the engine's random function and applying a row limit.
Use PostgreSQL's `DISTINCT ON (col)` to keep only the first row for each unique value of a column, with `ORDER BY` controlling which row within each group is considered first.
Use a `CASE` expression in `ORDER BY` to apply a custom sort order that is not alphabetical or numeric.
Define a column whose value is always derived from an expression over other columns — the database recomputes or stores it on every write so queries never need to repeat the formula.
Set a column to different values depending on other column values in the same row by using `CASE WHEN` inside a `SET` clause.
Use a subquery in the `WHERE` clause of an `UPDATE` to restrict which rows are modified based on data from another table.
Wrap a `SELECT` in the `FROM` clause to create a temporary result set you can filter or join against as if it were a real table.
Supply a small set of constant rows directly inside a query using a `VALUES` derived table — no temporary table or CTE required.
Filter `NULL` values out of the right side before using `NOT IN`, or the predicate can exclude everything.
`WHERE` filters individual rows before grouping. `HAVING` filters grouped results after aggregates are computed.
Use a `CHECK` constraint to reject rows whose values fall outside an allowed rule.