Tag archive
Commands grouped around the same concept, pattern, or recurring problem.
Use `CASE WHEN` inside `SUM` or `COUNT` to produce multiple metrics from a single pass over grouped data.
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.
Use `ROWS BETWEEN` in a window function to aggregate only the rows immediately surrounding each row.
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.
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 `IN` to match a column against a list of values in a single `WHERE` clause.
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`.
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`.
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.
Use `WITH` to define a named temporary result set that can be referenced in the main query, improving readability over inline subqueries.
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.
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.
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.
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.