SQL reference

Collects group values into an array. PostgreSQL only.

True when every or any row matches. PostgreSQL; SQLite uses min()/max() on 0 and 1.

Counts unique non-null values.

PostgreSQL alias for bool_and().

Joins values from a group into one string. SQLite name for string_agg.

Collects group values into a JSON array. SQLite and PostgreSQL.

Collects key-value pairs into a JSON object. SQLite only.

The most frequent value in a group. PostgreSQL.

Continuous percentile, such as the median. PostgreSQL.

Sample standard deviation. PostgreSQL; most engines use a different name.

Concatenates group values with a delimiter. PostgreSQL and SQL Server.

Counts rows that match a condition using only standard aggregate syntax.

Like sum() but returns 0.0 instead of NULL when there are no rows. SQLite only.

Sample variance. PostgreSQL; most engines use a different name.

count(*) counts rows; count(column) ignores NULLs.

Applies a condition to a single aggregate without splitting the query.

Collapses rows into groups so aggregates run per group.

Groups by a computed value. PostgreSQL needs the same expression in SELECT.

Filters groups after aggregation, unlike WHERE which filters rows first.

Returns the smallest or largest non-null value.

Adds subtotal rows for the grouping hierarchy. PostgreSQL only.

Joins group values into one string. SQLite calls it group_concat.

Adds or averages non-null numeric values.

Returns a new array with a value appended. PostgreSQL.

The length of an array dimension. PostgreSQL.

The one-based position of a value in an array. PostgreSQL.

Joins an array into a string. PostgreSQL.

Generates a series of values. PostgreSQL; use a recursive CTE in SQLite.

Splits a string into an array. PostgreSQL.

Expands an array into rows. PostgreSQL; use json_each in SQLite.

Builds an inline table without creating one. Works in PostgreSQL and SQLite.

A human-readable interval between timestamps. PostgreSQL.

The current date in UTC.

The current time in UTC.

The current date and time in UTC.

SQLite modifiers such as start of month, weekday 1 and +3 hours.

Returns a date, optionally adjusted by modifiers such as +7 days.

Returns a full timestamp, optionally adjusted.

Adds or subtracts an interval. PostgreSQL and MySQL.

The Julian day number. Subtract two values to get a difference in days.

Builds a date from parts. PostgreSQL.

The current transaction timestamp. PostgreSQL; SQLite uses current_timestamp.

Formats a date using codes such as %Y, %m, %d and %H.

Returns the time portion of a timestamp.

Formats a timestamp with a picture string. PostgreSQL.

Parses a date from a picture string. PostgreSQL.

Seconds since the Unix epoch.

Converts between types. Use it explicitly rather than relying on implicit casts.

Joins strings, treating NULL as an empty string. The || operator returns NULL if any side is NULL.

A named temporary result set that makes complex queries readable.

The current date or timestamp. now() includes time and time zone in PostgreSQL.

Truncates a timestamp to a unit, ideal for grouping by period.

Tests whether a subquery returns any row. Often clearer and faster than IN with nulls.

Reads a field from a date or timestamp. SQLite uses strftime().

Returns the largest or smallest of the arguments. PostgreSQL and SQLite differ in null handling.

Reads the previous or next row in a window, useful for deltas.

Character count of a string.

Changes case for comparisons and display.

Restarts a window calculation for each group.

Finds a substring position, returning 0 when absent. SQLite uses instr().

Ranks rows, leaving gaps after ties; dense_rank leaves no gaps.

Walks a hierarchy such as a management chain or category tree.

Replaces every occurrence of a substring.

Rounds a numeric value.

Assigns a sequential number to rows within a window.

A query nested inside another. Prefer JOINs when the relationship is simple.

Extracts part of a string. SQLite uses substr(); PostgreSQL also accepts substring(text from start for length).

A running total without collapsing rows, unlike GROUP BY.

Removes spaces from both ends. Use ltrim or rtrim for one side.

Every row of a paired with every row of b. Use it deliberately; it multiplies row counts.

Rows in the first result set that are not in the second.

Keeps unmatched rows from both sides. SQLite does not support FULL OUTER JOIN.

Keeps only rows with a match on both sides.

Rows present in both result sets.

Keeps every left row and fills missing right columns with NULL.

Keeps every right row. Prefer swapping the tables and using LEFT JOIN for readability.

Joins a table to itself, usually through two aliases.

Stacks result sets and removes duplicates. Column counts and compatible types must match.

Stacks result sets and keeps duplicates. Usually faster than UNION.

Reads a JSON value as text. PostgreSQL and SQLite.

Aggregates rows into a JSON array. PostgreSQL; SQLite uses json_group_array().

Builds a JSON array from values.

A table-valued function that expands a JSON array or object into rows.

Reads a value from a JSON document using a path.

Builds a JSON object from pairs.

Quotes a SQL value as JSON text.

Removes a value at a path.

Adds or replaces a value at a path.

Walks a JSON document into rows with key, value, type and path.

The type of a JSON value: object, array, text, integer, real, true, false or null.

Returns 1 when the text is well-formed JSON.

Validates and stores a JSON text value.

Binary JSON storage and update helpers. PostgreSQL only.

The absolute value.

Inverse trigonometric functions returning radians.

The angle of a vector, handling all quadrants.

Rounds up or down to an integer.

Converts between radians and degrees.

e raised to a power.

The natural logarithm. PostgreSQL and MySQL; SQLite builds without math functions may omit it.

A logarithm. PostgreSQL takes an explicit base; SQLite log(number) is base 10.

The base-10 logarithm.

The remainder of a division. SQLite uses the % operator.

Rounds to a number of decimal places.

The constant pi.

Raises a number to a power.

A pseudo-random integer. Use it for sampling, not security.

Random bytes, useful for generating ids in SQLite.

Returns -1, 0 or 1 depending on the sign.

Trigonometric functions that take radians.

The square root.

Parses a formatted number. PostgreSQL.

Truncates decimals without rounding. SQLite builds without math functions can use CAST.

Combines predicates. Use parentheses because AND binds tighter than OR.

An inclusive range test.

Returns different values based on conditions. It is an expression, so it can appear in SELECT, WHERE and ORDER BY.

Returns the first non-null argument.

Renames an output column. Quote aliases that contain spaces.

Compare values. Use IS NULL and IS NOT NULL for null checks, never = NULL.

Removes duplicate result rows. Use it deliberately because it costs sorting or hashing.

PostgreSQL keeps the first row per key according to the ORDER BY. SQLite does not support it.

Standard SQL pagination. PostgreSQL also supports LIMIT and OFFSET; SQLite only supports LIMIT.

Matches any value in a list. Faster to read than chained ORs.

Tests for the absence of a value. NULL never equals anything, including itself.

Pattern matching with % and _. LIKE is case-sensitive in PostgreSQL; ILIKE is not.

Returns a page of rows. Offset pagination gets slower and can skip rows under concurrent writes.

Returns null when the two values are equal. Useful for avoiding division by zero.

Sorts the result. Add a unique tie-breaker for stable pagination.

Chooses the columns returned by a query. Prefer explicit columns over select * in application code.

Returns every column. Convenient in exploration, risky in production because schema changes leak into results.

Filters rows before grouping. It cannot reference aggregate results; use HAVING for that.

Tests a POSIX regular expression. PostgreSQL.

Tests a regular expression. MySQL and Oracle; PostgreSQL uses ~.

Returns the first match of a regular expression. PostgreSQL.

Returns all matches as rows. PostgreSQL.

Replaces matches of a regular expression. PostgreSQL.

SQL standard pattern matching with more operators than LIKE. PostgreSQL; SQLite uses GLOB.

Parses a full-text query. PostgreSQL.

Builds a searchable text vector. PostgreSQL full-text search.

Ranks documents against a full-text query. PostgreSQL.

Parses a user-friendly search query. PostgreSQL.

Changes an existing table. Some databases require a table rewrite for certain changes.

Rejects rows that violate a domain rule.

Defines a table and its columns, types and constraints.

Indexes a column and enforces uniqueness at the same time.

Supplies a value when the insert omits the column.

Removes a table and its data. IF EXISTS makes it safe to rerun a script.

Enforces a relationship to another table and can cascade changes.

Speeds up matching reads at the cost of writes and storage. Index the columns you filter and join on.

Rejects rows that leave the column empty. Add it to every column you always require.

Uniquely identifies each row and implies NOT NULL.

Rejects duplicate values across rows. Nulls are allowed multiple times in most databases.

The code of the first character. SQLite uses unicode().

The number of characters. SQLite uses length().

The character for a code point. SQLite uses char().

Joins values, skipping NULLs. SQLite 3.44+ and PostgreSQL.

Builds a string with placeholders. SQLite 3.38+ and PostgreSQL.

Capitalises each word. PostgreSQL only.

Finds a substring and returns a one-based position, or 0.

Takes characters from one end. PostgreSQL and SQL Server; SQLite uses substr().

Escapes wildcard characters so they match literally.

Pads a string to a fixed width. PostgreSQL; use printf() in SQLite.

The MD5 hash of a string. PostgreSQL and MySQL; never use it for passwords.

The number of bytes in a string’s encoding. PostgreSQL.

Formats numbers and strings with printf-style placeholders. Very useful for padding in SQLite.

Escapes a value for safe inclusion in SQL. PostgreSQL.

Repeats a string. PostgreSQL and MySQL; not built into SQLite.

Reverses a string. PostgreSQL and MySQL; not built into SQLite.

Returns one part of a delimited string. PostgreSQL.

The one-based position of a substring, or 0. SQLite uses instr().

A negative start counts from the end of the string.

Formats an integer as hexadecimal. PostgreSQL.

Removes characters from both ends or one side.

The number of rows changed by the most recent statement. SQLite.

The current database user. PostgreSQL.

Returns the fallback when the first value is NULL. SQLite.

A compact conditional expression. SQLite.

The rowid generated by the most recent insert. SQLite.

Returns NULL when the value is zero. Useful for safe division.

Lists every function the SQLite build provides.

Returns a SQL literal for a value, with NULL rendered as NULL. SQLite.

The SQLite library version. SQLite only.

The storage class of a value: null, integer, real, text or blob. SQLite.

The server version string. PostgreSQL.

Shows the query plan and real timings. Use it before adding an index.

Controls how concurrent transactions see each other’s work. PostgreSQL defaults to READ COMMITTED.

Locks selected rows until the transaction ends, preventing lost updates. PostgreSQL and MySQL.

Reverses the current transaction, discarding its changes.

Marks a point inside a transaction so part of it can be rolled back.

Groups statements so they succeed or fail together. A rollback undoes the whole group.

The cumulative distribution of a row, from 0 to 1.

The first value in the window frame.

Uses a default when the previous row does not exist.

The last value in the current window frame. Widen the frame to see the group’s last row.

Looks ahead by n rows.

Names a window definition and reuses it across several columns.

The nth value in the window frame.

Splits rows into roughly equal buckets.

The relative rank of a row from 0 to 1.

Defines which rows an aggregate window sees. The default includes peers of the current row.

Removes matching rows. Prefer soft deletes when audit history or recovery matters.

Adds rows. Always list columns explicitly so schema changes do not break the statement.

Copies rows from another query into a table.

Returns generated values such as ids from the inserted rows. PostgreSQL and SQLite support it.

Standard SQL for conditional insert, update and delete. PostgreSQL 15+ supports it.

Removes every row quickly and resets identity in PostgreSQL. It cannot be easily rolled back selectively.

Changes matching rows. Always include a precise WHERE clause and check the affected count.

Updates rows using values from another table. PostgreSQL syntax; SQLite uses UPDATE … FROM as well.

Inserts or updates when a unique constraint is violated. PostgreSQL and SQLite support ON CONFLICT.