Glossary

101 terms from SQL Beyond the Basics, in plain words, each with the chapter that teaches it.

Search the glossary

#

->> (JSON operator): A shorthand that reads the value at a path inside JSON text, like json_extract. For example, a café's city is picked out of a small JSON list with ->> 0.
|| (concatenation operator): Joins pieces of text into one, such as a name, a bracket and a city. If any piece is NULL the whole result is NULL, so wrap nullable columns in COALESCE.

A

ACID: The four promises a transaction makes. It is atomic (all or nothing), consistent (constraints hold), isolated (others don't see half-finished work) and durable (committed changes survive a crash).
Alias: A short name given to a table or column inside a query, such as `orders AS o`. Putting the alias before column names avoids 'ambiguous column name' errors when joined tables share a column name.
ALTER TABLE: A command that changes an existing table. In SQLite it can add or rename a column but can't add a constraint to an existing column.
Anchor: The starting query of a recursive CTE, which supplies the first row or rows. In the org chart it picks the founder, who has no manager.
Anomaly: A problem caused by storing the same fact in several places. Examples are two different emails for Bloom Café, or losing a customer's details when their only order is deleted.
Anti-join: A pattern that finds rows with no match in another table. It uses a LEFT JOIN and keeps only the rows where the right side is NULL, or uses NOT EXISTS.
Autocommit: SQLite's default behaviour: outside BEGIN … COMMIT, each statement is its own small transaction and is saved immediately.

B

BEGIN: Starts a transaction. Changes after it are visible to you but not permanent until COMMIT.

C

CASE expression: A value chosen by testing conditions from the top and using the result of the first true one. If none are true and there's no ELSE, the result is NULL.
CAST: Converts a value to another type. Step 4 uses it to turn text like '09' into the number 9 so months can be added and subtracted.
CHECK constraint: A rule that a condition must not be false for a row to be stored, such as `CHECK (price > 0)`. A NULL passes, so pair it with NOT NULL when a value is required.
COALESCE: Gives back the first of its values that isn't NULL. It lets you choose what NULL should mean, such as treating a never-asked email_opt_in as 0.
Cohort: A group of customers who started in the same period, such as everyone whose first order was in 2025-Q4. A cohort table shows what share of each group came back later.
COMMIT: Ends a transaction and makes all its changes permanent at once. In DB Browser, Write Changes does this.
Composite key: A key made of several columns together. order_items uses (order_id, product_id), so a product can't appear twice on one order but can appear on many orders.
Conditional aggregation: Putting a CASE inside an aggregate such as SUM or COUNT so it only counts the rows you choose. It is how you build pivots, like cafés and online customers side by side.
Constraint: A rule the database enforces on every INSERT and UPDATE, such as NOT NULL, UNIQUE, CHECK or a foreign key. It stops bad data getting in.
Correlated subquery: A subquery that refers to a column of the outer query, so it runs again for every outer row. For example, it counts each café's own orders.
CROSS JOIN: Pairs every row of one table with every row of another. Three months and 11 products give a 33-row grid you can LEFT JOIN sales onto.
CSV (comma-separated values): A plain text file format with one row per line and values separated by commas. It is widely used to move table data between databases, spreadsheets and other programs.
CTE (common table expression): A named step written after WITH before the main query, which the main query then uses like a table. It lets a long query read from top to bottom.

D

date(): A SQLite function that reads a date, applies any modifiers from left to right, and gives back a YYYY-MM-DD date, such as date('2026-09-30', '+1 day').
DB Browser for SQLite: A free graphical tool for creating SQLite databases and running SQL. Changes aren't saved until you click Write Changes.
DEFAULT: A constraint giving the value used when an INSERT leaves the column out, such as status 'placed'. An expression default goes in parentheses.
Derived table: A subquery in FROM, used as a temporary table. It lets you aggregate twice, for example totaling each order first and then averaging those totals.
Dot commands: Commands that belong to the sqlite3 shell, not SQL, so they need no semicolon. Examples are `.read` to run a file, `.mode box` to draw tables and `.once` to send one result to a file.

E

EXCEPT: A set operation giving the rows of the first result that aren't in the second. It finds mug buyers who never bought Ethiopia Guji.
excluded: Inside an upsert's DO UPDATE, the name for the row you tried to insert. It is used to add the new bags to the existing stock.
EXISTS: A test that is true if a subquery returns at least one row; NOT EXISTS is true when it returns none. NOT EXISTS is a safe way to write an anti-join.
EXPLAIN QUERY PLAN: Put in front of a query, it shows how SQLite plans to run it, such as SCAN (read the whole table) or SEARCH USING INDEX.

F

Fan-out: Rows repeated by joining to a table with several matches per row. Joining order_items makes Bloom Café's order appear once per line, so COUNT(*) overcounts orders.
Foreign key: A column pointing to another table's primary key, such as orders.customer_id. Declared with REFERENCES, it lets the database refuse rows that point to nothing.
Frame: The rows a window function looks at for the current row, set with ROWS BETWEEN … AND …. For example, 2 PRECEDING AND CURRENT ROW covers this row and the two before it.
FULL JOIN: Keeps unmatched rows from both tables, with NULLs where there's no partner. It is useful for comparing two lists.
Full table scan: Reading every row of a table to answer a query. It is fine for 2,781 orders but slow on millions.

I

iif: A SQLite function for a single yes-or-no test, such as `iif(price >= 50, 'big', 'small')`. PostgreSQL doesn't have it, so CASE is the portable choice.
Index: A separate sorted list of one or more columns with pointers back to the rows, like a book's index. It lets SQLite jump straight to matching rows instead of scanning the table.
INSERT … SELECT: An insert that takes its rows from a query. It is used to copy rows into a new table when SQLite can't alter a constraint.
instr: Gives the position where some text first appears inside another text, or 0 if it doesn't. Combined with substr it can pull the part of an email after the @.
Integer: A whole number. When both sides of / are integers the remainder is thrown away, so 100 * 94 / 2781 gives 3; start with 100.0 to get decimals.
INTERSECT: A set operation giving the rows that appear in both results. It finds customers who bought both a Speckled Blue Mug and Ethiopia Guji.
IS / IS NOT: SQLite comparisons that work like = and <> except that two NULLs count as equal, so the result is never unknown. The SQL standard spells it IS DISTINCT FROM.
ISO 8601: The international date format YYYY-MM-DD. Because the biggest unit comes first, text dates sort and compare in time order.

J

JSON: A common text format for structured data, made of named values and lists, such as {"roast": "light", "notes": ["jasmine"]}. SQLite reads it with built-in functions.
json_each: A function used in FROM like a table that turns a JSON list into one row per item, with the position in key and the item in value.
json_extract: A function that returns the value found at a path inside JSON text, such as '$.notes[1]'.
julianday: A SQLite function that turns a date into a day number, so julianday(a) - julianday(b) counts the days between two dates. If either date is NULL, so is the result.

L

LAG / LEAD: Window functions that give the value from the previous row (LAG) or next row (LEAD) in the window's order. LAG is NULL on the first row; it is used for month-over-month change.

M

Materialized view: A view that stores its result for speed and is refreshed on request. PostgreSQL has them; SQLite doesn't.
Modifier: A small instruction passed to date(), such as '+1 day', 'start of month' or 'weekday 1', applied from left to right.
Moving average: The average of a value and its neighbours, row by row. It smooths bumpy figures, such as Sam's three-month average of revenue.
Multi-column index: An index on several columns, such as (customer_id, order_date). It helps queries that filter on the first column, or the first and second, but not on the second alone.
MySQL: A popular free, open-source relational database. Its SQL differs from SQLite's in places, for example it joins text with `CONCAT()` instead of `||`, and it has no `FULL JOIN`.

N

Normal forms: The textbook rules (first, second, third and so on) that a well-normalized set of tables follows so each fact is stored only once. Roughly, they require one value per cell, one kind of thing per table, and every column describing only its table's key.
Normalization: Splitting data into tables so each fact is stored exactly once. That is why customer details live in customers and orders only store customer_id.
NOT IN trap: If the list in NOT IN (subquery) contains a NULL, the test is never true and the result is empty. Use NOT EXISTS, or filter NULLs out of the list.
NOT NULL: A constraint meaning a value must be given for the column.
NULL: Marks a missing or unknown value. It isn't zero or empty text, and any comparison or arithmetic involving it gives NULL.
NULLIF: Gives NULL when its two values are equal, and the first value otherwise. It turns unwanted values into NULL for aggregates to skip, and guards against dividing by zero with x / NULLIF(y, 0).
NULLS LAST: An ORDER BY option that chooses where NULLs sort, since SQLite and MySQL put them first and PostgreSQL puts them last by default.

O

ON DELETE CASCADE: Options after REFERENCES that say what happens when a referenced row is deleted. CASCADE deletes the dependent rows too, RESTRICT refuses the delete, and SET NULL empties the link.
Orphan: A row whose foreign key points to nothing, such as an order for a customer 9999 who doesn't exist. Foreign keys prevent them.
OVER: The clause that turns an aggregate or ranking function into a window function. Inside it you put PARTITION BY, ORDER BY and an optional frame.

P

Partition: One group of rows in a window, set by PARTITION BY. It works like GROUP BY except each row keeps its own row and sees only its group.
Path (JSON): Where to look inside JSON, such as '$.notes[1]'. Here $ is the whole value, .notes a name inside it, and [1] the second list item, since counting starts at 0.
Pivot: A summary with one row per group and one column per category, such as cafés and online side by side for each state. It is built with conditional aggregation.
PostgreSQL: A popular free database for applications and analysis. It uses most of the same ideas as SQLite but with different function names and stricter errors.
PRAGMA: A SQLite-specific command that changes a setting or reports on the database. `PRAGMA foreign_keys = ON;` makes SQLite check foreign keys.
Primary key: The column or columns that identify each row; no two rows share it and it can't be NULL. A column declared INTEGER PRIMARY KEY numbers itself in SQLite.
printf: A SQLite function that formats a number as text for display, such as printf('$%.2f', 9.5) giving $9.50. A %% in the format prints a real percent sign.

R

RANK / DENSE_RANK / ROW_NUMBER: Window functions that number rows in order and differ on ties. ROW_NUMBER gives 1, 2, 3, 4; RANK gives 1, 2, 2, 4; DENSE_RANK gives 1, 2, 2, 3.
Recursive CTE: A CTE that refers to itself to build rows step by step, with an anchor and a recursive step joined by UNION ALL. It generates calendars and walks hierarchies like the staff org chart.
Recursive step: The part of a recursive CTE that reads the previous rows and makes the next ones. It needs a WHERE that eventually produces no rows, or the query runs forever.
replace: Replaces every occurrence of some text with other text, such as removing '12 oz' from a product name.
RETURNING: A clause that makes an `INSERT`, `UPDATE` or `DELETE` give back the rows it changed. Because it is a SQL keyword, it is best avoided as a column name.
RIGHT JOIN: Keeps every row of the second table. Most people swap the tables and write a LEFT JOIN instead.
ROLLBACK: Throws away all changes made since BEGIN, as if they never happened. It is the safety net if Jo forgets a WHERE on an UPDATE.
Running total: A sum of all rows up to the current one, made with an ordered SUM(...) OVER. Each month's figure includes the months before it.

S

Scalar subquery: A subquery that returns exactly one row and one column, so it can be used anywhere a number can, such as `price > (SELECT AVG(price) …)`.
Self join: A table joined to itself using two aliases, as if it were two tables. In staff it shows each person next to their manager.
Set operation: UNION, UNION ALL, INTERSECT and EXCEPT combine the results of two whole queries, one above the other. Both queries need the same number of columns, and UNION removes duplicates while UNION ALL keeps them.
SQLite: A lightweight database that runs as a library and stores everything in a single file. Some features need a minimum version, such as 3.25 for window functions and 3.39 for `RIGHT` and `FULL` joins.
sqlite3 shell: SQLite's command-line program for running SQL and dot commands in a terminal.
strftime: A SQLite function that picks parts out of a date using % codes, such as strftime('%Y-%m', order_date) for the year and month. It is handy for monthly grouping.
STRICT table: A SQLite table, made by adding STRICT after CREATE TABLE's closing parenthesis, that enforces its column types (INTEGER, REAL, TEXT, BLOB, ANY). Without it SQLite treats types as a preference.
Subquery: A complete SELECT in parentheses inside another query. It can give one value, one column of values, or a whole table.
substr: Gives part of a text from a start position (counting from 1) for an optional length. A negative start counts from the right.
Surrogate key: A made-up ID number with no meaning, such as customer_id, used as a primary key. It is safe because names change and repeat, like the two customers called Wes Ford.

T

Three-valued logic: Because comparisons with NULL give NULL, a SQL condition can be true, false or unknown. WHERE keeps a row only when the condition is true.
Transaction: A group of statements that all take effect together or not at all, started with BEGIN and ended with COMMIT or ROLLBACK. Marking an order shipped and adding its delivery row is an example.
trim: Removes spaces from both ends of a text; ltrim and rtrim do just the left or right end.

U

UNIQUE: A constraint that no two rows may share the same value in the column or column set. NULLs are excepted.
Upsert: An INSERT … ON CONFLICT … DO UPDATE that inserts a row, or updates the existing one if the key is already taken. Jo's stock table uses it to add new bags to an existing total.
USING: A shorter way to join on a column with the same name in both tables, such as `JOIN order_items USING (order_id)`.
UTC: The world reference time. date('now') uses it, so late evening in Oregon it can already show tomorrow; add 'localtime' to get your local date.

V

View: A query saved in the database under a name, which you select from as if it were a table. It stores no data and always shows current results, like the order_summary view.

W

Window: The set of rows a window function works over, defined by OVER (…). With an empty OVER () it is every row of the result.
WINDOW clause: Names a window definition so several functions in one query can share it with `OVER w`.
Window function: A function computed over a set of related rows that keeps every row and adds the value alongside, unlike GROUP BY. It is made by adding OVER (…) and can't be used in WHERE.