SQL reference: keywords, clauses and functions
Every SQL keyword and function you will use, explained in plain English, each with an example query and its real result. Differences between SQLite, PostgreSQL and MySQL are noted where they matter.
Querying
SELECTChoose which columns (or calculations) a query returns.FROMSay which table (or tables, or subquery) the rows come from.AS (aliases)Give a column or a table a new name for this query.DISTINCTRemove duplicate rows from the result.ORDER BYSort the result.LIMIT and OFFSETReturn only the first n rows (after skipping m).
Filtering
Numbers and NULL
Text functions
UPPER and LOWERChange text to capitals or small letters.LENGTHReturn the number of characters in a text value.SUBSTRTake part of a text value.INSTRReturn the position of some text inside another, or 0.TRIMRemove spaces (or given characters) from the ends of text.REPLACEReplace every occurrence of some text.|| and CONCATJoin text values together.
Dates
date() and strftime()Work with dates in SQLite.julianday()Count the days between two dates in SQLite.
Aggregating
COUNTCount rows, non-NULL values, or different values.SUMAdd up values.AVGReturn the average (mean) of values.MIN and MAXReturn the smallest and largest value.GROUP BYSplit rows into groups and aggregate each group.HAVINGFilter groups after aggregation.GROUP_CONCATJoin the values of a group into one text value.
Joins
INNER JOINCombine rows of two tables that match on a condition.LEFT JOINKeep every row of the left table, matched or not.Self joinJoin a table to itself, with two aliases.CROSS JOINPair every row of one table with every row of another.
Subqueries, CTEs and sets
SubqueryA query inside another query.EXISTSTrue if a subquery returns at least one row.UNION and UNION ALLStack the results of two queries.INTERSECT and EXCEPTRows in both results / rows in the first only.WITH (CTE)Name the steps of a query (common table expressions).WITH RECURSIVEA CTE that refers to itself, to generate series or walk hierarchies.
Window functions
ROW_NUMBER, RANK, DENSE_RANKNumber or rank rows within groups.OVER and PARTITION BYTurn an aggregate into a window function that keeps every row.Running totalsAdd up values row by row, in order.LAG and LEADGet the value from a previous or following row.NTILESplit ordered rows into n roughly equal groups (quartiles, deciles).
Tables and design
CREATE TABLECreate a new table.ConstraintsRules the database enforces on every change.FOREIGN KEYMake a column refer to a row in another table.ALTER TABLEChange an existing table.DROP TABLEDelete a table and all its data.CREATE INDEXMake look-ups on some columns fast.EXPLAIN QUERY PLANShow how the database will run a query.CREATE VIEWSave a query under a name and use it like a table.
Changing data
INSERTAdd rows to a table.UPDATEChange values in existing rows.DELETERemove rows.INSERT … ON CONFLICT (upsert)Insert a row, or update it if the key already exists.BEGIN, COMMIT, ROLLBACKGroup changes so they all happen, or none do.