All lessons

SQL glossary

156 words from the course, in plain English. "Also called" lists the other names you will meet in SQL Server, PostgreSQL, Power BI and job ads. Each term links to the lesson that explains it.

ABCDEFGHIJKLMNOPQRSTUWY

A

ACID
The four promises a transaction makes: atomic (all or nothing), consistent, isolated from other users, and durable once committed. Lesson 9.5
Additive measure
Also called: additive metric. A number you can add up across groups and get the right total, such as revenue. Sales per store add up to sales for the chain. Lesson 3.4
Aggregate first
Also called: pre-aggregate. Summarise each table to the grain you need before joining, so a one-to-many join cannot copy rows and inflate totals. Lesson 4.5
Aggregate function
Also called: aggregate, summary function. A function that turns many values into one, such as COUNT, SUM, AVG, MIN or MAX. In Power BI these are the default summaries of a column. Lesson 3.1
Alias
Also called: AS name, correlation name. A short or friendlier name given to a column or table with AS, such as SUM(amount) AS total or orders o. Lesson 2.1
Anchor
Also called: anchor member, base case. The first part of a recursive CTE: the starting rows, before UNION ALL, that the recursive step builds on. Lesson 5.5
AND, OR, NOT
Also called: Boolean operators, logical operators. Words that combine conditions. AND needs both to be true, OR needs at least one, and NOT flips a condition. Lesson 2.3
Anti-join
Also called: NOT EXISTS join, left anti join. Keeping the rows of one table that have no match in another, such as customers who never ordered. Written with NOT EXISTS or LEFT JOIN ... WHERE key IS NULL. Lesson 4.4
Average order value
Also called: AOV. Revenue divided by the number of orders. Get the grain right first, or you divide by order lines instead of orders. Lesson 3.5
AVG
Also called: mean, Average (Power BI). The aggregate that adds up the non-NULL values and divides by how many there are. NULLs are skipped, not counted as zero. Lesson 3.1

B

B-tree
Also called: balanced tree. The sorted tree structure most indexes use, so the database can jump to a value in a few steps instead of reading every row. Lesson 9.2
Bag
Also called: multiset. A collection where repeats count, unlike a set. SQL results are bags unless you use DISTINCT or UNION. Lesson 2.1
BETWEEN
Tests whether a value lies in a range, ends included: price BETWEEN 10 AND 20 means 10 ≤ price ≤ 20. Lesson 2.3

C

Calendar table
Also called: date table, date dimension. A table with one row per day (or month), used so days with no sales still appear. Power BI calls it a date table. Lesson 8.1
Cardinality
Also called: distinct count. How many different values a column holds, or how many rows match on each side of a join (one-to-one, one-to-many, many-to-many). Lesson 3.4
Cartesian product
Also called: cross product, CROSS JOIN. Every row of one table paired with every row of another. A join without a proper condition gives this, and the row count explodes. Lesson 4.2
CASE expression
Also called: CASE WHEN, IF (Excel), IIF (SQL Server). If-then-else inside a query: CASE WHEN condition THEN value ... ELSE value END gives one value per row. Lesson 2.6
CAST
Also called: CONVERT (SQL Server). Changes a value from one type to another, such as CAST('42' AS INTEGER) or CAST(amount AS REAL). Lesson 7.3
COALESCE
Also called: IFNULL, ISNULL (SQL Server), NVL (Oracle). Returns the first value in its list that is not NULL, such as COALESCE(email, '(no email)'). Lesson 2.4
Cohort
A group of customers who started in the same period, such as everyone whose first order was in March 2024. Lesson 8.2
Collation
Also called: sort order. The rules a database uses to compare and sort text, including whether upper and lower case count as the same. Lesson 2.2
Column
Also called: field, attribute. One named property that every row of a table has, such as order_date. Every value in it has the same meaning and type. Lesson 1.3
COMMIT
Ends a transaction and makes its changes permanent. Lesson 9.5
Composite index
Also called: multi-column index. An index on more than one column, sorted by the first column, then the second, and so on. Lesson 9.2
Composite key
Also called: compound key. A key made of two or more columns together, such as (order_id, line_no) in an order lines table. Lesson 4.1
Concatenation
Also called: ||, CONCAT, + (SQL Server). Joining pieces of text end to end. In SQLite and PostgreSQL write first_name || ' ' || last_name. Lesson 7.2
Conditional aggregation
Also called: SUM(CASE WHEN ...), CALCULATE (Power BI). Putting a CASE inside an aggregate so it only counts or adds the rows you want, such as SUM(CASE WHEN channel = 'online' THEN 1 ELSE 0 END). Lesson 2.6
Correlated subquery
Also called: dependent subquery. A subquery that uses a column from the outer query, so it is worked out again for each outer row. Lesson 5.3
COUNT
Also called: Count (Power BI). COUNT(*) counts rows; COUNT(column) counts the non-NULL values in that column. Lesson 3.1
COUNT DISTINCT
Also called: distinct count, DISTINCTCOUNT (Power BI). Counts how many different values a column has, each one once, such as the number of different customers who ordered. Lesson 3.4
Covering index
Also called: index-only scan (PostgreSQL). An index that holds every column a query needs, so the database answers from the index without visiting the table. Lesson 9.2
CROSS JOIN
Also called: Cartesian join. A join with no condition that pairs every row on the left with every row on the right. Handy with a one-row CTE of totals. Lesson 5.4
CTE
Also called: common table expression, WITH clause. A named, temporary result written with WITH name AS (...) at the top of a query, so a long query reads as steps. Lesson 5.4
CUME_DIST
Also called: cumulative distribution. A window function giving the share of rows with a value less than or equal to the current one, from just above 0 to 1. Lesson 6.7

D

Data quality check
Also called: data test, assertion. A query that should return no rows (or a known number) if the data is healthy, such as orders with no matching customer. Lesson 7.5
Date spine
Also called: date series, calendar CTE. An unbroken list of dates, often made with a recursive CTE, that you left join your data onto so empty days show as zero. Lesson 5.5
Declarative
Describing the result you want rather than the steps to get it. SQL is declarative: the database works out how to run it. Lesson 1.1
DELETE
Removes the rows that match a WHERE condition. Without WHERE it removes every row, so run the matching SELECT first. Lesson 9.5
DENSE_RANK
A ranking window function where tied rows share a rank and the next rank follows on with no gap: 1, 1, 2. Lesson 6.2
Derived table
Also called: inline view, subquery in FROM. A subquery in the FROM clause that is used like a table, and must be given an alias. Lesson 5.1
Dialect
Also called: flavour of SQL. One database's version of SQL: the standard plus its own extras and spellings. T-SQL and PostgreSQL are dialects. Lesson 9.3
DISTINCT
Also called: SELECT DISTINCT, remove duplicates. Removes repeated rows from a result, so each combination of the chosen columns appears once. Lesson 2.1
DML
Also called: data manipulation language. The statements that change rows: INSERT, UPDATE and DELETE (and MERGE in some databases). Lesson 9.5

E

Exact duplicate
Also called: duplicate row. A row that matches another in every column, often from loading the same file twice. Lesson 7.4
EXCEPT
Also called: MINUS (Oracle). Returns the rows of the first query that are not in the second, with duplicates removed. Lesson 4.7
EXISTS
Also called: semi-join. True when a subquery returns at least one row. It stops looking at the first match, and NULLs cannot trip it up. Lesson 5.2
EXPLAIN QUERY PLAN
Also called: EXPLAIN, execution plan, showplan (SQL Server). Asks the database how it will run a query, without running it: which tables it scans and which indexes it uses. Lesson 9.1
Expression index
Also called: functional index, index on expression. An index on a calculated value, such as LOWER(email), so searches on that expression can use it. Lesson 9.2

F

Fan-out
Also called: row explosion, join duplication. When a one-to-many join repeats each row on the "one" side once per match, so sums and counts come out too big. Lesson 4.5
FIRST_VALUE
Also called: LAST_VALUE. A window function that returns the value from the first row of the window, such as each customer's earliest email. Lesson 7.4
Floating point
Also called: REAL, FLOAT, double. The way computers store decimals in binary, which is not exact: 0.1 + 0.2 is 0.30000000000000004. Round money before comparing. Lesson 7.3
Foreign key
Also called: FK, lookup column. A column that holds a key from another table, such as orders.customer_id pointing at customers. In Power BI it is the many side of a relationship. Lesson 4.1
Full join
Also called: FULL OUTER JOIN. Keeps every row from both tables, matched where possible and filled with NULLs where not. Lesson 4.3
Full table scan
Also called: SCAN, table scan. Reading every row of a table from start to end, because no index helps. Fine for small tables, slow for huge ones. Lesson 9.1
Funnel
Also called: conversion funnel. A series of steps people go through, such as first order, second order, fifth order, with the count left at each step. Lesson 8.3

G

Grain
Also called: granularity, level of detail. What one row of a table stands for: one order, one product in an order, one customer per month. Get it wrong and every total is wrong. Lesson 3.5
GROUP BY
Also called: group, summarise, Group By (Power Query). Puts rows with the same values in the chosen columns into groups and returns one row per group, usually with aggregates. Lesson 3.2

H

HAVING
Also called: group filter. Filters groups after GROUP BY, using aggregates, such as HAVING COUNT(*) >= 5. WHERE filters rows before grouping. Lesson 3.3

I

IN
Tests whether a value is in a list, such as city IN ('Toronto', 'Ottawa'). Shorter than a row of ORs. Lesson 2.3
IN subquery
Also called: membership test. IN with a query instead of a list, such as customer_id IN (SELECT customer_id FROM returns). Lesson 5.2
Index
A sorted copy of one or more columns with pointers back to the rows, so the database can find matches without reading the whole table. Lesson 9.2
Inner join
Also called: JOIN, Merge (Power Query, inner). Joins two tables and keeps only the pairs of rows that match on the join condition. Rows with no match disappear. Lesson 4.2
INSTR
Also called: CHARINDEX (SQL Server), STRPOS / POSITION (PostgreSQL). Finds where one piece of text starts inside another, such as the position of @ in an email. Gives 0 if it is not there. Lesson 7.2
Integer division
Also called: whole-number division. Dividing one whole number by another drops the fraction: 7 / 2 gives 3 in SQLite, SQL Server and PostgreSQL. Multiply by 1.0 first to keep decimals. Lesson 1.3
INTERSECT
Returns the rows that appear in both queries, with duplicates removed. Lesson 4.7
IS NULL
Also called: IS NOT NULL. The only correct way to test for a blank. A comparison such as x = NULL is never true. Lesson 2.4
Island
Also called: streak, run. An unbroken run of rows in a sequence, such as consecutive days with at least one order. Lesson 8.4
ISO date
Also called: YYYY-MM-DD, ISO 8601. A date written year-month-day, such as 2025-03-07. Stored as text this way, dates sort and compare correctly. Lesson 7.1

J

Join condition
Also called: ON clause, join key. The test in ON that says which rows go together, usually a key match such as o.customer_id = c.customer_id. Lesson 4.2
julianday
Also called: DATEDIFF (SQL Server), date subtraction (PostgreSQL). A SQLite function that turns a date into a day number, so julianday(b) − julianday(a) gives the days between. Lesson 7.1

K

Key
Also called: identifier, ID column. A column, or set of columns, whose value names each row uniquely, such as order_id. Lesson 4.1

L

LAG
Also called: previous row. A window function that looks back a number of rows in the window, such as last month's revenue next to this month's. Lesson 6.4
LEAD
Also called: next row. Like LAG but looks forward, such as the date of a customer's next order. Lesson 6.4
Left censoring
When data starts partway through people's history, so the earliest order you see may not be their real first one. Lesson 8.2
Left join
Also called: LEFT OUTER JOIN, Merge (Power Query, left outer). Keeps every row of the left table and fills the right table's columns with NULL where there is no match. Lesson 4.3
Leftmost prefix rule
A composite index only helps queries that use its first column (or first few). An index on (store_id, order_date) cannot help a filter on order_date alone. Lesson 9.2
LIKE
Also called: ILIKE (PostgreSQL, ignores case). Matches text against a pattern with wildcards: % for any run of characters and _ for exactly one. Lesson 2.3
LIMIT
Also called: TOP (SQL Server), FETCH FIRST. Keeps only the first so many rows of the result, usually after ORDER BY. Lesson 2.5
Logical order of execution
Also called: query order of operations. The order SQL works through a query: FROM and joins, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT. Lesson 1.4
Long table
Also called: tall table, tidy data. A table with one row per thing and measure, such as one row per department per year. Easy to filter and chart. Lesson 8.6

M

Median
Also called: middle value, 50th percentile. The middle value once sorted: half are below, half above. Unlike the average, a few huge values do not drag it. Lesson 6.7
Metric definition
An exact written rule for a number, such as "revenue = completed orders only, after discounts, before returns". Lesson 10.2
Month-over-month growth
Also called: MoM growth. The change from last month as a share of last month: (this − last) ÷ last. Lesson 8.1
Moving average
Also called: rolling average, running average. The average of the last few rows (for example 7 days) at each row, which smooths out day-to-day noise. Lesson 6.6

N

Natural key
Also called: business key. A key that already means something in the real world, such as an email address or a product barcode. Lesson 4.1
Non-additive measure
Also called: non-additive metric. A number that does not add up across groups, such as distinct customers or an average. Recalculate it for the total. Lesson 3.4
NOT IN trap
If the list or subquery after NOT IN holds a NULL, NOT IN returns no rows at all. Use NOT EXISTS instead. Lesson 4.4
NTILE
Also called: buckets, quantile groups. A window function that splits ordered rows into a given number of buckets of near-equal size, such as NTILE(4) for quartiles. Lesson 6.7
NULL
Also called: blank, missing value. A marker meaning "no value here": unknown or not applicable. It is not zero and not an empty string. Lesson 2.4
NULLIF
Returns NULL when two values are equal, otherwise the first. NULLIF(x, 0) stops a divide-by-zero. Lesson 2.4

O

OFFSET
Also called: skip. Skips a number of rows before LIMIT starts counting, used for paging through results. Lesson 2.5
ORDER BY
Also called: sort. Sorts the result by one or more columns or expressions, ASC (smallest first, the default) or DESC. Without it, row order is not guaranteed. Lesson 2.5
Orphan
Also called: orphan row, unmatched row. A row whose foreign key points at a row that does not exist, such as an order for a missing customer. Lesson 4.1
OVER
Also called: window clause. The clause that turns a function into a window function and says which rows it sees, with PARTITION BY, ORDER BY and a frame. Lesson 6.1

P

PARTITION BY
Also called: window partition. Splits the rows for a window function into groups, like GROUP BY, but keeps every row. Lesson 6.1
PERCENT_RANK
A window function giving each row's rank as a share from 0 (first) to 1 (last). Lesson 6.7
Pivot
Also called: crosstab, PIVOT (SQL Server), matrix (Power BI). Turning rows into columns, such as one column per year, usually with SUM(CASE WHEN ...). Lesson 8.6
Precedence
Also called: operator precedence. Which operator is worked out first. AND comes before OR, so use brackets whenever you mix them. Lesson 2.3
Primary key
Also called: PK. The key a table declares for itself: unique and never NULL, such as customer_id in customers. In Power BI it is the one side of a relationship. Lesson 4.1

Q

QUALIFY
A clause in BigQuery and Snowflake that filters on a window function directly. SQLite, SQL Server and PostgreSQL need a CTE instead. Lesson 6.3
Query plan
Also called: execution plan. The steps the database chooses to run a query: which tables to scan, which indexes to use, where to sort. Lesson 9.1
Query planner
Also called: optimiser, query optimizer. The part of the database that turns your SQL into a plan and picks the fastest way it can find. Lesson 9.1

R

RANGE
Also called: RANGE frame. A window frame measured by value: rows with the same ORDER BY value are treated together. It is the default frame when there is an ORDER BY. Lesson 6.5
RANK
A ranking window function where tied rows share a rank and the next rank skips: 1, 1, 3. Lesson 6.2
Recursive CTE
Also called: WITH RECURSIVE. A CTE that refers to itself: an anchor gives the first rows and a step keeps adding rows until it adds none. Builds calendars and org charts. Lesson 5.5
Referential integrity
The rule that every foreign key value matches a real row in the table it points at, so there are no orphans. Lesson 7.5
Retention
Also called: repeat rate. The share of a cohort that comes back and orders again in a later period. Lesson 8.2
Retention table
Also called: cohort table, retention triangle. A grid with one row per cohort and one column per month number, showing how many customers were still active. Lesson 8.2
RFM
Also called: recency, frequency, monetary. Scoring customers on how recently, how often and how much they buy, then grouping them into segments. Lesson 8.5
Right join
Also called: RIGHT OUTER JOIN. The mirror image of a left join: keeps every row of the right table. Usually rewritten as a left join with the tables swapped. Lesson 4.3
ROLLBACK
Ends a transaction and throws away every change made since BEGIN. Lesson 9.5
Row
Also called: record, tuple. One entry in a table, with a value (or NULL) for every column. Lesson 1.3
ROW_NUMBER
Also called: RANKX (Power BI, roughly). A window function that numbers rows 1, 2, 3 within each partition, with no ties. Add a tie-breaker so the order is fixed. Lesson 6.2
ROWS
Also called: ROWS frame. A window frame counted in physical rows, such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for the last seven rows. Lesson 6.5
Running total
Also called: cumulative sum, RUNNINGSUM. A total that adds each row to everything before it, written SUM(x) OVER (ORDER BY date). Lesson 6.5

S

Sargable
Also called: search-argument-able. A condition written so an index can be used, such as order_date >= '2025-01-01'. Wrapping the column in a function usually stops that. Lesson 9.1
Scalar subquery
Also called: single-value subquery. A subquery that returns exactly one value, so it can sit anywhere a number could, such as WHERE amount > (SELECT AVG(amount) ...). Lesson 5.1
Seasonality
A pattern that repeats each year (or week), such as December peaks. Year-over-year comparisons cancel it out. Lesson 8.1
SELECT
The clause that says which columns and expressions to return. Every query has one. Lesson 2.1
Self-join
Joining a table to itself under two aliases, such as employees as staff and employees as managers. Lesson 4.6
Semi-join
Also called: EXISTS join, left semi join. Keeping the rows of one table that have at least one match in another, without copying them. Written with EXISTS or IN. Lesson 4.4
Set operation
Stacking or comparing whole query results: UNION, UNION ALL, INTERSECT and EXCEPT. Lesson 4.7
Share of total
Also called: percent of total, % of grand total (Power BI). Each row's value divided by the total, often written x / SUM(x) OVER (). Lesson 6.7
snake_case
Writing names in lower case with underscores, such as order_date. The usual style for SQL tables and columns. Lesson 9.4
SQL
Also called: Structured Query Language, "sequel". The language for asking questions of, and changing, data in tables. Nearly every database and BI tool speaks it. Lesson 1.1
SQLite
A small database kept in one file and built into phones and browsers. This course runs on it. Lesson 1.1
Step conversion
Also called: step-to-step conversion. The share of people at one funnel step who reach the next. Lesson 8.3
strftime
Also called: FORMAT / DATEPART (SQL Server), to_char / extract (PostgreSQL). A SQLite function that pulls parts out of a date or formats it, such as strftime('%Y-%m', order_date) for the month. Lesson 7.1
Style guide
A team's agreed way of writing SQL: capitals for keywords, one clause per line, clear aliases, comments for the why. Lesson 9.4
Subquery
Also called: nested query, inner query. A query inside another query, in brackets. It can give one value, a list, or a whole table. Lesson 5.1
SUBSTR
Also called: SUBSTRING, LEFT / RIGHT. Takes part of a text value by position and length, such as SUBSTR(first_name, 1, 1) for the first letter. Lesson 7.2
Surrogate key
Also called: synthetic key, ID, IDENTITY. A made-up number that names each row, with no meaning of its own, such as customer_id 1, 2, 3. Lesson 4.1

T

T-SQL
Also called: Transact-SQL. Microsoft SQL Server's dialect of SQL, also used in Azure SQL and Fabric. It writes TOP instead of LIMIT, for example. Lesson 9.3
Three-valued logic
In SQL a condition can be true, false or unknown. Anything compared with NULL is unknown, and WHERE keeps only true rows. Lesson 2.4
Tie-breaker
An extra ORDER BY column, often the key, that fixes the order of rows with equal values so results do not change between runs. Lesson 2.5
Time-boxed funnel
A funnel that only counts a step if it happens within a set time, such as a second order within 90 days of the first. Lesson 8.3
Top N per group
Also called: top-k per group. Keeping the best few rows in each group, such as the top two products per department: ROW_NUMBER or RANK in a CTE, then filter. Lesson 6.3
Transaction
Also called: BEGIN ... COMMIT. A group of changes that happen all together or not at all. Start with BEGIN, end with COMMIT or ROLLBACK. Lesson 9.5
TRIM
Also called: LTRIM, RTRIM. Removes spaces from the start and end of text. Stray spaces make 'Winnipeg ' and 'Winnipeg' different groups. Lesson 7.2
Truncate (dates)
Also called: DATETRUNC (SQL Server), date_trunc (PostgreSQL). Rounding a date down to the start of its month, week or year, such as date(order_date, 'start of month') in SQLite. Lesson 7.1
TRY_CAST
Also called: TRY_CONVERT, SAFE_CAST (BigQuery). A CAST that gives NULL instead of an error when a value cannot be converted. SQL Server has it; SQLite never errors on CAST. Lesson 7.3

U

UNION
Also called: Append (Power Query). Stacks the results of two queries and removes duplicate rows. Lesson 4.7
UNION ALL
Also called: Append (Power Query). Stacks the results of two queries and keeps every row, duplicates included. Faster than UNION and usually what you want. Lesson 4.7
Union-compatible
Two queries that can be stacked: the same number of columns, in the same order, with matching types. Lesson 4.7
Unpivot
Also called: melt, Unpivot Columns (Power Query). Turning columns back into rows, such as one column per year into a year column and a value column. Lesson 8.6
UPDATE
Changes values in the rows that match a WHERE condition. Without WHERE it changes every row. Lesson 9.5
Upsert
Also called: MERGE, INSERT ... ON CONFLICT. Insert a row, or update it if its key already exists, in one statement. Lesson 9.5
USING
A short form of a join condition when both tables name the column the same: JOIN customers USING (customer_id). Lesson 4.2

W

WHERE
Also called: filter. The clause that keeps only the rows whose condition is true, before any grouping. Lesson 2.2
Wide table
Also called: crosstab, matrix. A table with one column per category or period, such as rev_2024 and rev_2025. Easy to read, harder to filter. Lesson 8.6
Window
Also called: window of rows. The set of rows a window function looks at for the current row, set by OVER. Lesson 6.1
Window frame
Also called: frame clause. Which rows of the partition the function uses, such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Lesson 6.5
Window function
Also called: analytic function. A function that works over a set of related rows but keeps every row, such as SUM(x) OVER (PARTITION BY store_id). Lesson 6.1

Y

Year-over-year growth
Also called: YoY growth, SAMEPERIODLASTYEAR (Power BI). The change from the same period a year earlier, as a share of it. It cancels out seasonality. Lesson 8.1
Year-to-date
Also called: YTD, TOTALYTD (Power BI). A running total that restarts each year: SUM(x) OVER (PARTITION BY year ORDER BY month). Lesson 6.5

See also the SQL cheat sheet and the SQL sandbox.