Engine archive
Every guide here is marked as supported for PostgreSQL, with examples you can follow directly.
Reference values from preceding or following rows within an ordered partition without writing a self-join.
Return a value from the first row of a window partition, useful for comparing each row against the group leader.
Extend a table's structure by adding a new column with `ALTER TABLE`.
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.
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.
Collect values from multiple rows into a single JSON array, ordered and grouped by other columns.
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.
Group a stream of user events into sessions by labelling each event with a session ID that increments whenever the gap since the previous event exceeds a threshold.
Use `EXISTS` when you only need to test whether a related row exists, not return one output row per match.
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 breadcrumb path string as a recursive CTE walks a parent-child tree.
Use COUNT as a window function to number how many rows have appeared up to the current row.
Accumulate a column's values row by row using `SUM` as a window function.
Divide the sum of value times weight by the sum of weights instead of averaging pre-aggregated values.
Compute the arithmetic mean of numeric values across matching rows.
Use `SUM(...) OVER ()` to compare each row's value to the total without collapsing the result set.
Use percent-rank window functions to express each row's position within an ordered group.
Find how many days separate two date values using each engine's date difference function.
Define several named subqueries in one `WITH` block, each building on the previous, before the final `SELECT`.
Modify the data type, default value, or nullability of an existing column without recreating the table.
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.
`UNION` removes duplicates, while `UNION ALL` keeps them. Picking the wrong one silently changes row counts.
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.
Find the median or any percentile value using `PERCENTILE_CONT` in PostgreSQL, MariaDB, and SQL Server, or a window-function workaround in MySQL and SQLite.
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 `ABS` to strip the sign from a number and `MOD` (or the `%` operator) to get the integer remainder after division.
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.
Measure the strength of a linear relationship between two columns with `CORR(y, x)` and covariance with `COVAR_SAMP` / `COVAR_POP` in PostgreSQL.
Measure how spread out values are with `STDDEV_SAMP` / `VAR_SAMP` in MySQL, MariaDB, and PostgreSQL, or `STDEV` / `VAR` in SQL Server.
Aggregate multiple string values from grouped rows into a single delimited string.
Use `ROWS BETWEEN` in a window function to aggregate only the rows immediately surrounding each row.
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(...), '')`.
Reshape a wide table by turning multiple columns into key-value row pairs — the inverse of a PIVOT operation.
Strip the time portion from a timestamp so only the calendar date remains.
Change a value from one SQL type to another using an explicit cast.
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.
Audit a table for `NULL` values by counting how many rows have no value in each column of interest.
Aggregate a result set into a single row that reports how many rows matched.
Count only the distinct values in a column, ignoring duplicates.
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.
Define a new table with column names and data types using `CREATE 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.
Permanently remove a table and all its data with `DROP TABLE`.
Use `ROW_NUMBER()` to identify duplicate rows and delete every copy except the one you want to keep.
Remove child rows that reference missing parent rows before enforcing referential integrity.
Remove matching rows with `DELETE`, then query the remaining table contents.
Remove rows from one table when a matching row exists in another table using a subquery.
Remove rows from one table based on matching rows in another table.
Flag values that fall outside the interquartile fence `[Q1 − 1.5 × IQR, Q3 + 1.5 × IQR]` using `PERCENTILE_CONT` in PostgreSQL, MariaDB, and SQL Server.
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.
Use `SIGN` to reduce any number to -1, 0, or 1 based on whether it is negative, zero, or positive.
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 the portion of a string that matches a regular expression pattern, useful for parsing structured text without fixed delimiters.
Return components such as year, month, and day from a date or timestamp value.
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 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.
Produce a row for every date in a range — even dates with no data — by generating a date spine and left-joining actual records onto it.
Define a column default so inserts can omit routine values and still store a complete row.
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.
Return rows where a JSON array contains a specific scalar value.
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.
Match string columns against a wildcard pattern using `LIKE`.
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.
Compare normalized text values with LOWER or UPPER so matching does not depend on stored capitalization.
Group rows by the candidate key and keep only values that appear more than once.
Generate the expected numbers and left join to the existing table to find missing ids or sequence values.
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`.
Use a `WHERE` predicate to list rows that already break a business or constraint rule.
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.
Use `MIN` and `MAX` to find the lowest and highest values in a column.
Compute the least-squares slope, intercept, and R² of a linear regression directly in SQL using `REGR_SLOPE(y, x)`, `REGR_INTERCEPT(y, x)`, and `REGR_R2(y, x)` in PostgreSQL.
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.
Produce a consecutive sequence of integers without a base table — using `generate_series` in PostgreSQL or a recursive CTE in all other engines.
Produce every possible pairing of rows from two tables using `CROSS JOIN`.
Define an identity or auto-increment column so new rows receive numeric IDs without the insert supplying them.
Return the current date and time using `CURRENT_TIMESTAMP` or engine-specific functions.
Return the weekday number or name for a date using engine-specific date functions.
Use LAST_VALUE with an explicit full-partition frame to compare each row with the final row in its group.
Return the number of characters in a string using `LENGTH` or `LEN`.
Create stable run groups without a primary key by subtracting two `ROW_NUMBER()` calculations over the same ordered events.
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.
Add new data to a table, then verify the inserted row with a deterministic query.
Add several rows in one statement using a multi-value `INSERT`.
Atomically insert a row if it does not exist or update it if it does, using the database's native conflict-resolution syntax.
Insert rows without raising an error when a unique constraint is violated, using each engine's conflict-ignore syntax.
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 multiple text values into one string in the query result.
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.
Combine multiple text values into one hyphen-separated key-like string.
Return only part of a result set using the row-limiting syntax each engine supports.
Inspect a table definition to see its column names and data types.
Inspect the current database or schema to see which tables exist.
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.
Rescale each row's value to the number of standard deviations it sits above or below the mean using `(value − AVG … OVER ()) / STDDEV_SAMP … OVER ()`.
Assign sequential integers to rows within a partition without collapsing the result set the way `GROUP BY` does.
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.
Mark a named checkpoint inside an open transaction so you can roll back to that point without aborting the whole transaction.
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.
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 column's name in place without dropping or recreating the table or its data.
Change a table name while keeping its existing rows and columns.
Use `AS` to give result columns clearer labels without changing the underlying table schema.
Use `REPEAT` to duplicate a string a given number of times, and `REVERSE` to flip its character order.
Return the first non-null value from a list of expressions.
Use `REGEXP_REPLACE` to substitute every match of a pattern in a string with a replacement value.
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 `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.
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.
Control the precision or snapping of numeric results using `ROUND` for half-up rounding, `CEIL` to round up to the next integer, and `FLOOR` to round down.
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 `ROW_NUMBER()` to rank rows within each group and keep only the highest-ranked row.
Control how much a transaction can see concurrent changes made by other transactions.
Use more than one sort key so ties are broken deterministically.
Use a `CASE` expression in `ORDER BY` to apply a custom sort order that is not alphabetical or numeric.
Control the order of result rows explicitly instead of relying on storage order.
Create an index on one or more columns to make lookups and joins faster.
Expand a comma-separated or delimited string into one row per element, turning a single cell into a proper set of rows.
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.
Use `MERGE` to insert new rows, update matching ones, and delete rows that should be removed — all in a single statement against a source table.
Use a correlated subquery inside `EXISTS` to include rows only when related rows are found in another table.
Add numeric values across matching rows and return one aggregate total.
Use `WITH RECURSIVE` to repeatedly join a query to its own results, enabling traversal of tree or graph structures.
Remove leading and trailing whitespace from string values with `TRIM`.
`COUNT(column)` skips `NULL` values, while `COUNT(*)` counts every row that made it into the result set.
Set a column to different values depending on other column values in the same row by using `CASE WHEN` inside a `SET` clause.
Modify existing rows with `UPDATE`, then verify the changed data with a stable query.
Use a subquery in the `WHERE` clause of an `UPDATE` to restrict which rows are modified based on data from another table.
Use a join inside an `UPDATE` statement to copy or derive values from a related 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.
A row limit without an explicit sort can return different rows over time or across engines.
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.
Schema and metadata choices tend to survive longer than any one statement, which is why naming and structure deserve more care than a temporary query draft.