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.
No term matches. Try a shorter word.
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
- 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
- Index search
- Also called: SEARCH, index seek (SQL Server). Using an index to jump straight to the rows that match, shown as SEARCH in a SQLite plan. Lesson 9.1
- 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
- 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