SQL Reference
Every SQL statement, clause, operator and function, with data types, constraints, window functions and dialect differences in one place. Concise, accurate pages with live SQL examples that run in your browser.
Statements and Clauses
-
SELECTRetrieves rows and columns from tables. -
SELECT DISTINCTRemoves duplicate rows from results. -
FROMNames the tables a query reads from. -
WHEREFilters rows before grouping. -
GROUP BYGroups rows for aggregation. -
HAVINGFilters groups after aggregation. -
ORDER BYSorts the result. -
LIMIT and OFFSETLimits and pages results (and FETCH FIRST). -
ASColumn and table aliases. -
UNION and UNION ALLCombines query results. -
INTERSECTRows present in both results. -
EXCEPTRows in the first result but not the second. -
VALUESRow constructors and inline tables. -
WITH (CTEs)Common table expressions. -
WITH RECURSIVERecursive queries. -
INSERT INTOAdds rows. -
INSERT INTO ... SELECTAdds rows from a query. -
UPSERT (ON CONFLICT)Insert or update on conflict. -
UPDATEChanges rows. -
DELETERemoves rows. -
TRUNCATE TABLERemoves all rows quickly (and its SQLite equivalent). -
MERGECombines insert, update and delete (dialect support). -
RETURNINGReturns rows changed by DML. -
EXPLAINShows how a query will run.
Joins and Subqueries
-
INNER JOINMatches rows in both tables. -
LEFT JOINKeeps all rows from the left table. -
RIGHT JOINKeeps all rows from the right table. -
FULL OUTER JOINKeeps all rows from both tables. -
CROSS JOINProduces the Cartesian product. -
NATURAL JOIN and USINGJoins on same-named columns. -
self joinJoining a table to itself (concept page). -
LATERAL joinsCorrelated joins (dialect support). -
scalar subqueriesSubqueries that return one value. -
IN with a subqueryMembership tests against a query. -
EXISTSTests whether a subquery returns rows. -
ANY, SOME and ALLComparisons against subquery results. -
correlated subqueriesSubqueries that reference the outer row (concept page). -
derived tablesSubqueries in FROM. -
anti-joinsNOT EXISTS, NOT IN and LEFT JOIN IS NULL (concept page).
Operators and Predicates
-
comparison operators=, <>, <, >, <=, >=. -
AND, OR and NOTLogical operators and precedence. -
BETWEENRange tests. -
INList membership. -
LIKEPattern matching. -
GLOBCase-sensitive Unix-style patterns (SQLite). -
regular expression matchingREGEXP, ~ and RLIKE across dialects. -
IS NULL and IS NOT NULLTesting for NULL. -
IS DISTINCT FROMNULL-safe comparison. -
arithmetic operators+, -, *, /, %. -
|| and string concatenationJoining strings. -
bitwise operators&, |, ~, <<, >>. -
CASEConditional expressions. -
CASTType conversion. -
NULL and three-valued logicHow NULL behaves (concept page).
Aggregate Functions
-
COUNT()Counts rows or values. -
SUM()Adds values. -
AVG()Averages values. -
MIN()Smallest value. -
MAX()Largest value. -
GROUP_CONCAT() and STRING_AGG()Concatenates values per group. -
TOTAL()Sum that returns 0.0 for no rows (SQLite). -
FILTER clauseConditional aggregation. -
STDDEV, VARIANCE and MEDIANStatistical aggregates and portable alternatives. -
JSON aggregatesjson_group_array and json_group_object. -
GROUPING SETS, ROLLUP and CUBEMultiple grouping levels (dialect support).
Scalar Functions
-
COALESCE()First non-NULL value. -
NULLIF()NULL if two values are equal. -
IFNULL(), NVL() and ISNULL()Dialect variants of default values. -
IIF()Inline conditional. -
ABS()Absolute value. -
ROUND()Rounds a number. -
CEIL() and FLOOR()Rounds up and down. -
POWER() and SQRT()Powers and roots. -
MOD()Remainder. -
RANDOM()Random numbers. -
SIGN()Sign of a number. -
LENGTH()String length. -
UPPER() and LOWER()Case conversion. -
SUBSTR() and SUBSTRING()Extracts part of a string. -
TRIM(), LTRIM() and RTRIM()Strips characters. -
REPLACE()Replaces substrings. -
INSTR(), POSITION() and CHARINDEX()Finds a substring. -
LPAD() and RPAD()Pads strings (and SQLite alternatives). -
CONCAT() and CONCAT_WS()Concatenates strings. -
FORMAT() and printf()Formats values. -
TYPEOF()Type of a value (SQLite). -
HEX() and UNHEX()Hexadecimal conversion. -
QUOTE()Quotes a value as an SQL literal. -
MIN() and MAX() with several argumentsScalar min and max in SQLite.
Date and Time Functions
-
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMPThe current date and time. -
DATE()Extracts or builds a date. -
TIME()Extracts a time. -
DATETIME()Builds a date-time. -
STRFTIME()Formats dates. -
JULIANDAY()Julian day numbers and date differences. -
date modifiers+1 day, start of month and other modifiers. -
EXTRACT() and DATE_PART()Extracting date parts (dialects). -
DATEADD(), DATE_ADD() and INTERVALDate arithmetic across dialects. -
DATEDIFF()Date differences across dialects. -
date and time storageStoring dates in SQLite and elsewhere (concept page).
Window Functions
-
OVER()The window clause. -
PARTITION BYSplits rows into partitions. -
ROW_NUMBER()Numbers rows. -
RANK() and DENSE_RANK()Ranking with ties. -
NTILE()Divides rows into buckets. -
LAG() and LEAD()Looks at previous and next rows. -
FIRST_VALUE(), LAST_VALUE() and NTH_VALUE()Window frame values. -
PERCENT_RANK() and CUME_DIST()Distribution functions. -
aggregates as window functionsSUM() OVER and friends. -
window framesROWS, RANGE and GROUPS. -
named windowsThe WINDOW clause.
Data Definition
-
CREATE TABLEDefines a table. -
CREATE TABLE ASCreates a table from a query. -
ALTER TABLEChanges a table. -
DROP TABLERemoves a table. -
CREATE INDEXCreates an index. -
DROP INDEXRemoves an index. -
CREATE VIEWCreates a view. -
CREATE TRIGGERCreates a trigger. -
temporary tablesTEMP and TEMPORARY tables. -
CREATE SCHEMA and databasesSchemas and databases across dialects. -
sequences and auto-incrementAUTOINCREMENT, IDENTITY and SEQUENCE. -
generated columnsComputed columns. -
RENAMERenaming tables and columns. -
IF EXISTS and IF NOT EXISTSSafe DDL. -
comments in SQL-- and /* */. -
ATTACH DATABASEUsing multiple databases (SQLite). -
PRAGMASQLite configuration statements. -
VACUUM and ANALYZEMaintenance statements.
Constraints and Data Types
-
PRIMARY KEYUnique row identifiers. -
FOREIGN KEYReferential integrity. -
UNIQUEUniqueness. -
NOT NULLDisallowing NULL. -
CHECKCustom conditions. -
DEFAULTDefault values. -
ON DELETE and ON UPDATE actionsCASCADE, SET NULL, RESTRICT. -
DEFERRABLE constraintsDeferred checking. -
integer typesINTEGER, BIGINT, SMALLINT. -
DECIMAL and NUMERICExact numbers. -
FLOAT, REAL and DOUBLEApproximate numbers. -
CHAR, VARCHAR and TEXTText types. -
BOOLEANBoolean values. -
DATE, TIME and TIMESTAMPTemporal types. -
BLOB and binary typesBinary data. -
JSON typesJSON storage and functions. -
SQLite type affinityHow SQLite treats types (concept page). -
UUIDsStoring identifiers.
Transactions and Performance
-
BEGIN, COMMIT and ROLLBACKTransaction control. -
SAVEPOINTPartial rollbacks. -
ACIDThe transaction guarantees (concept page). -
isolation levelsRead phenomena and levels (concept page). -
locking and deadlocksConcurrency control (concept page). -
index typesB-tree, hash, partial and covering indexes (concept page). -
EXPLAIN QUERY PLANReading query plans. -
sargable predicatesWriting index-friendly conditions. -
the N+1 query problemAvoiding query loops. -
pagination strategiesOFFSET vs keyset (concept page). -
query optimizer and statisticsHow the optimizer chooses (concept page).
JSON and Special Features
-
json_extract() and ->Reads JSON values. -
json_object() and json_array()Builds JSON. -
json_each() and json_tree()Expands JSON into rows. -
json_set(), json_insert() and json_remove()Modifies JSON. -
full-text search (FTS5)Full-text search tables in SQLite. -
materialized viewsDialect support and alternatives. -
trigger patternsAudit logs and derived data (concept page). -
stored procedures and functionsDialect support (concept page). -
recursive query patternsTrees, graphs and sequences (concept page). -
pivoting dataPIVOT and conditional aggregation (concept page).
Dialects and Concepts
-
SQL dialect differencesSQLite, PostgreSQL, MySQL, SQL Server and Oracle compared (concept page). -
LIMIT, TOP and FETCHRow limiting across dialects. -
identifier and string quotingQuotes across dialects. -
auto-increment across dialectsKey generation in each dialect. -
booleans across dialectsBoolean handling in each dialect. -
string functions across dialectsFunction name map. -
date functions across dialectsFunction name map. -
the SQL standardSQL:1992 to SQL:2023 (concept page). -
logical order of executionHow a query is evaluated (concept page). -
reserved keywordsKeyword reference (concept page). -
normalization1NF to BCNF (concept page). -
the relational modelTables, keys and relations (concept page). -
SQL injectionThe attack and the defense (concept page). -
SQL style guideFormatting and naming (concept page). -
the sample databasesThe bookstore, shop, company and school databases (concept page).
Nothing matches โโ. Try the site search.