Tag archive
Commands grouped around the same concept, pattern, or recurring problem.
Produce multiple independent aggregation levels in one query using `GROUPING SETS` — more flexible than `ROLLUP` because you choose exactly which combinations to include.
Use `CASE WHEN` inside `SUM` or `COUNT` to produce multiple metrics from a single pass over grouped data.
Use `WIDTH_BUCKET(value, low, high, n)` to assign each row to one of `n` equal-width buckets between `low` and `high` — the foundation for building numeric histograms directly in SQL.
Use `NTILE(n)` to divide an ordered result set into `n` roughly equal buckets and assign each row a bucket number.
Construct a JSON object literal from individual column values within a query, returning structured data without a separate serialization step.
Accumulate a column's values row by row using `SUM` as a window function.
Compute the arithmetic mean of numeric values across matching rows.
Define several named subqueries in one `WITH` block, each building on the previous, before the final `SELECT`.
Convert string values to all uppercase or all lowercase using `UPPER` and `LOWER`.
Use `IS NULL` and `IS NOT NULL` to filter rows based on whether a column has a value.
Return all rows from both tables, filling NULLs on either side when there is no matching row.
Merge result sets from two `SELECT` statements into one, removing duplicate rows by default.
Append result sets together without removing duplicates.
Pull the prior year's value into the current row with `LAG`, then compute the percentage change — a common pattern for revenue, signups, and other business KPIs.
Match rows where two values are equal, treating two NULLs as equal instead of unknown.
Smooth time-series data by computing a rolling average over a fixed window of preceding rows using `AVG … OVER (ORDER BY … ROWS BETWEEN n PRECEDING AND CURRENT ROW)`.
Use `GROUP BY CUBE(col_a, col_b)` to generate subtotals for every possible combination of those columns — the full power set — in a single query.
Use `ROWS BETWEEN` in a window function to aggregate only the rows immediately surrounding each row.
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.
Aggregate a result set into a single row that reports how many rows matched.
Count only the distinct values in a column, ignoring duplicates.
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`.
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 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 all rows from the right table and matching rows from the left, filling NULL where no match exists.
Return all rows from the left table and matching rows from the right, filling NULL where no match exists.
Use each engine's JSON path syntax in a `WHERE` clause to keep only rows whose JSON column contains a matching value.
Use `IN` to match a column against a list of values in a single `WHERE` clause.
Reference the outer query's current row inside a subquery to compute a per-row comparison value.
Return only rows that match a condition, with explicit ordering for stable output.
Use `BETWEEN` to match rows where a column falls within an inclusive lower and upper bound.
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`.
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.
Use `MIN` and `MAX` to find the lowest and highest values in a column.
Produce every possible pairing of rows from two tables using `CROSS JOIN`.
Return the current date and time using `CURRENT_TIMESTAMP` or engine-specific functions.
Return the number of characters in a string using `LENGTH` or `LEN`.
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.
Populate a table by selecting rows from another table using `INSERT INTO ... SELECT`.
Ask the database how it plans to execute a SELECT so you can understand scans, joins, and index usage.
Reference the same table twice using aliases to compare or relate rows within the same dataset.
Combine rows from two tables when matching keys exist in both tables.
Return only part of a result set using the row-limiting syntax each engine supports.
Acquire a row-level write lock inside a transaction so no other session can modify those rows before your update completes.
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.
Assign sequential integers to rows within a partition without collapsing the result set the way `GROUP BY` does.
Skip earlier rows and return the next slice of a sorted result set.
Turn distinct row values into separate columns using `CASE WHEN` inside an aggregate.
Assign rank numbers to rows within a partition, controlling whether tied ranks leave gaps in the sequence.
Extract a scalar value from a JSON column using the JSON function or operator each engine supports.
Use `LATERAL` (or `CROSS APPLY` on SQL Server) to let a subquery reference columns from the preceding table in the `FROM` clause.
Use `AS` to give result columns clearer labels without changing the underlying table schema.
Substitute all occurrences of a substring with a new value using `REPLACE`.
Evaluate conditions row-by-row inside a `SELECT` to produce computed columns based on branching logic.
Use `DISTINCT` to remove duplicate rows from a result set.
Use `GREATEST` and `LEAST` to compare multiple values in a single expression without a `CASE WHEN` chain.
Truncate a timestamp to the start of a month, day, or hour using engine-specific functions.
Return every row from a table with an explicit sort order for deterministic output.
Include arithmetic, string, or conditional expressions directly in a `SELECT` list and give them an alias with `AS`.
Return a random sample by ordering rows with the engine's random function and applying a row limit.
Retrieve a fixed number of rows from the top of a sorted result set using `LIMIT`, `TOP`, or `FETCH FIRST`.
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 more than one sort key so ties are broken deterministically.
Control the order of result rows explicitly instead of relying on storage order.
Add numeric values across matching rows and return one aggregate total.
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.