Every SQL keyword

% (wildcard) Inside LIKE, stand for any run of characters, including none at all.
+ - * / % Do arithmetic: add, subtract, multiply, divide and find a remainder.
-- comment Start a note that SQL ignores until the end of the line.
/* */ Wrap a note across several lines so SQL skips over it.
:name placeholder Mark a named spot in a query where a value is safely filled in later.
; End one SQL statement so the next one can begin.
< > <= >= Compare values to check which is smaller or larger.
= <> != Compare two values to check if they are equal or different.
? placeholder Mark a spot in a query where a value is safely filled in later.
_ (wildcard) Inside LIKE, stand for exactly one character of any kind.
ALTER TABLE ADD COLUMN Add a new column to a table that already exists.
ALTER TABLE RENAME Give an existing table or column a new name.
AND Require that both conditions are true for a row to be kept.
AS Give a column, calculation or table a new, clearer name.
ASC / DESC Sort in ascending order (the default) or descending order.
AVG() Work out the average of the values in a column.
BEGIN Start a transaction so several changes succeed or fail together.
BETWEEN Check whether a value falls inside a range, including both ends.
CASE Choose a value based on conditions, like an if-then inside a query.
CHECK Refuse any row whose values fail a condition you write.
COALESCE() Return the first value in a list that is not missing.
COMMIT Save all the changes made since the transaction began.
correlated subquery A subquery that refers to the current row of the outer query.
COUNT(*) Count how many rows there are, including rows with missing values.
COUNT(column) Count the rows where a column has a value, skipping NULLs.
COUNT(DISTINCT) Count how many different values appear in a column.
CREATE INDEX Build a lookup structure that makes searching a column faster.
CREATE TABLE Make a new table with named columns and their types.
CREATE UNIQUE INDEX Build an index that also refuses duplicate values.
CREATE VIEW Save a query under a name so you can select from it like a table.
date modifiers Add words like '+7 days' or 'start of month' to shift a date.
date() Get a date as YYYY-MM-DD text, optionally shifted by modifiers.
DEFAULT Fill in a value automatically when an insert leaves a column out.
DELETE FROM Remove rows from a table, usually limited with WHERE.
DENSE_RANK() Rank rows so ties share a number with no gaps after them.
DISTINCT Remove duplicate rows so each result appears only once.
DROP INDEX Delete an index you no longer need, leaving the table as it was.
DROP TABLE Delete a whole table along with all of its rows.
DROP VIEW Delete a saved view without touching the tables it reads from.
ELSE Give the value CASE uses when none of the conditions are true.
END Close a CASE expression so the query can carry on.
ESCAPE Pick a character that makes % or _ match literally inside LIKE.
EXISTS Check whether a subquery returns at least one row.
EXPLAIN QUERY PLAN Show how SQLite will run a query, such as whether it uses an index.
FOREIGN KEY Mark a column that points to the primary key of another table.
FROM Name the table a query reads its rows from.
FULL JOIN Keep every row from both tables, matched where possible.
GLOB Match text with case-sensitive * and ? wildcards in SQLite.
GROUP BY Collect rows that share a value so totals are worked out per group.
HAVING Keep only the groups that pass a condition, applied after grouping.
IF NOT EXISTS Skip creating something when it is already there, avoiding an error.
IFNULL() Swap a missing value for a fallback value you choose.
IIF() Pick one of two values depending on whether a condition is true.
IN Check whether a value matches any item in a list.
IN (subquery) Check whether a value appears in the results of another query.
INNER JOIN The full name for JOIN, which keeps only rows that match in both tables.
INSERT ... SELECT Add rows to a table using the results of a query.
INSERT INTO Add one or more new rows to a table you name.
INSTR() Find the position where some text first appears inside another text.
INTEGER PRIMARY KEY Make a column that SQLite fills with a new number for each row.
IS NOT NULL Check that a value is present rather than missing.
IS NULL Check whether a value is missing, since = NULL never matches.
JOIN Combine rows from two tables where a matching condition holds.
julianday() Turn a date into a day number so you can subtract two dates.
LAG() Read a value from the row before the current one.
LEAD() Read a value from the row after the current one.
LEFT JOIN Keep every row from the first table, even when nothing matches.
LENGTH() Count how many characters are in a piece of text.
LIKE Match text against a pattern that uses % and _ as wildcards.
LIMIT Return at most a given number of rows from a query.
LOWER() Turn every letter in a piece of text into a lowercase letter.
MAX() Find the largest value in a column, or in each group.
MIN() Find the smallest value in a column, or in each group.
NOT Flip a condition so true becomes false and false becomes true.
NOT BETWEEN Check that a value falls outside a range of values.
NOT EXISTS Check that a subquery returns no rows at all.
NOT IN Check that a value matches none of the items in a list.
NOT LIKE Keep rows whose text does not match a LIKE pattern.
NOT NULL Refuse any row that leaves this column empty.
NULL A special marker meaning a value is missing or unknown.
NULLS FIRST / NULLS LAST Choose whether missing values appear at the start or end of a sort.
OFFSET Skip a number of rows before returning results, often used for paging.
ON Give the condition that decides which rows of a join match up.
ON DELETE CASCADE Delete linked rows automatically when the row they point to is deleted.
ON DELETE RESTRICT Block deleting a row while other rows still point to it.
ON DELETE SET NULL Empty the linking column when the row it points to is deleted.
OR Keep a row when at least one of the conditions is true.
ORDER BY Sort the result rows by one or more columns.
OVER Turn a function into a window function that looks across related rows.
PARTITION BY Split rows into separate groups for a window function to work within.
PRAGMA Run a SQLite command that reports or changes database settings.
PRIMARY KEY Mark the column whose value uniquely identifies each row.
RANK() Rank rows so ties share a number and the next rank skips ahead.
REFERENCES Name the table and column that a foreign key points to.
RELEASE Keep the changes since a savepoint and forget that savepoint.
REPLACE() Swap every copy of one piece of text for another inside a value.
RETURNING Show the rows that an INSERT, UPDATE or DELETE just changed.
RIGHT JOIN Keep every row from the second table, even when nothing matches.
ROLLBACK Undo all the changes made since the transaction began.
ROLLBACK TO Undo changes back to a savepoint without ending the transaction.
ROUND() Round a number to a chosen number of decimal places.
ROW_NUMBER() Number the rows 1, 2, 3 in order, with no shared numbers.
rowid The hidden number SQLite gives every row in an ordinary table.
running SUM Use SUM with OVER and ORDER BY to keep a running total down the rows.
SAVEPOINT Mark a point inside a transaction that you can undo back to later.
scalar subquery A subquery that returns a single value you can use like a number.
SELECT Choose which columns or values a query returns.
SELECT * Return every column of a table, handy for a quick look at the data.
self-join Join a table to itself to compare rows within the same table.
SET Say which columns to change and what their new values are.
sqlite_master A built-in table that lists every table, index and view in the database.
strftime() Format a date or time as text using codes like %Y and %m.
STRICT Make SQLite refuse values that do not match a column's type.
subquery A query placed inside another query, written in brackets.
SUBSTR() Take part of a text value, starting at a chosen position.
SUM() Add up all the values in a column, skipping missing ones.
table aliases Give a table a short name so its columns are quicker to write.
TRIM() Remove spaces from the start and end of some text.
UNION ALL Stack the rows of two queries on top of each other, keeping duplicates.
UNIQUE Refuse any row that repeats a value already in this column.
UPDATE Change values in rows that already exist in a table.
UPPER() Turn every letter in a piece of text into a capital letter.
VALUES List the values for each new row in an INSERT statement.
WHEN ... THEN Give one condition inside CASE and the value to use when it is true.
WHERE Keep only the rows that pass a condition you write.
WITH Name a query step up front so later parts can use it like a table.
WITH RECURSIVE Build rows step by step, useful for walking trees and hierarchies.
|| Join two pieces of text together into one longer string.