Skip to content

Chapter 2: The Relational Model & SQL

SQL is the one database skill every data job asks for. This chapter starts with the theory underneath — relations, tuples, domains and relational algebra — then teaches SQL hands-on: its sublanguages, SELECT, joins, aggregation, subqueries and recursive CTEs, window functions, NULL and three-valued logic, constraints, views and server-side code, and JSON. It closes with the SQL:2023 standard, how the main dialects differ, and the classic interview problems you should be able to solve.

2.1 The Relational Model: Relations, Tuples, Attributes and Domains

The relational model is the mathematical idea behind SQL databases. Data is held in relations (tables); each relation has attributes (columns) whose values come from domains (sets of allowed values), and its contents are a set of tuples (rows). Because everything is a table, any question can be answered by combining tables with a small set of operations.

Three vocabularies for the same thing

Relational theory SQL Everyday
The whole set Relation Table File / sheet
One item Tuple Row Record
One property Attribute Column Field
Allowed values Domain Type + constraints Valid entries
Number of columns Degree (arity) Column count Width
Number of rows Cardinality Row count Length

Theory, SQL and everyday words name the same ideas.

Think of it like…

A relation is like a printed bus timetable: each column has a fixed meaning and type of value, each line is one journey, and the order of lines does not change what the timetable says.

Real-world example

At the fictional Brookfield College, the relation STUDENT(student_id, name, year) has attribute 'year' drawn from the domain {1, 2, 3, 4}. The tuple (2031, 'Lina Osei', 2) is one fact: 'student 2031 is Lina Osei in year 2'. Inserting year 7 violates the domain, and inserting a second tuple with student_id 2031 violates the key.

How it works

  1. Define domains: the sets of legal values, such as integers 1-4 or valid dates.
  2. Declare a relation schema: a name plus attributes, each tied to a domain.
  3. Fill the relation with tuples; each tuple states one true fact.
  4. Identify candidate keys — minimal attribute sets that are unique for every tuple.
  5. Query by combining relations with relational operators; every result is again a relation.

Who uses it

Computer-science students meet it in every databases module; database designers, query-optimiser developers and interviewers use its vocabulary.

Key takeaway

Memorise the term mapping — relation = table, tuple = row, attribute = column, domain = allowed values (data type plus rules), degree = number of columns, cardinality = number of rows.

Going deeper

  • Formally a relation is a subset of the Cartesian product of its domains, so it has no duplicate tuples and no inherent order; SQL tables relax the first rule, allowing duplicates unless a key forbids them.
  • Codd's model uses set semantics while SQL uses bag (multiset) semantics; this is why SQL needs DISTINCT and UNION versus UNION ALL, and why 'SELECT name FROM student' can return the same name twice.
  • Values in a relation are atomic with respect to the DBMS's operators; modern SQL bends this with arrays, JSON and composite types, which theorists such as C. J. Date treat as legitimate domain types.
  • In 1985 Codd published twelve rules (numbered 0-12, so actually thirteen) defining a 'fully relational' DBMS, including the information rule, guaranteed access, systematic NULL treatment and an online relational catalogue; no commercial product meets them all.
  • The closed-world assumption says that a fact absent from a relation is false: if no row says Lina takes Chemistry, the database treats her as not taking it.
  • Relational calculus (tuple and domain variants) is the declarative counterpart of relational algebra; Codd proved them equal in expressive power, and a language that can express every algebra query is called relationally complete.

Common mistakes & misconceptions

  • Saying 'relational' means 'tables have relationships to each other': the name comes from the mathematical relation — the table itself.
  • Assuming rows have a natural order: neither the model nor SQL guarantees any order without ORDER BY.
  • Thinking a domain is just a data type: a domain can also carry rules, such as year BETWEEN 1 AND 4.

Try it: Declare a domain and a relation in PostgreSQL

CREATE DOMAIN year_of_study AS smallint CHECK (VALUE BETWEEN 1 AND 4);
CREATE TABLE student (
  student_id integer PRIMARY KEY,
  name       text NOT NULL,
  year       year_of_study NOT NULL
);
INSERT INTO student VALUES (2031, 'Lina Osei', 2);   -- ok
INSERT INTO student VALUES (2032, 'Sam Reid', 7);    -- rejected by the domain

Key databases, concepts, languages, tools & standards

Name What it does Example
Relation
E. F. Codd, 1970 · Concept · Current · Beginner · Relational
A named set of tuples sharing the same attributes — what SQL calls a table. STUDENT(student_id, name, year) is a relation with three attributes.
Tuple
Relational model · Concept · Current · Beginner · Relational
An ordered set of attribute values forming one row of a relation and stating one fact. (2031, 'Lina Osei', 2) is a tuple of STUDENT.
Attribute
Relational model · Concept · Current · Beginner · Relational
A named column of a relation whose values are drawn from one domain. 'year' is an attribute of STUDENT with domain 1-4.
Domain
Relational model; CREATE DOMAIN in ISO SQL · Concept · Current · Intermediate · Relational
The set of permissible values for an attribute — a data type plus any rules restricting it. CREATE DOMAIN uk_postcode AS text CHECK (VALUE ~ '^[A-Z0-9 ]{5,8}$');
Degree (arity)
Relational model · Concept · Current · Beginner · Relational
The number of attributes in a relation. STUDENT(student_id, name, year) has degree 3.
Cardinality (row count)
Relational model · Concept · Current · Beginner · Relational
The number of tuples in a relation at a given moment; it changes as rows are inserted and deleted. STUDENT has cardinality 4,812 this term.
Relation schema vs instance
Relational model · Concept · Current · Intermediate · Relational
The schema is the relation's fixed definition; the instance is the set of tuples it holds right now. STUDENT's schema stays the same all year while its instance grows each September.
Superkey
Relational model · Concept · Current · Intermediate · Relational
Any set of attributes whose values uniquely identify each tuple, possibly including unnecessary extras. {student_id, name} is a superkey because student_id alone is already unique.
Candidate key
Relational model · Concept · Current · Intermediate · Relational
A minimal superkey: remove any attribute and it stops being unique. One is chosen as primary key. Both student_id and national_insurance_no are candidate keys of STUDENT.
Closed-world assumption
Database theory · Concept · Current · Advanced · Relational
Anything not stated by a tuple in the database is assumed to be false. No ENROLMENT row for Lina in Chemistry means she is not enrolled.
Codd's 12 rules
E. F. Codd, Computerworld, 1985 · Standard · Legacy · Advanced · Relational
Thirteen rules (0-12) defining what a truly relational DBMS must provide, from the information rule to non-subversion. Rule 3 requires NULLs to be handled systematically, independent of data type.
Relational calculus
E. F. Codd, 1972 · Language · Current · Advanced · Relational
A declarative logic notation describing what result you want; tuple and domain variants exist. { s.name | s ∈ STUDENT ∧ s.year = 2 } describes all second-year names.
Set vs bag semantics
Relational theory vs ISO SQL · Concept · Current · Intermediate · Relational
Pure relations are sets without duplicates; SQL tables and results are bags that may repeat rows. SELECT year FROM student returns 4,812 rows; SELECT DISTINCT year returns 4.
The Third Manifesto
C. J. Date and Hugh Darwen, 1995 onward · Resource · Current · Advanced · Relational
A proposal for a 'truly relational' foundation without NULLs or duplicates, with Tutorial D as an example language. Database theory courses use it to critique SQL's departures from the model.

2.2 Relational Algebra and Set Operations

Relational algebra is a small set of operations that take tables in and give a table out: pick rows (selection), pick columns (projection), combine tables (join, product) and do set maths (union, difference, intersection). Every SQL query is translated into an algebra expression inside the database, and SQL's UNION, INTERSECT and EXCEPT are these set operations almost directly.

Optimisers push selections down early so joins handle fewer rows.
A query as an algebra pipeline. Optimisers push selections down early so joins handle fewer rows.

Think of it like…

It is like kitchen techniques for data: sieve (selection keeps some rows), slice (projection keeps some columns), mix (join) and compare two shopping lists (union, difference, intersection).

Real-world example

Brookfield College wants the names of second-year students taking Databases. In algebra that is π name ( σ year=2 (STUDENT) ⋈ σ course='Databases' (ENROLMENT) ). The college also wants students on either the chess or debating club list (UNION) and those on the chess list but not debating (EXCEPT).

How it works

  1. Start from base relations (tables).
  2. Apply σ (selection) to keep rows matching a condition.
  3. Apply ⋈ (join) or × (product) to combine relations.
  4. Apply π (projection) to keep the columns you need.
  5. Use ∪, ∩ and − to combine union-compatible results; the output is always a relation.

Who uses it

Database students, query-optimiser engineers and anyone reading EXPLAIN plans, which are trees of algebra-like operators.

Key takeaway

The minimal complete set is σ, π, ×, ∪, − and ρ (rename); join, intersection and division can all be built from them — a classic exam question.

Going deeper

  • Codd defined the algebra in 1970-72; because every operator returns a relation (closure), operators nest freely, which is why SQL allows subqueries in FROM, WHERE and SELECT.
  • Optimisers rewrite algebra using equivalence rules — pushing selections below joins, reordering joins, merging projections — then estimate costs of each physical plan; the internals chapter covers this cost-based optimisation.
  • Set operations need union-compatible inputs: the same number of columns with compatible types, matched by position, not by name; the result takes column names from the first query.
  • UNION removes duplicates (usually by sorting or hashing), so UNION ALL is faster when duplicates are impossible or wanted; INTERSECT ALL and EXCEPT ALL keep duplicate counts in PostgreSQL and other engines.
  • Oracle historically spelled EXCEPT as MINUS (Oracle 21c and later also accept EXCEPT); MySQL gained INTERSECT and EXCEPT only in 8.0.31 (2022).
  • Extended algebra adds γ (grouping and aggregation), outer joins, semi-joins (⋉) and anti-joins (▷), which correspond to SQL's GROUP BY, LEFT JOIN, EXISTS and NOT EXISTS.

Common mistakes & misconceptions

  • Using UNION when UNION ALL is meant: the extra duplicate-removal step costs time and can silently drop legitimate repeated rows.
  • Matching UNION columns by name: SQL matches them by position, so swapped columns produce wrong data without an error if types are compatible.
  • Confusing selection (σ, rows — SQL's WHERE) with SQL's SELECT keyword, which actually performs projection (columns).

Try it: Selection, projection, join and set operations in SQL

-- π name ( σ year = 2 (student) ⋈ σ course = 'Databases' (enrolment) )
SELECT s.name
FROM student s JOIN enrolment e ON e.student_id = s.student_id
WHERE s.year = 2 AND e.course = 'Databases';

SELECT student_id FROM chess_club
UNION        SELECT student_id FROM debating_club;   -- in either
SELECT student_id FROM chess_club
INTERSECT    SELECT student_id FROM debating_club;   -- in both
SELECT student_id FROM chess_club
EXCEPT       SELECT student_id FROM debating_club;   -- chess only (Oracle: MINUS)

Key databases, concepts, languages, tools & standards

Name What it does Example
Selection (σ)
Relational algebra (Codd) · Concept · Current · Beginner · Relational
Keeps only the tuples satisfying a condition; SQL expresses it in WHERE. σ year = 2 (STUDENT) keeps second-year students.
Projection (π)
Relational algebra · Concept · Current · Beginner · Relational
Keeps only chosen attributes and, in pure algebra, removes duplicate tuples. π name, year (STUDENT) is SELECT DISTINCT name, year FROM student.
Cartesian product (×)
Relational algebra · Concept · Current · Beginner · Relational
Pairs every tuple of one relation with every tuple of another; SQL's CROSS JOIN. 4 sizes × 3 colours gives 12 product variants.
Join (⋈)
Relational algebra · Concept · Current · Beginner · Relational
A product followed by a selection on matching values; natural join matches all same-named attributes. STUDENT ⋈ ENROLMENT pairs each student with their enrolments.
Theta join
Relational algebra · Concept · Current · Intermediate · Relational
A join whose condition uses any comparison (<, >, ≠, =), not only equality. Match orders to price bands with order.total BETWEEN band.low AND band.high.
Rename (ρ)
Relational algebra · Concept · Current · Intermediate · Relational
Gives a relation or attribute a new name, needed for self-joins and clear results. ρ manager(EMPLOYEE) lets EMPLOYEE be joined to itself; SQL uses aliases.
Union (∪)
Relational algebra · Concept · Current · Beginner · Relational
All tuples in either of two union-compatible relations, without duplicates. Chess members ∪ debating members gives everyone in at least one club.
Set difference (−)
Relational algebra · Concept · Current · Beginner · Relational
Tuples in the first relation but not the second. All students − students with enrolments = students taking no course.
Intersection (∩)
Relational algebra · Concept · Current · Beginner · Relational
Tuples present in both relations; derivable as R − (R − S). Chess ∩ debating gives students in both clubs.
Division (÷)
Relational algebra · Concept · Current · Advanced · Relational
Finds tuples related to every tuple of another relation — 'for all' questions. Students who have taken every compulsory module: ENROLMENT ÷ COMPULSORY.
Grouping and aggregation (γ)
Extended relational algebra · Concept · Current · Intermediate · Relational
Groups tuples by attributes and computes aggregates per group, matching SQL's GROUP BY. γ year; COUNT(*) (STUDENT) counts students per year.
UNION vs UNION ALL
ISO SQL · Command · Current · Beginner · Relational
UNION removes duplicate rows from the combined result; UNION ALL keeps every row and is cheaper. Combining 2025 and 2026 order tables with UNION ALL keeps all 3.1 million rows.
INTERSECT
ISO SQL-92; MySQL since 8.0.31 · Command · Current · Beginner · Relational
Returns rows produced by both queries. Customers who bought in both January and February.
EXCEPT / MINUS
ISO SQL-92 EXCEPT; Oracle MINUS · Command · Current · Beginner · Relational
Returns rows from the first query that the second query does not produce. Products listed in the catalogue EXCEPT products sold this year = slow movers.
Union compatibility
Relational algebra and ISO SQL · Concept · Current · Intermediate · Relational
Set operations need the same number of columns with compatible types, matched by position. SELECT id, name UNION SELECT id, email works only because both second columns are text.
Closure property
Relational algebra · Concept · Current · Intermediate · Relational
Every operator returns a relation, so operations can be nested to any depth. A join's output can feed a projection, whose output can feed a union.

2.3 SQL's Sublanguages: DDL, DML, DCL and TCL

SQL (Structured Query Language) is really several mini-languages in one. DDL defines structure (CREATE, ALTER, DROP), DML reads and changes data (SELECT, INSERT, UPDATE, DELETE, MERGE), DCL controls who may do what (GRANT, REVOKE) and TCL controls transactions (BEGIN, COMMIT, ROLLBACK). SQL is declarative: you say what result you want, and the database works out how.

Some books add DQL for SELECT alone; most treat SELECT as DML.
The four sublanguages of SQL. Some books add DQL for SELECT alone; most treat SELECT as DML.

Think of it like…

Running a library: DDL builds and rearranges the shelves, DML adds, finds and removes books, DCL hands out library cards with different rights, and TCL is the checkout desk that finalises or cancels a transaction.

Real-world example

Developer Nia builds a stock system for the fictional Oak & Ember furniture shop. She runs CREATE TABLE products (DDL), loads 1,200 products with INSERT and adjusts prices with UPDATE (DML), lets the reporting role only read via GRANT SELECT (DCL), and wraps a stock transfer between warehouses in BEGIN … COMMIT (TCL).

How it works

  1. DDL: create schemas, tables, indexes and views; alter or drop them as needs change.
  2. DML: insert new rows, query with SELECT, update or delete existing rows, merge from staging data.
  3. DCL: grant and revoke privileges to roles and users.
  4. TCL: group DML into transactions and commit or roll back.
  5. Version every DDL change as a migration so environments stay in step.

Who uses it

Developers write DML daily; DBAs and platform engineers own most DDL and DCL; everyone touching production must understand TCL.

Key takeaway

Know the differences between DELETE (removes chosen rows, logged per row, can roll back), TRUNCATE (empties a whole table fast) and DROP (removes the table itself) — and that TRUNCATE is transactional in PostgreSQL and SQL Server but causes an implicit commit in MySQL and Oracle.

Going deeper

  • PostgreSQL and SQL Server support transactional DDL — a failed migration of ten ALTER statements can be rolled back as one unit — whereas MySQL and Oracle commit implicitly around most DDL statements.
  • MERGE (standardised in SQL:2003) inserts, updates or deletes target rows based on a source; PostgreSQL added it in version 15 and RETURNING support in 17, while MySQL offers INSERT … ON DUPLICATE KEY UPDATE instead.
  • PostgreSQL's INSERT … ON CONFLICT DO UPDATE and SQLite's ON CONFLICT clause provide 'upserts' that are safe under concurrency, unlike a separate SELECT-then-INSERT.
  • ALTER TABLE operations differ widely in cost: adding a nullable column is usually instant metadata, but changing a column type can rewrite every row and lock the table — big-table migrations are planned carefully or done online.
  • SQL is declarative and set-based; procedural extensions (PL/pgSQL, T-SQL, PL/SQL) add loops and variables, but row-by-row loops are usually far slower than one set-based statement.
  • Keywords are case-insensitive and statements end with a semicolon in most clients; unquoted identifiers are folded to lower case in PostgreSQL and to upper case in Oracle, a common source of 'table not found' errors.

Common mistakes & misconceptions

  • Running UPDATE or DELETE without a WHERE clause — every row changes; run the WHERE as a SELECT first, or inside BEGIN so you can roll back.
  • Assuming DDL can always be rolled back: in MySQL and Oracle a CREATE or ALTER commits the current transaction.
  • Granting broad privileges (ALL, or superuser) to application accounts instead of only the needed SELECT, INSERT, UPDATE and DELETE.

Try it: One statement from each sublanguage (PostgreSQL)

-- DDL
CREATE TABLE products (sku text PRIMARY KEY, name text NOT NULL, price numeric(8,2));
-- DML
INSERT INTO products VALUES ('OAK-T1','Oak table',449.00), ('OAK-C1','Oak chair',89.00);
UPDATE products SET price = price * 1.05 WHERE sku LIKE 'OAK-%';
-- DCL
CREATE ROLE reporting;
GRANT SELECT ON products TO reporting;
-- TCL
BEGIN;
DELETE FROM products WHERE sku = 'OAK-C1';
ROLLBACK;   -- the chair is still there

Key databases, concepts, languages, tools & standards

Name What it does Example
SQL
IBM (SEQUEL, 1974); ISO/IEC 9075 · Language · Current · Beginner · Relational
Structured Query Language: the standard declarative language for defining, querying and controlling relational databases. SELECT name FROM products WHERE price < 100; works on almost every relational database.
DDL (Data Definition Language)
ISO SQL · Concept · Current · Beginner · Relational
Statements that define or change database structure: CREATE, ALTER, DROP, TRUNCATE, RENAME. ALTER TABLE products ADD COLUMN colour text;
DML (Data Manipulation Language)
ISO SQL · Concept · Current · Beginner · Relational
Statements that read or change data: SELECT, INSERT, UPDATE, DELETE and MERGE. UPDATE products SET price = 99 WHERE sku = 'OAK-C1';
DCL (Data Control Language)
ISO SQL · Concept · Current · Beginner · Relational
Statements that grant or revoke privileges on database objects to users and roles. GRANT SELECT, INSERT ON orders TO app_writer;
TCL (Transaction Control Language)
ISO SQL · Concept · Current · Beginner · Relational
Statements that start, commit, roll back and mark points within transactions. BEGIN; … SAVEPOINT s1; … ROLLBACK TO s1; … COMMIT;
CREATE TABLE
ISO SQL DDL · Command · Current · Beginner · Relational
Defines a new table's name, columns, data types and constraints. CREATE TABLE warehouses (id int PRIMARY KEY, city text NOT NULL);
ALTER TABLE
ISO SQL DDL · Command · Current · Beginner · Relational
Changes an existing table: add, drop or rename columns, change types, add or drop constraints. ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);
DROP vs TRUNCATE vs DELETE
ISO SQL and vendor docs · Concept · Current · Beginner · Relational
DROP removes the table; TRUNCATE empties it quickly; DELETE removes chosen rows one by one, fully logged. Empty a 40-million-row staging table with TRUNCATE, not DELETE, to finish in seconds.
INSERT
ISO SQL DML · Command · Current · Beginner · Relational
Adds new rows to a table, from literal values or from a SELECT. INSERT INTO archive_orders SELECT * FROM orders WHERE placed < '2024-01-01';
UPDATE
ISO SQL DML · Command · Current · Beginner · Relational
Changes column values in existing rows that match a condition. UPDATE products SET price = price * 0.9 WHERE category = 'clearance';
DELETE
ISO SQL DML · Command · Current · Beginner · Relational
Removes rows that match a condition; without WHERE it removes every row. DELETE FROM sessions WHERE last_seen < now() - interval '30 days';
MERGE
ISO SQL:2003; PostgreSQL 15+, SQL Server, Oracle, Db2 · Command · Current · Intermediate · Relational
Compares a source with a target and inserts, updates or deletes target rows in one statement. Nightly MERGE applies the supplier price file to the products table.
Upsert (ON CONFLICT / ON DUPLICATE KEY)
PostgreSQL and SQLite ON CONFLICT; MySQL ON DUPLICATE KEY UPDATE · Command · Current · Intermediate · Relational
Inserts a row, or updates the existing one if a unique key already exists, atomically. INSERT INTO page_views(url, n) VALUES ('/home', 1) ON CONFLICT (url) DO UPDATE SET n = page_views.n + 1;
GRANT and REVOKE
ISO SQL DCL · Command · Current · Beginner · Relational
Give or remove privileges such as SELECT, INSERT, UPDATE, DELETE or EXECUTE on objects. REVOKE DELETE ON orders FROM app_writer; stops the app deleting orders.
BEGIN / START TRANSACTION
ISO SQL START TRANSACTION; BEGIN in PostgreSQL, SQL Server, SQLite · Command · Current · Beginner · Relational
Explicitly opens a transaction so several statements commit or roll back together. START TRANSACTION; in MySQL before moving stock between two warehouses.
Transactional DDL
PostgreSQL, SQL Server, SQLite; not MySQL or Oracle · Feature · Current · Intermediate · Relational
Schema changes take part in transactions and can be rolled back with everything else. A failed migration's ALTER and CREATE statements roll back cleanly in PostgreSQL.

2.4 SELECT in Depth

SELECT is the statement for asking questions. You list the columns you want, name the tables in FROM, filter rows with WHERE, sort with ORDER BY and limit how many rows come back with LIMIT or FETCH FIRST. Although you write SELECT first, the database logically processes FROM and WHERE before SELECT, which explains many beginner errors.

Written order starts with SELECT; logical order starts with FROM.
Logical processing order of SELECT. Written order starts with SELECT; logical order starts with FROM.

Think of it like…

Writing a SELECT is like ordering from a catalogue: choose the catalogue (FROM), tick the conditions (WHERE), say which details to show (SELECT), how to sort them (ORDER BY) and how many to send (LIMIT).

Real-world example

The fictional streaming service Lumen wants its ten most-watched films released since 2020 that are suitable for children. The query reads FROM films, keeps rows WHERE release_year >= 2020 AND rating IN ('U','PG'), shows title and views, sorts ORDER BY views DESC and keeps FETCH FIRST 10 ROWS ONLY. A CASE expression labels each film 'hit' or 'niche' depending on views.

How it works

  1. FROM (and JOIN) assembles the source rows.
  2. WHERE removes rows that fail the condition.
  3. GROUP BY and HAVING form and filter groups (when used).
  4. SELECT computes output columns and aliases; DISTINCT removes duplicates.
  5. ORDER BY sorts; LIMIT/OFFSET or FETCH FIRST trims the result.

Who uses it

Everyone who uses a relational database — developers, analysts, data scientists, testers and support engineers — writes SELECT statements daily.

Key takeaway

Because WHERE is evaluated before SELECT, you cannot use a SELECT alias in WHERE (repeat the expression or use a subquery/CTE), but you can use it in ORDER BY.

Going deeper

  • Logical order is about meaning, not execution: the optimiser may use an index to read rows already sorted, stop after ten rows, or push filters into joins, as long as the result matches the logical definition.
  • Row-limiting syntax varies: LIMIT/OFFSET (PostgreSQL, MySQL, SQLite), TOP (SQL Server), and the standard FETCH FIRST n ROWS ONLY from SQL:2008, supported by PostgreSQL, Oracle 12c+, Db2 and SQL Server (with OFFSET).
  • OFFSET pagination gets slower on deep pages because the database still reads and discards the skipped rows; keyset pagination (WHERE id > last_seen_id ORDER BY id LIMIT 20) stays fast at page 10,000.
  • A predicate is 'sargable' when an index can be used: WHERE created_at >= '2026-01-01' can use an index on created_at, while WHERE EXTRACT(YEAR FROM created_at) = 2026 usually cannot without an expression index.
  • Text comparisons, LIKE and ORDER BY depend on collation: case sensitivity and accent rules differ between MySQL's default utf8mb4_0900_ai_ci (case- and accent-insensitive) and PostgreSQL's usual case-sensitive collations.
  • SELECT * is fine for exploration but harmful in application code: it returns unneeded columns, prevents index-only scans and breaks when columns are added or reordered.

Common mistakes & misconceptions

  • Expecting a stable order without ORDER BY — the same query can return rows in a different order tomorrow.
  • Paginating with ORDER BY on a non-unique column (such as created_at alone), which can repeat or skip rows between pages; add a unique tie-breaker like id.
  • Writing WHERE col = NULL, which never matches; use IS NULL.

Try it: Filtering, CASE, sorting and limiting (PostgreSQL, SQLite or MySQL)

SELECT title,
       views,
       CASE WHEN views >= 1000000 THEN 'hit' ELSE 'niche' END AS popularity
FROM films
WHERE release_year >= 2020
  AND rating IN ('U', 'PG')
  AND title LIKE '%Dragon%'
ORDER BY views DESC, title
LIMIT 10;               -- standard SQL: FETCH FIRST 10 ROWS ONLY

Key databases, concepts, languages, tools & standards

Name What it does Example
SELECT list and aliases (AS)
ISO SQL · Command · Current · Beginner · Relational
Chooses and computes output columns; AS gives a column or expression a readable name. SELECT price * qty AS line_total FROM order_lines;
FROM clause
ISO SQL · Command · Current · Beginner · Relational
Names the tables, views, subqueries or joins that supply rows to the query. FROM films f JOIN genres g ON g.id = f.genre_id
WHERE clause
ISO SQL · Command · Current · Beginner · Relational
Filters rows before grouping, keeping only those for which the condition is TRUE. WHERE release_year >= 2020 AND rating IN ('U','PG')
ORDER BY
ISO SQL · Command · Current · Beginner · Relational
Sorts the result by one or more expressions, ascending (ASC, default) or descending (DESC). ORDER BY views DESC, title ASC
LIMIT and OFFSET
PostgreSQL, MySQL, SQLite, DuckDB · Command · Current · Beginner · Relational
Return at most n rows, optionally after skipping the first m. LIMIT 20 OFFSET 40 shows page 3 of 20 results per page.
FETCH FIRST n ROWS ONLY
ISO SQL:2008; PostgreSQL, Oracle 12c+, Db2, SQL Server · Command · Current · Intermediate · Relational
The standard row-limiting clause; WITH TIES also keeps rows tied with the last one. ORDER BY score DESC FETCH FIRST 3 ROWS WITH TIES returns 4 rows if two share third place.
TOP
Microsoft SQL Server (T-SQL) · Command · Current · Beginner · Relational
SQL Server's way of limiting rows, written right after SELECT. SELECT TOP (10) title FROM films ORDER BY views DESC;
DISTINCT
ISO SQL · Command · Current · Beginner · Relational
Removes duplicate rows from the result. SELECT DISTINCT country FROM customers; lists each country once.
CASE expression
ISO SQL-92 · Command · Current · Beginner · Relational
Returns different values depending on conditions — SQL's if-then-else inside a query. CASE WHEN views >= 1000000 THEN 'hit' ELSE 'niche' END
LIKE and ILIKE
ISO SQL LIKE; ILIKE in PostgreSQL · Command · Current · Beginner · Relational
Pattern matching on text with % (any characters) and _ (one character); ILIKE ignores case. WHERE email LIKE '%@example.org'
IN and BETWEEN
ISO SQL · Command · Current · Beginner · Relational
IN tests membership in a list; BETWEEN tests an inclusive range. WHERE price BETWEEN 10 AND 20 AND size IN ('S','M')
CAST and type conversion
ISO SQL CAST; PostgreSQL :: shorthand · Command · Current · Beginner · Relational
Converts a value from one data type to another, explicitly. CAST('2026-10-08' AS date) or '42'::int in PostgreSQL.
Logical query processing order
ISO SQL semantics · Concept · Current · Intermediate · Relational
The order in which clauses are conceptually evaluated: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. Explains why 'WHERE line_total > 100' fails when line_total is a SELECT alias.
Keyset pagination
Performance technique (also called seek method) · Pattern · Current · Intermediate · Relational
Pages through results using the last seen key instead of OFFSET, keeping deep pages fast. WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20
Sargable predicate
Query-tuning term (Search ARGument ABLE) · Concept · Current · Intermediate · Relational
A condition written so the optimiser can use an index to satisfy it. WHERE created_at >= '2026-01-01' is sargable; WHERE YEAR(created_at) = 2026 usually is not.

2.5 Joins: Inner, Outer, Cross, Self, Semi and Anti

A join combines rows from two tables that belong together, usually because one table's foreign key matches the other's primary key. An inner join keeps only matching pairs; outer joins also keep unmatched rows, filling the gaps with NULL; a cross join pairs everything with everything; a self-join joins a table to itself; and semi- and anti-joins ask 'is there a match?' without returning the matched rows.

What each join returns

Rows returned Unmatched rows kept
INNER JOIN Matching pairs (7) None
LEFT JOIN Pairs + lonely customers (8) Left side, NULL-filled
RIGHT JOIN Pairs + lonely orders Right side, NULL-filled
FULL OUTER JOIN Pairs + both kinds Both sides
CROSS JOIN Every pair (5 × 7 = 35) Not applicable
Semi-join (EXISTS) Customers with ≥1 order (4) None; each once
Anti-join (NOT EXISTS) Customers with no order (1) Only the unmatched

Customers on the left, orders on the right; Ivo has no orders.

Think of it like…

Joining is like matching a class list with a list of signed permission slips: inner join lists pupils who have a slip; left join lists every pupil, with blanks where no slip came back; anti-join lists only those still missing a slip.

Real-world example

The fictional online bookshop Quillstone has 5 customers and 7 orders. An INNER JOIN of customers and orders gives 7 rows. A LEFT JOIN gives 8 rows, because customer Ivo has no orders and appears once with NULL order columns. A NOT EXISTS anti-join lists just Ivo, so marketing can send him a welcome discount.

How it works

  1. Choose the tables and the join condition, usually foreign key = primary key.
  2. Pick the join type based on which unmatched rows you need to keep.
  3. Write the condition in ON (or USING for same-named columns).
  4. Put filters on the outer table in WHERE, but filters on the optional table in ON.
  5. Check row counts: a join can multiply rows if the key is not unique on one side.

Who uses it

Joins are the core skill in every SQL interview and the most common operation in reporting, APIs and data pipelines.

Key takeaway

A filter on the right-hand table placed in WHERE silently turns a LEFT JOIN into an INNER JOIN, because NULL rows fail the condition — put that filter in the ON clause instead.

Going deeper

  • A join on a non-unique key multiplies rows (fan-out): joining orders to order_lines and to payments at once can double-count totals, so aggregate each child table before joining or use separate subqueries.
  • MySQL has no FULL OUTER JOIN (emulate it with a LEFT JOIN UNION a RIGHT JOIN), and SQLite added RIGHT and FULL OUTER JOIN only in version 3.39.0 (2022).
  • Semi-joins (EXISTS, IN) return each left row at most once however many matches exist, which is why EXISTS is preferred over JOIN + DISTINCT for 'customers who have ordered'.
  • NOT IN is a dangerous anti-join: if the subquery returns even one NULL, NOT IN yields no rows at all; NOT EXISTS or LEFT JOIN … WHERE right.key IS NULL behave as expected.
  • LATERAL (SQL:1999; PostgreSQL, MySQL 8.0.14+, Oracle) and SQL Server's CROSS APPLY let a subquery in FROM refer to earlier tables — ideal for 'latest three orders per customer'.
  • Physically, databases run joins with nested-loop, hash or merge algorithms chosen by the optimiser; the internals chapter explains when each wins.

Common mistakes & misconceptions

  • Forgetting the join condition (or using old comma syntax without WHERE), creating an accidental Cartesian product of millions of rows.
  • Filtering the optional side of a LEFT JOIN in WHERE, turning it into an inner join.
  • Using NATURAL JOIN in production code: adding a same-named column like 'updated_at' later silently changes the join condition.

Try it: Inner, left and anti-join on the same data (any SQL database)

SELECT c.name, o.order_id
FROM customers c JOIN orders o ON o.customer_id = c.customer_id;      -- inner

SELECT c.name, o.order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id; -- keeps Ivo

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id); -- anti-join

Key databases, concepts, languages, tools & standards

Name What it does Example
INNER JOIN
ISO SQL-92 · Command · Current · Beginner · Relational
Returns only row pairs where the join condition is true in both tables. customers JOIN orders ON orders.customer_id = customers.customer_id
LEFT OUTER JOIN
ISO SQL-92 · Command · Current · Beginner · Relational
Keeps every row from the left table, adding matched right rows or NULLs when none match. Every product with its sales total, including products never sold.
RIGHT OUTER JOIN
ISO SQL-92; SQLite since 3.39 · Command · Current · Beginner · Relational
Keeps every row from the right table; usually rewritten as a LEFT JOIN for readability. orders RIGHT JOIN customers … equals customers LEFT JOIN orders.
FULL OUTER JOIN
ISO SQL-92; not in MySQL · Command · Current · Intermediate · Relational
Keeps all rows from both tables, NULL-filling whichever side has no match. Reconcile bank records with ledger entries, showing unmatched items on either side.
CROSS JOIN
ISO SQL-92 · Command · Current · Beginner · Relational
Pairs every row of one table with every row of another (Cartesian product). sizes CROSS JOIN colours generates all 12 product variants.
Self-join
Standard SQL technique · Pattern · Current · Intermediate · Relational
Joining a table to itself under two aliases to relate rows in the same table. employees e JOIN employees m ON e.manager_id = m.employee_id lists each person's manager.
Semi-join (EXISTS / IN)
Relational algebra ⋉; SQL EXISTS and IN · Pattern · Current · Intermediate · Relational
Returns rows from the first table that have at least one match, each only once. Customers WHERE EXISTS (an order in the last 30 days).
Anti-join (NOT EXISTS)
Relational algebra ▷; SQL NOT EXISTS · Pattern · Current · Intermediate · Relational
Returns rows from the first table that have no match in the second. Products WHERE NOT EXISTS (any order line) = products never sold.
ON clause
ISO SQL-92 · Command · Current · Beginner · Relational
Specifies the join condition; for outer joins it decides matching without removing outer rows. LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'paid'
USING and NATURAL JOIN
ISO SQL-92 · Command · Current · Intermediate · Relational
USING joins on listed same-named columns; NATURAL JOIN joins on all same-named columns automatically. orders JOIN customers USING (customer_id)
Non-equi join
Standard SQL technique · Pattern · Current · Intermediate · Relational
A join whose condition uses ranges or inequalities rather than equality. Join sales to tax_bands ON sale.amount BETWEEN band.min AND band.max.
LATERAL join / CROSS APPLY
ISO SQL:1999 LATERAL; SQL Server CROSS/OUTER APPLY · Command · Current · Advanced · Relational
A join where the right-hand subquery can reference columns of the left-hand row. Each customer's three latest orders via LATERAL (… ORDER BY placed DESC LIMIT 3).
Join fan-out
Query-correctness term · Concept · Current · Intermediate · Relational
Row multiplication when a join key repeats, which inflates sums and counts if unnoticed. Joining an order to 3 lines and 2 payments yields 6 rows; SUM(payment) doubles.
Implicit (comma) join
Pre-SQL-92 style, still valid · Command · Legacy · Beginner · Relational
Listing tables separated by commas in FROM with the join condition in WHERE. FROM customers c, orders o WHERE o.customer_id = c.customer_id

2.6 Aggregation: GROUP BY and HAVING

Aggregate functions boil many rows down to one value: COUNT, SUM, AVG, MIN and MAX. GROUP BY splits rows into groups first, so you get one result per group, such as total sales per shop. HAVING then filters those groups, the way WHERE filters individual rows.

WHERE works on rows; HAVING works on groups.
From 2.4 million rows to 6 shops. WHERE works on rows; HAVING works on groups.

Think of it like…

Aggregation is like a teacher sorting exam papers into piles by class (GROUP BY), counting and averaging each pile (aggregates), then only reporting piles whose average is below 50 (HAVING).

Real-world example

The fictional café chain Bean There has 2.4 million till rows for 2026. GROUP BY shop_id with SUM(amount) gives one revenue line per shop for its 38 shops. Adding HAVING SUM(amount) < 200000 shows the six under-performing shops. ROLLUP adds subtotals per region and a grand total in the same query.

How it works

  1. WHERE removes rows you do not want before grouping (e.g. refunds).
  2. GROUP BY puts the remaining rows into buckets by one or more columns.
  3. Aggregate functions compute one value per bucket.
  4. HAVING keeps only buckets that meet a condition on the aggregates.
  5. SELECT may only show grouping columns and aggregates; ORDER BY sorts the groups.

Who uses it

Analysts, BI developers and data engineers aggregate constantly; developers use it for dashboards, counters and reports.

Key takeaway

WHERE filters rows before grouping and cannot use aggregates; HAVING filters groups after grouping and can — a top-five SQL interview question.

Going deeper

  • COUNT(*) counts rows; COUNT(column) counts non-NULL values; COUNT(DISTINCT column) counts different non-NULL values — on a table with missing emails these three give three different numbers.
  • SUM, AVG, MIN and MAX ignore NULLs, and SUM over zero rows returns NULL rather than 0; wrap it in COALESCE(SUM(x), 0) for reports.
  • Standard SQL requires every non-aggregated SELECT column to appear in GROUP BY unless it is functionally dependent on the grouping key (SQL:1999); MySQL enforces this with ONLY_FULL_GROUP_BY, on by default since 5.7.
  • GROUPING SETS, ROLLUP and CUBE (SQL:1999) compute several groupings in one pass; the GROUPING() function tells subtotal rows apart from real NULLs.
  • Ordered-set aggregates such as PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) compute medians; string aggregation is STRING_AGG in PostgreSQL and SQL Server, LISTAGG in Oracle and Db2, GROUP_CONCAT in MySQL and SQLite.
  • SQL:2023 standardised ANY_VALUE(), which returns an arbitrary value from each group — useful when a column is the same throughout the group but not in GROUP BY.

Common mistakes & misconceptions

  • Putting an aggregate condition in WHERE (WHERE SUM(amount) > 100) — it is an error; use HAVING.
  • Averaging averages: the mean of shop averages is not the overall average when shops have different numbers of sales; compute SUM/COUNT over the base rows.
  • Using COUNT(column) when you meant COUNT(*), silently skipping rows where that column is NULL.

Try it: Totals per shop, filtered groups and subtotals (PostgreSQL)

SELECT region, shop_id,
       COUNT(*)                         AS sales,
       SUM(amount)                      AS revenue,
       ROUND(AVG(amount), 2)            AS avg_ticket,
       COUNT(*) FILTER (WHERE amount > 20) AS big_sales
FROM till
WHERE sold_at >= '2026-01-01' AND NOT is_refund
GROUP BY ROLLUP (region, shop_id)
HAVING SUM(amount) > 0
ORDER BY region, shop_id;

Key databases, concepts, languages, tools & standards

Name What it does Example
COUNT
ISO SQL aggregate · Command · Current · Beginner · Relational
COUNT(*) counts rows; COUNT(expr) counts non-NULL values; COUNT(DISTINCT expr) counts different values. SELECT COUNT(DISTINCT customer_id) FROM orders; counts unique buyers.
SUM and AVG
ISO SQL aggregates · Command · Current · Beginner · Relational
Add up or average numeric values in a group, ignoring NULLs. SELECT shop_id, SUM(amount), AVG(amount) FROM till GROUP BY shop_id;
MIN and MAX
ISO SQL aggregates · Command · Current · Beginner · Relational
Return the smallest and largest values in a group; work on numbers, dates and text. SELECT MIN(sold_at), MAX(sold_at) FROM till; shows the data's date range.
GROUP BY
ISO SQL · Command · Current · Beginner · Relational
Splits rows into groups sharing the same values so aggregates are computed per group. GROUP BY region, shop_id gives one row per shop within each region.
HAVING
ISO SQL · Command · Current · Beginner · Relational
Filters groups after aggregation using conditions on aggregate values. HAVING COUNT(*) >= 5 keeps customers with at least five orders.
GROUPING SETS
ISO SQL:1999 · Command · Current · Advanced · Relational
Computes several different groupings in one query, as if UNION ALL of separate GROUP BYs. GROUP BY GROUPING SETS ((region), (product), ()) gives region totals, product totals and a grand total.
ROLLUP
ISO SQL:1999 · Command · Current · Intermediate · Relational
Produces hierarchical subtotals from right to left plus a grand total. GROUP BY ROLLUP (year, month) adds yearly subtotals and an overall total.
CUBE
ISO SQL:1999 · Command · Current · Advanced · Relational
Produces subtotals for every combination of the listed columns. GROUP BY CUBE (region, product) gives 4 levels of totals.
FILTER clause
ISO SQL:2003; PostgreSQL, SQLite, DuckDB · Command · Current · Intermediate · Relational
Restricts which rows feed one aggregate, enabling several conditional counts in one pass. COUNT(*) FILTER (WHERE status = 'late') AS late_orders
Conditional aggregation
Portable SQL technique · Pattern · Current · Intermediate · Relational
Using CASE inside an aggregate to count or sum only certain rows; works in every dialect. SUM(CASE WHEN channel = 'web' THEN amount ELSE 0 END) AS web_revenue
STRING_AGG / LISTAGG / GROUP_CONCAT
PostgreSQL & SQL Server / Oracle & Db2 / MySQL & SQLite · Command · Current · Intermediate · Relational
Concatenates text values from a group into one string with a separator. STRING_AGG(product_name, ', ' ORDER BY product_name) lists an order's items.
PERCENTILE_CONT and medians
ISO SQL:2003 ordered-set aggregate · Command · Current · Advanced · Relational
Computes a continuous percentile such as the median over an ordered group. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY delivery_minutes) gives the median delivery time.
ANY_VALUE
ISO SQL:2023; MySQL, BigQuery, Snowflake, PostgreSQL 16+ · Command · Emerging · Intermediate · Relational
Aggregate returning any one value from the group, for columns constant within each group. SELECT customer_id, ANY_VALUE(customer_name) … GROUP BY customer_id
ONLY_FULL_GROUP_BY
MySQL SQL mode (default since 5.7) · Feature · Current · Intermediate · Relational
Rejects queries selecting non-grouped columns that are not functionally dependent on the GROUP BY key. SELECT shop_id, city, SUM(amount) … GROUP BY shop_id errors unless city depends on shop_id.

2.7 Subqueries, CTEs and Recursive Queries

A subquery is a query inside another query, used as a value, a list or a temporary table. A correlated subquery refers to the outer row, so it is logically re-run for each row. Common table expressions (CTEs), written with WITH, give subqueries names so long queries read top to bottom, and recursive CTEs can walk hierarchies like org charts and category trees.

Iteration stops when a step produces no new rows.
How a recursive CTE runs. Iteration stops when a step produces no new rows.

Think of it like…

Subqueries are like asking a colleague a quick side-question while writing a report; a CTE is writing each side-question's answer on a labelled sticky note first; a recursive CTE is asking 'and who reports to them?' again and again until nobody is left.

Real-world example

HR at the fictional engineering firm Kestrel Labs wants staff earning more than their department's average — a correlated subquery compares each employee to AVG(salary) of their own department. Then they want everyone under the CTO, Grace, at any depth: a recursive CTE starts from Grace (anchor) and repeatedly joins employees whose manager_id is in the previous level, finding all 140 people in five levels.

How it works

  1. Write the inner question first and test it on its own.
  2. Decide its role: a single value (scalar), a list (IN/EXISTS) or a table (FROM or WITH).
  3. For readability, name pieces as CTEs: WITH a AS (…), b AS (…) SELECT …
  4. For hierarchies, write WITH RECURSIVE: an anchor query UNION ALL a recursive step.
  5. Add a depth limit or cycle check, and confirm the plan with EXPLAIN.

Who uses it

Analysts and analytics engineers write CTE-heavy SQL daily (dbt models are mostly CTEs); developers use recursive CTEs for trees, threads and bills of materials.

Key takeaway

Every recursive CTE needs three parts — anchor, UNION ALL, recursive member — and a termination condition; interviewers love 'list all reports of a manager' and 'generate a calendar of dates'.

Going deeper

  • A correlated subquery is defined as running once per outer row, but optimisers often decorrelate it into a join or semi-join; when they cannot, a 1-million-row outer table can mean a million inner executions.
  • Since PostgreSQL 12, non-recursive CTEs referenced once are inlined into the main query; write AS MATERIALIZED to force a one-off computed result or AS NOT MATERIALIZED to force inlining (before 12 they were always materialised optimisation fences).
  • Recursive CTEs arrived in SQL:1999; MySQL added them in 8.0 (2018) and SQLite in 3.8.3 (2014); SQL Server uses WITH without the RECURSIVE keyword and caps recursion at 100 levels unless OPTION (MAXRECURSION n) is set.
  • SQL:1999's SEARCH and CYCLE clauses (implemented in PostgreSQL 14) produce depth-first or breadth-first ordering and detect loops, which matters in graphs where A follows B follows A.
  • Oracle's older CONNECT BY PRIOR syntax predates recursive CTEs and is still common in Oracle code bases; Oracle also supports the standard recursive WITH since 11g Release 2.
  • PostgreSQL allows data-modifying CTEs — WITH moved AS (DELETE … RETURNING *) INSERT INTO archive SELECT * FROM moved — moving rows between tables atomically in one statement.

Common mistakes & misconceptions

  • Writing a recursive CTE without a stop condition over cyclic data, which loops until it hits a recursion limit or runs out of memory.
  • A scalar subquery that returns more than one row causes a runtime error — make sure it is keyed or aggregated.
  • Using NOT IN (subquery) when the subquery can return NULL; use NOT EXISTS instead.

Try it: Correlated subquery and recursive org chart (PostgreSQL, SQLite, MySQL 8+)

-- above their department's average
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees x WHERE x.dept_id = e.dept_id);

-- everyone under Grace (employee 1), with depth
WITH RECURSIVE chain AS (
  SELECT employee_id, name, 0 AS depth FROM employees WHERE employee_id = 1
  UNION ALL
  SELECT e.employee_id, e.name, c.depth + 1
  FROM employees e JOIN chain c ON e.manager_id = c.employee_id
  WHERE c.depth < 20
)
SELECT * FROM chain ORDER BY depth, name;

Key databases, concepts, languages, tools & standards

Name What it does Example
Scalar subquery
ISO SQL · Concept · Current · Intermediate · Relational
A subquery returning exactly one value, usable anywhere a single value is allowed. SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) FROM customers c;
IN (subquery)
ISO SQL · Command · Current · Beginner · Relational
Tests whether a value appears in the set of values a subquery returns. WHERE product_id IN (SELECT product_id FROM recalls)
EXISTS
ISO SQL · Command · Current · Intermediate · Relational
True if a subquery returns at least one row; stops at the first match. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
Correlated subquery
ISO SQL · Concept · Current · Intermediate · Relational
A subquery that references columns of the outer query, so it is evaluated per outer row. Employees earning above the average of their own department.
Derived table
ISO SQL (subquery in FROM) · Concept · Current · Intermediate · Relational
A subquery in the FROM clause that acts as a temporary, unnamed table. SELECT * FROM (SELECT shop_id, SUM(amount) t FROM till GROUP BY shop_id) s WHERE t > 1e5;
ANY / SOME / ALL
ISO SQL quantified comparisons · Command · Current · Advanced · Relational
Compare a value against every value a subquery returns: true for any match, or only if true for all. WHERE price > ALL (SELECT price FROM products WHERE brand = 'Budget')
Common table expression (WITH)
ISO SQL:1999 · Command · Current · Intermediate · Relational
A named subquery defined before the main query, making complex SQL readable and reusable within it. WITH monthly AS (SELECT …) SELECT * FROM monthly WHERE revenue > 50000;
Recursive CTE (WITH RECURSIVE)
ISO SQL:1999; MySQL 8.0+, SQLite 3.8.3+ · Command · Current · Advanced · Relational
A CTE that refers to itself, repeatedly adding rows to walk trees, graphs or generate series. Find all subcategories of 'Garden' in a category tree of any depth.
Anchor and recursive member
ISO SQL recursive query structure · Concept · Current · Advanced · Relational
The starting query and the self-referencing query joined by UNION ALL in a recursive CTE. Anchor selects the CEO; the recursive member joins employees whose manager is in the previous level.
SEARCH and CYCLE clauses
ISO SQL:1999; PostgreSQL 14+ · Command · Current · Advanced · Relational
Add depth-first or breadth-first ordering and loop detection to recursive CTEs. CYCLE id SET is_cycle USING path stops endless follow-loops in a social graph.
MATERIALIZED / NOT MATERIALIZED CTE
PostgreSQL 12+ · Feature · Current · Advanced · Relational
Hints controlling whether a CTE is computed once and stored or inlined into the main query. WITH big AS MATERIALIZED (…) computes an expensive aggregate only once.
CONNECT BY
Oracle Database (proprietary) · Command · Legacy · Advanced · Relational
Oracle's older syntax for hierarchical queries using START WITH and PRIOR. SELECT name FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr;
Data-modifying CTE
PostgreSQL · Feature · Current · Advanced · Relational
A CTE containing INSERT, UPDATE or DELETE … RETURNING whose output feeds the main statement. WITH gone AS (DELETE FROM carts WHERE stale RETURNING *) INSERT INTO cart_archive SELECT * FROM gone;
generate_series
PostgreSQL set-returning function · Command · Current · Intermediate · Relational
Produces a series of numbers or timestamps, handy for calendars and filling gaps. SELECT d::date FROM generate_series('2026-10-01', '2026-10-31', interval '1 day') d;

2.8 Window Functions: Ranking, LAG/LEAD and Running Totals

A window function calculates something across a set of related rows — the 'window' — without collapsing them into one row like GROUP BY does. You can number rows, rank them, look at the previous or next row, or keep a running total, while still seeing every individual row. The OVER clause defines the window: which rows belong together (PARTITION BY) and in what order (ORDER BY).

Three ways to rank tied scores

ROW_NUMBER RANK DENSE_RANK
Ana 95 1 1 1
Ben 90 2 2 2
Cara 90 3 2 2
Dev 85 4 4 3
Eli 80 5 5 4

Ben and Cara tie on 90 — watch how each function treats them.

Think of it like…

GROUP BY is like reading only each class's average mark; a window function is like writing each pupil's rank and the class average next to their own name on the full register.

Real-world example

The fictional running club StrideOn has race results for 600 members. ROW_NUMBER() OVER (PARTITION BY age_group ORDER BY finish_time) numbers runners within each age group, so filtering on rank <= 3 gives the podiums. LAG(finish_time) OVER (PARTITION BY runner_id ORDER BY race_date) shows each runner's improvement since their last race, and SUM(distance_km) OVER (… ORDER BY race_date) gives a running season total.

How it works

  1. The FROM, WHERE, GROUP BY and HAVING steps produce rows first.
  2. PARTITION BY splits those rows into independent windows (like groups, but rows stay).
  3. ORDER BY inside OVER orders rows within each partition.
  4. An optional frame (ROWS/RANGE/GROUPS BETWEEN …) narrows which neighbouring rows count.
  5. The function is computed per row; filter on its result in an outer query, CTE or QUALIFY.

Who uses it

Data analysts, analytics engineers and data scientists use window functions daily; they appear in almost every intermediate SQL interview.

Key takeaway

Know the ranking trio for ties: scores 95, 90, 90, 85 give ROW_NUMBER 1-2-3-4, RANK 1-2-2-4 and DENSE_RANK 1-2-2-3.

Going deeper

  • Window functions were standardised in SQL:2003 (OLAP amendment to SQL:1999); MySQL gained them in 8.0 and SQLite in 3.25, both in 2018, so they are now available almost everywhere.
  • When ORDER BY is present but no frame is given, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all peers with the same sort value — so running totals jump across ties unless you specify ROWS.
  • LAST_VALUE surprises everyone: with the default frame it returns the current row's value; use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the partition's true last value.
  • Window functions are evaluated after WHERE, GROUP BY and HAVING, so they cannot appear in WHERE; filter in an outer query or with QUALIFY, supported by Snowflake, BigQuery, Databricks, DuckDB and Teradata but not PostgreSQL, MySQL or SQL Server.
  • Frames come in three units: ROWS counts physical rows, RANGE uses value distance (e.g. RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW), and GROUPS (SQL:2011) counts peer groups.
  • Engines usually implement windows by sorting each partition once; an index matching PARTITION BY and ORDER BY columns can avoid the sort, which matters on tables with hundreds of millions of rows.

Common mistakes & misconceptions

  • Trying to use a window function in WHERE — wrap the query in a CTE or subquery and filter outside.
  • Using ROW_NUMBER for 'top 3 per group' when ties should share places; RANK or DENSE_RANK may be what the business means.
  • Relying on the default RANGE frame for running totals when the ORDER BY column has duplicates.

Try it: Podium per age group and running totals (PostgreSQL, MySQL 8+, SQLite 3.25+, SQL Server)

WITH ranked AS (
  SELECT runner, age_group, finish_time,
         RANK() OVER (PARTITION BY age_group ORDER BY finish_time) AS pos
  FROM results WHERE race_id = 42
)
SELECT * FROM ranked WHERE pos <= 3;

SELECT runner_id, race_date, distance_km,
       SUM(distance_km) OVER (PARTITION BY runner_id ORDER BY race_date
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS season_km,
       finish_time - LAG(finish_time) OVER (PARTITION BY runner_id ORDER BY race_date) AS change
FROM results;

Key databases, concepts, languages, tools & standards

Name What it does Example
OVER clause
ISO SQL:2003 · Command · Current · Intermediate · Relational
Turns a function into a window function and defines its partition, order and frame. AVG(salary) OVER (PARTITION BY dept_id) shows each department's average on every row.
PARTITION BY
ISO SQL:2003 · Command · Current · Intermediate · Relational
Divides rows into independent windows, like groups that keep their individual rows. PARTITION BY age_group restarts numbering for each age group.
Window frame (ROWS / RANGE / GROUPS)
ISO SQL:2003; GROUPS in SQL:2011 · Concept · Current · Advanced · Relational
Defines which rows around the current row a window function considers. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives a 7-day moving average over daily rows.
ROW_NUMBER
ISO SQL:2003 · Command · Current · Intermediate · Relational
Gives each row a unique sequential number within its partition; ties get arbitrary different numbers. Keep each customer's latest order: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed DESC) = 1.
RANK
ISO SQL:2003 · Command · Current · Intermediate · Relational
Ranks rows, giving ties the same rank and leaving gaps after them. Scores 95, 90, 90, 85 rank 1, 2, 2, 4.
DENSE_RANK
ISO SQL:2003 · Command · Current · Intermediate · Relational
Ranks rows, giving ties the same rank without leaving gaps. Scores 95, 90, 90, 85 rank 1, 2, 2, 3 — ideal for 'second-highest salary'.
NTILE
ISO SQL:2003 · Command · Current · Intermediate · Relational
Splits ordered rows into n roughly equal buckets and returns the bucket number. NTILE(4) OVER (ORDER BY spend DESC) labels customers by spending quartile.
LAG and LEAD
ISO SQL:2003 · Command · Current · Intermediate · Relational
Return a value from a previous (LAG) or following (LEAD) row in the window. revenue - LAG(revenue) OVER (ORDER BY month) gives month-on-month change.
FIRST_VALUE / LAST_VALUE / NTH_VALUE
ISO SQL:2011 · Command · Current · Advanced · Relational
Return the first, last or nth value in the window frame. FIRST_VALUE(price) OVER (PARTITION BY sku ORDER BY changed_at) shows the launch price.
Running total
Window-function pattern · Pattern · Current · Intermediate · Relational
A cumulative sum computed with SUM() OVER an ordered window. SUM(amount) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING) gives revenue to date.
Moving average
Window-function pattern · Pattern · Current · Intermediate · Relational
An average over a sliding frame of recent rows, smoothing noisy series. AVG(visits) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
PERCENT_RANK and CUME_DIST
ISO SQL:2003 · Command · Current · Advanced · Relational
Give a row's relative position in its partition as a fraction from 0 to 1. CUME_DIST() = 0.9 means the student scored at or above 90% of the class.
QUALIFY
Snowflake, BigQuery, Databricks, DuckDB, Teradata (non-standard) · Command · Current · Intermediate · Analytical
Filters rows on window-function results directly, like HAVING for windows. SELECT * FROM results QUALIFY RANK() OVER (PARTITION BY age_group ORDER BY finish_time) <= 3;
WINDOW clause (named windows)
ISO SQL:2003; PostgreSQL, MySQL 8, SQLite, SQL Server 2022+ · Command · Current · Advanced · Relational
Defines a window once by name so several functions can reuse it. SELECT RANK() OVER w, LAG(t) OVER w FROM results WINDOW w AS (PARTITION BY grp ORDER BY t);

2.9 NULL and Three-Valued Logic

NULL is SQL's marker for a missing or unknown value. Because nobody knows what an unknown value is, comparing anything with NULL gives neither TRUE nor FALSE but a third result, UNKNOWN. WHERE keeps only rows whose condition is TRUE, so rows with UNKNOWN quietly disappear — the source of many bugs.

Three-valued truth tables

x AND y x OR y NOT x
x=T, y=U U T F
x=F, y=U F U T
x=U, y=U U U U
x=T, y=F F T F
x=U, y=T U T U

U = UNKNOWN; WHERE keeps a row only when the result is TRUE.

Think of it like…

NULL is a sealed envelope: asking 'is the amount inside equal to £10?' or even 'is it equal to another sealed envelope?' can only be answered 'don't know'.

Real-world example

The fictional charity Brightwell stores donors' phone numbers, but 3,000 of 20,000 are NULL. A query WHERE phone <> '07700900123' returns only 16,999 rows, not 19,999, because the 3,000 NULL rows evaluate to UNKNOWN. Worse, a 'donors not in the opt-out list' query using NOT IN returns zero rows because one opt-out record has a NULL donor_id.

How it works

  1. Any arithmetic or comparison with NULL yields NULL/UNKNOWN (5 + NULL is NULL; NULL = NULL is UNKNOWN).
  2. Logical operators follow three-valued truth tables (TRUE OR UNKNOWN = TRUE; FALSE AND UNKNOWN = FALSE).
  3. WHERE, HAVING and JOIN ON keep rows only when the result is TRUE.
  4. Test for NULL with IS NULL / IS NOT NULL, or compare null-safely with IS DISTINCT FROM.
  5. Replace NULLs for display or maths with COALESCE; prevent them with NOT NULL constraints.

Who uses it

Everyone writing SQL hits NULL bugs; data-quality engineers and analysts must account for NULLs in every count, average and join.

Key takeaway

Remember the trap: x NOT IN (1, 2, NULL) is never TRUE, so the query returns nothing — use NOT EXISTS for anti-joins.

Going deeper

  • Codd later proposed two kinds of missing value — 'applicable but unknown' and 'inapplicable' — but SQL has a single NULL, so a NULL middle name can mean 'unknown' or 'has none' and the schema cannot tell.
  • Aggregates skip NULLs (AVG of 10, NULL, 20 is 15, not 10), COUNT(*) counts the row anyway, and GROUP BY puts all NULLs into one group even though NULL = NULL is not TRUE.
  • Sorting differs by product: PostgreSQL and Oracle place NULLs last in ascending order, while MySQL, SQLite and SQL Server place them first; NULLS FIRST / NULLS LAST makes it explicit where supported.
  • UNIQUE constraints normally allow many NULLs because NULLs are not equal; SQL Server allows only one, and SQL:2023's UNIQUE NULLS NOT DISTINCT (PostgreSQL 15+) lets you choose.
  • Oracle treats the empty string '' as NULL, so WHERE name = '' never matches and LENGTH('') is NULL — a classic porting surprise.
  • Null-safe comparison is spelled differently everywhere: IS [NOT] DISTINCT FROM (standard; PostgreSQL, SQL Server 2022+), <=> in MySQL, IS / IS NOT in SQLite, DECODE tricks in older Oracle code.

Common mistakes & misconceptions

  • Writing = NULL or <> NULL instead of IS NULL / IS NOT NULL.
  • Assuming COUNT(column) equals COUNT(*) — it skips NULLs.
  • Using NOT IN with a subquery whose column can be NULL, which silently returns no rows.

Try it: See three-valued logic for yourself (PostgreSQL)

SELECT NULL = NULL          AS eq,        -- null (UNKNOWN)
       NULL IS NULL        AS is_null,   -- true
       1 NOT IN (2, NULL)  AS not_in,    -- null, not true!
       NULL IS DISTINCT FROM NULL AS dist, -- false
       COALESCE(NULL, 'n/a') AS shown,   -- 'n/a'
       5 + NULL            AS maths;     -- null

Key databases, concepts, languages, tools & standards

Name What it does Example
Three-valued logic (3VL)
ISO SQL · Concept · Current · Intermediate · Relational
SQL's logic with TRUE, FALSE and UNKNOWN, needed because comparisons with NULL are unknown. WHERE discount > 10 skips rows where discount is NULL because the test is UNKNOWN.
UNKNOWN
ISO SQL truth value · Concept · Current · Intermediate · Relational
The third truth value produced when a condition involves NULL; treated like FALSE by WHERE. NULL = NULL evaluates to UNKNOWN, not TRUE.
IS NULL / IS NOT NULL
ISO SQL · Command · Current · Beginner · Relational
The correct way to test whether a value is missing. SELECT * FROM donors WHERE phone IS NULL; finds donors without a phone.
IS DISTINCT FROM
ISO SQL:1999; PostgreSQL, SQL Server 2022+, DuckDB · Command · Current · Intermediate · Relational
A comparison that treats two NULLs as equal and NULL versus a value as different — never UNKNOWN. WHERE old_email IS DISTINCT FROM new_email detects changes even to or from NULL.
COALESCE
ISO SQL-92 · Command · Current · Beginner · Relational
Returns the first non-NULL argument, used to supply defaults. COALESCE(nickname, first_name, 'Friend') picks a greeting name.
NULLIF
ISO SQL-92 · Command · Current · Intermediate · Relational
Returns NULL if two values are equal, otherwise the first value; avoids divide-by-zero. revenue / NULLIF(visits, 0) returns NULL instead of an error when visits = 0.
ISNULL / IFNULL / NVL
SQL Server ISNULL; MySQL & SQLite IFNULL; Oracle NVL · Command · Current · Beginner · Relational
Vendor-specific two-argument versions of COALESCE. NVL(commission, 0) in Oracle; IFNULL(commission, 0) in MySQL.
NOT IN NULL trap
ISO SQL semantics · Concept · Current · Intermediate · Relational
If the list or subquery contains a NULL, NOT IN can never be TRUE, so no rows return. WHERE id NOT IN (SELECT donor_id FROM opt_outs) returns nothing if one donor_id is NULL.
NULLs in aggregates
ISO SQL semantics · Concept · Current · Intermediate · Relational
SUM, AVG, MIN, MAX and COUNT(column) ignore NULLs; SUM of no values is NULL. AVG over 10, NULL and 20 is 15.
NULLS FIRST / NULLS LAST
ISO SQL:2003; PostgreSQL, Oracle, SQLite 3.30+ · Command · Current · Intermediate · Relational
Controls where NULLs appear when sorting. ORDER BY last_login DESC NULLS LAST puts never-logged-in users at the bottom.
UNIQUE NULLS NOT DISTINCT
ISO SQL:2023; PostgreSQL 15+ · Feature · Emerging · Advanced · Relational
Makes a unique constraint treat NULLs as equal, so only one NULL is allowed. UNIQUE NULLS NOT DISTINCT (email) allows at most one account without an email.
Oracle empty string as NULL
Oracle Database behaviour · Concept · Current · Intermediate · Relational
Oracle stores a zero-length string as NULL, unlike PostgreSQL, SQL Server and MySQL. INSERT '' into an Oracle NOT NULL column fails with ORA-01400.
Outer-join NULLs
ISO SQL semantics · Concept · Current · Intermediate · Relational
NULLs created by outer joins for missing matches, distinct from NULLs stored in the table. LEFT JOIN orders … WHERE o.order_id IS NULL finds customers with no orders.

2.10 Constraints: Rules the Database Enforces

Constraints are rules declared on tables that the database checks on every insert and update, whoever makes the change. PRIMARY KEY and UNIQUE stop duplicates, FOREIGN KEY keeps links valid, NOT NULL forbids missing values, CHECK enforces conditions such as 'price is positive', and DEFAULT fills in a value when none is given.

Every write path passes through the same checks.
Constraints guarding the tickets table. Every write path passes through the same checks.

Think of it like…

Constraints are like the shape-sorter toy's holes: whatever hand pushes the block, a square never fits through the round hole.

Real-world example

The fictional ticketing site GigBay declares tickets(ticket_id PRIMARY KEY, event_id REFERENCES events, seat UNIQUE per event, price CHECK (price > 0), status DEFAULT 'reserved' NOT NULL). When a buggy batch job tries to create a ticket for event 9999 at price −5, the database rejects it, even though the job bypasses the website's validation entirely.

How it works

  1. Declare constraints in CREATE TABLE or add them later with ALTER TABLE.
  2. Give each a clear name (e.g. tickets_price_positive) so error messages are readable.
  3. On every INSERT or UPDATE the DBMS checks the affected rows (or at COMMIT if deferred).
  4. A violation raises an error and the statement is rolled back.
  5. Choose referential actions — what happens to child rows when a parent is deleted or updated.

Who uses it

Backend developers, data modellers and DBAs declare constraints; data engineers rely on them to trust the data they extract.

Key takeaway

Application validation improves user experience, but only database constraints protect data from every path — scripts, imports, other services and humans with SQL consoles.

Going deeper

  • Referential actions decide what happens to children when a parent changes: NO ACTION or RESTRICT (refuse), CASCADE (delete or update children too), SET NULL and SET DEFAULT; the default in standard SQL is NO ACTION.
  • Constraints declared DEFERRABLE INITIALLY DEFERRED (PostgreSQL, Oracle) are checked at COMMIT rather than per statement, allowing circular references or swapping two unique values inside one transaction.
  • MySQL parsed but ignored CHECK constraints until version 8.0.16 (2019); legacy schemas migrated from older MySQL may contain CHECKs that were never enforced.
  • PostgreSQL adds a foreign key or CHECK to a big table with NOT VALID and later VALIDATE CONSTRAINT, avoiding a long lock; SQL Server's equivalent is WITH NOCHECK followed by a re-check.
  • UNIQUE and PRIMARY KEY constraints create indexes automatically, but foreign keys do not in PostgreSQL or SQL Server — index the referencing column, or deleting a parent scans the whole child table.
  • PostgreSQL 18 added temporal constraints (PRIMARY KEY or UNIQUE … WITHOUT OVERLAPS and foreign keys with PERIOD) and made virtual generated columns the default kind; exclusion constraints stop overlapping bookings in older versions.

Common mistakes & misconceptions

  • Leaving constraints out 'for speed' — fixing corrupted data later costs far more than the checks.
  • Using ON DELETE CASCADE carelessly: deleting one customer can silently delete years of orders and invoices.
  • Forgetting to index foreign-key columns, making joins and parent deletes slow.

Try it: Constraints in action (PostgreSQL)

CREATE TABLE events  (event_id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE tickets (
  ticket_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_id  int  NOT NULL REFERENCES events ON DELETE CASCADE,
  seat      text NOT NULL,
  price     numeric(8,2) CONSTRAINT tickets_price_positive CHECK (price > 0),
  status    text NOT NULL DEFAULT 'reserved',
  CONSTRAINT tickets_one_per_seat UNIQUE (event_id, seat)
);
INSERT INTO events VALUES (1, 'Jazz Night');
INSERT INTO tickets(event_id, seat, price) VALUES (1, 'A1', 25);   -- ok
INSERT INTO tickets(event_id, seat, price) VALUES (1, 'A1', 25);   -- duplicate seat
INSERT INTO tickets(event_id, seat, price) VALUES (9999, 'B2', -5); -- FK + CHECK fail

Key databases, concepts, languages, tools & standards

Name What it does Example
PRIMARY KEY constraint
ISO SQL · Command · Current · Beginner · Relational
Declares the column(s) that uniquely identify each row; implies NOT NULL and creates a unique index. ticket_id bigint PRIMARY KEY
FOREIGN KEY (REFERENCES) constraint
ISO SQL · Command · Current · Beginner · Relational
Requires column values to match an existing key in the referenced table. event_id int REFERENCES events(event_id)
Referential actions
ISO SQL: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION · Feature · Current · Intermediate · Relational
Rules for child rows when a referenced parent row is deleted or its key updated. ON DELETE CASCADE removes a cancelled event's tickets automatically.
UNIQUE constraint
ISO SQL · Command · Current · Beginner · Relational
Forbids duplicate values (or combinations) in the listed columns. UNIQUE (event_id, seat) stops two tickets for seat A1 at the same gig.
CHECK constraint
ISO SQL; enforced by MySQL only since 8.0.16 · Command · Current · Beginner · Relational
A Boolean condition every row must satisfy. CHECK (end_date >= start_date) on a bookings table.
NOT NULL constraint
ISO SQL · Command · Current · Beginner · Relational
Forbids NULL in a column, so a value must always be supplied. email text NOT NULL on a users table.
DEFAULT value
ISO SQL · Command · Current · Beginner · Relational
The value used when an INSERT does not supply one for the column. created_at timestamptz NOT NULL DEFAULT now()
Identity column
ISO SQL:2003; PostgreSQL 10+, Oracle 12c+, SQL Server, Db2 · Feature · Current · Beginner · Relational
A column whose values are generated automatically from a sequence, typically for surrogate keys. id bigint GENERATED ALWAYS AS IDENTITY; MySQL uses AUTO_INCREMENT instead.
Generated column
ISO SQL:2003; PostgreSQL 12+, MySQL 5.7+, SQL Server computed columns · Feature · Current · Intermediate · Relational
A column computed from other columns, stored or calculated on read (virtual). total numeric GENERATED ALWAYS AS (qty * unit_price) STORED
Deferrable constraint
ISO SQL; PostgreSQL, Oracle · Feature · Current · Advanced · Relational
A constraint whose check can be postponed until the transaction commits. Swap two seats' unique numbers inside one transaction with a DEFERRABLE unique constraint.
Exclusion constraint
PostgreSQL · Feature · Current · Advanced · Relational
Ensures no two rows satisfy an operator comparison together, such as overlapping time ranges. EXCLUDE USING gist (room WITH =, during WITH &&) prevents double-booked rooms.
Temporal constraints (WITHOUT OVERLAPS)
ISO SQL:2011 application-time periods; PostgreSQL 18 · Feature · Emerging · Advanced · Relational
Keys that must be unique for any point in time, with foreign keys checked across periods. PRIMARY KEY (room_id, valid_period WITHOUT OVERLAPS) on a room-rates table.
NOT VALID / VALIDATE CONSTRAINT
PostgreSQL · Command · Current · Advanced · Relational
Adds a constraint for new rows immediately and checks existing rows later with a lighter lock. ALTER TABLE orders ADD CONSTRAINT fk_cust … NOT VALID; then VALIDATE CONSTRAINT fk_cust;
Constraint naming
Common convention · Pattern · Current · Beginner · Relational
Giving constraints explicit, descriptive names so errors and migrations are understandable. Error 'violates check constraint tickets_price_positive' beats 'tickets_check1'.

2.11 Views, Materialized Views, Procedures, Functions and Triggers

Databases can store more than tables. A view is a saved query that looks like a table; a materialized view also stores the query's result for speed. Stored procedures and user-defined functions are code kept inside the database and run on request, and triggers are code that runs automatically when rows are inserted, updated or deleted.

View vs materialized view

View Materialized view
Stores result rows No — query re-run Yes — on disk
Freshness Always current As of last refresh
Read speed Same as the query Like reading a table
Can be indexed No (SQL Server indexed views aside) Yes
Maintenance None REFRESH (full or incremental)
Typical use Security, simplicity Dashboards, heavy aggregates

Materialized views trade freshness for speed.

Think of it like…

A view is a window onto the data; a materialized view is a photograph of that window taken at a moment; a stored procedure is a recipe card the kitchen keeps; a trigger is a smoke alarm that reacts by itself when something happens.

Real-world example

At the fictional bank Northgate, a view customer_public hides date of birth and national-insurance numbers from the call-centre role. A materialized view of daily branch balances, refreshed every 15 minutes, makes the managers' dashboard load in 0.2 seconds instead of 40. A stored procedure transfer_funds performs the checked two-account update, and an AFTER UPDATE trigger writes every balance change to an audit table.

How it works

  1. Create a view with CREATE VIEW name AS SELECT …; query it like a table.
  2. For expensive queries, create a materialized view and schedule REFRESH.
  3. Package multi-step logic as a stored procedure (CALL) or reusable function (used inside SELECT).
  4. Attach triggers to tables for automatic actions such as auditing or derived columns.
  5. Grant users access to views and routines instead of the underlying tables.

Who uses it

DBAs and backend developers build views and routines; analysts use views as a stable interface; finance and regulated industries rely on trigger-based audit trails.

Key takeaway

A view stores no data and always shows current results; a materialized view stores data and is only as fresh as its last refresh — be ready to explain that trade-off.

Going deeper

  • Simple views over one table without aggregates are automatically updatable in PostgreSQL, MySQL and SQL Server; WITH CHECK OPTION then rejects inserts or updates that would produce rows the view cannot see.
  • PostgreSQL's REFRESH MATERIALIZED VIEW recomputes everything and, with CONCURRENTLY (needs a unique index), lets readers continue during refresh; Oracle supports incremental 'fast refresh' using materialized-view logs, and SQL Server keeps indexed views up to date synchronously.
  • MySQL has no materialized views; teams emulate them with summary tables refreshed by scheduled events or triggers, and extensions like pg_ivm offer incremental view maintenance for PostgreSQL.
  • PostgreSQL distinguishes functions (callable in queries, run inside the caller's transaction) from procedures (added in version 11, invoked with CALL, able to COMMIT inside); SQL Server and Oracle have long had both.
  • Triggers can fire BEFORE or AFTER an event, per row or per statement, and INSTEAD OF on views; heavy trigger logic hides work from developers and can make bulk loads dramatically slower.
  • SECURITY DEFINER routines (PostgreSQL) or EXECUTE AS (SQL Server) run with the owner's privileges, letting users perform a controlled action — such as a transfer — without direct table access; always set a safe search_path.

Common mistakes & misconceptions

  • Building views on views on views until no one can tune or understand the final query.
  • Treating a materialized view as live data; dashboards must show 'as of' times.
  • Putting business logic in hidden triggers that surprise developers and break bulk imports.

Try it: View, materialized view, function and trigger (PostgreSQL)

CREATE VIEW customer_public AS
  SELECT customer_id, name, city FROM customers;

CREATE MATERIALIZED VIEW branch_daily AS
  SELECT branch_id, date_trunc('day', at) AS day, SUM(amount) AS net
  FROM transactions GROUP BY 1, 2;
CREATE UNIQUE INDEX ON branch_daily (branch_id, day);
REFRESH MATERIALIZED VIEW CONCURRENTLY branch_daily;

CREATE FUNCTION log_balance() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO balance_audit(account_id, old_bal, new_bal, changed_at)
  VALUES (OLD.account_id, OLD.balance, NEW.balance, now());
  RETURN NEW;
END $$;
CREATE TRIGGER accounts_audit AFTER UPDATE OF balance ON accounts
  FOR EACH ROW EXECUTE FUNCTION log_balance();

Key databases, concepts, languages, tools & standards

Name What it does Example
View (CREATE VIEW)
ISO SQL · Command · Current · Beginner · Relational
A named, stored query that behaves like a virtual table and holds no data itself. CREATE VIEW active_customers AS SELECT * FROM customers WHERE closed_at IS NULL;
Updatable view
ISO SQL; PostgreSQL 9.3+, MySQL, SQL Server · Feature · Current · Intermediate · Relational
A simple view through which INSERT, UPDATE and DELETE pass to the underlying table. UPDATE active_customers SET city = 'York' WHERE customer_id = 7; changes customers.
WITH CHECK OPTION
ISO SQL · Command · Current · Advanced · Relational
Stops changes through a view that would create rows the view's WHERE clause excludes. A 'UK customers' view refuses an insert with country = 'FR'.
Materialized view
Oracle, PostgreSQL 9.3+, Snowflake, BigQuery, Db2 (MQT); not MySQL · Feature · Current · Intermediate · Relational
A view whose results are stored physically and refreshed on demand or schedule. A 40-second revenue query becomes a 0.2-second read from a materialized view.
REFRESH MATERIALIZED VIEW CONCURRENTLY
PostgreSQL 9.4+ · Command · Current · Advanced · Relational
Rebuilds a materialized view without blocking readers; requires a unique index on it. A cron job refreshes branch_daily every 15 minutes while dashboards keep reading.
Indexed view
Microsoft SQL Server · Feature · Current · Advanced · Relational
A view with a unique clustered index, stored and maintained automatically as base tables change. An indexed view keeps per-product sales totals always current.
Incremental view maintenance
Oracle fast refresh; pg_ivm extension; streaming databases · Concept · Emerging · Advanced · Relational
Updating a materialized result by applying only the changes rather than recomputing it. Inserting one sale adjusts one total in the summary instead of re-scanning 50 million rows.
Stored procedure (CREATE PROCEDURE / CALL)
ISO SQL/PSM; PostgreSQL 11+, SQL Server, Oracle, MySQL · Command · Current · Intermediate · Relational
Named server-side code invoked with CALL or EXEC, able to run many statements and control transactions. CALL transfer_funds(101, 202, 50.00);
User-defined function (UDF)
ISO SQL; every major RDBMS · Command · Current · Intermediate · Relational
Server-side code returning a value, usable inside SELECT, WHERE and other expressions. SELECT vat_inclusive(price) FROM products;
Table-valued function
SQL Server, PostgreSQL (RETURNS TABLE / SETOF), Oracle pipelined functions · Feature · Current · Advanced · Relational
A function that returns a set of rows and can be used in FROM like a table. SELECT * FROM orders_for_customer(42);
Trigger
ISO SQL:1999; every major RDBMS · Feature · Current · Intermediate · Relational
Code that runs automatically before or after INSERT, UPDATE or DELETE on a table. An AFTER UPDATE trigger writes old and new balances to an audit table.
INSTEAD OF trigger
SQL Server, PostgreSQL, Oracle · Feature · Current · Advanced · Relational
A trigger on a view that replaces the attempted change with custom logic. Inserting into a joined view splits the row into two base tables.
PL/pgSQL
PostgreSQL Global Development Group · Language · Current · Intermediate · Relational
PostgreSQL's built-in procedural language with variables, loops, conditions and exception handling. A PL/pgSQL trigger function validates and logs each balance change.
SECURITY DEFINER
PostgreSQL; EXECUTE AS in SQL Server; definer rights in Oracle · Feature · Current · Advanced · Relational
Makes a routine run with its owner's privileges rather than the caller's. Tellers call transfer_funds() but have no direct UPDATE right on accounts.

2.12 JSON in SQL

Modern relational databases can store JSON documents inside columns and query into them with SQL. This lets you keep flexible, nested data — such as product attributes that differ by category — next to normal columns, and still filter, index and join on it. The SQL standard added JSON functions in 2016 and a native JSON type in 2023.

JSON_TABLE turns nested arrays into rows you can join and group.
From a JSON column to relational rows. JSON_TABLE turns nested arrays into rows you can join and group.

Think of it like…

A JSON column is like a zipped folder attached to a spreadsheet row: the row has neat fixed columns, and the folder holds whatever extra papers each item needs, which SQL can open and read on request.

Real-world example

The fictional outdoor shop Ridgeway sells tents, boots and stoves, each with different specs. Products have fixed columns (sku, name, price) plus a specs JSON column, such as {"weight_g": 1450, "season": 3}. A query finds three-season tents under 2 kg with specs->>'season' = '3' and (specs->>'weight_g')::int < 2000, and a GIN index keeps it fast across 80,000 products.

How it works

  1. Store documents in a JSON-typed column (jsonb in PostgreSQL, JSON in MySQL, json in SQL Server 2025, JSON in Oracle).
  2. Extract values with operators or functions (->, ->>, JSON_VALUE, JSON_QUERY).
  3. Filter with containment (@>) or JSON path predicates.
  4. Index the paths you query: GIN on jsonb, or an index on an extracted or generated column.
  5. Turn JSON arrays into rows with JSON_TABLE for joins and aggregation; build JSON output with JSON_OBJECT and JSON_ARRAYAGG.

Who uses it

Backend developers storing API payloads and flexible attributes, data engineers landing semi-structured data, and analysts unpacking events all use SQL/JSON.

Key takeaway

Use JSON for genuinely variable attributes, not to avoid designing tables: values you filter, join or constrain often deserve real columns with types and constraints.

Going deeper

  • SQL:2016 (ISO/IEC 9075-2) introduced SQL/JSON — JSON_VALUE, JSON_QUERY, JSON_EXISTS, JSON_TABLE, constructors and the SQL/JSON path language — storing JSON as character strings; SQL:2023 added a native JSON data type and simplified dot-notation access.
  • PostgreSQL offers json (text, keeps formatting and duplicate keys) and jsonb (binary, since 9.4 in 2014, de-duplicated and indexable); jsonb is the usual choice. Version 16 added IS JSON and standard constructors, and 17 added JSON_TABLE, JSON_VALUE, JSON_QUERY and JSON_EXISTS.
  • MySQL has had a binary JSON type since 5.7.8 (2015) with -> and ->> operators and multi-valued indexes; SQL Server stored JSON in NVARCHAR until SQL Server 2025 added a native json type plus JSON_OBJECTAGG and JSON_ARRAYAGG.
  • Oracle added a native binary JSON type (OSON) in 21c, and 23ai introduced JSON Relational Duality views that expose relational tables as updatable JSON documents; SQLite has built-in JSON functions and, since 3.45 (2024), a binary JSONB format.
  • A GIN index on jsonb accelerates containment (@>) and key-existence queries; its jsonb_path_ops variant is smaller and faster for @> only, while B-tree indexes on expressions like ((specs->>'season')) suit equality on a single key.
  • JSON columns weaken guarantees: no per-key types, foreign keys or NOT NULL without extra CHECK constraints, and every document repeats its key names, often making storage larger than equivalent columns.

Common mistakes & misconceptions

  • Comparing JSON values as text: '10' < '9' alphabetically — cast extracted values to numeric types before comparing.
  • Confusing -> (returns JSON) with ->> (returns text) in PostgreSQL and MySQL.
  • Putting everything in one JSON blob, losing constraints, readable schemas and efficient indexes.

Try it: Query and index JSON (PostgreSQL 17+)

CREATE TABLE products (sku text PRIMARY KEY, name text, specs jsonb);
INSERT INTO products VALUES
 ('TENT-2','Ridge 2', '{"weight_g":1450,"season":3,"colours":["green","grey"]}'),
 ('TENT-4','Base 4',  '{"weight_g":4200,"season":4}');
CREATE INDEX ON products USING gin (specs);

SELECT name, specs->>'weight_g' AS grams
FROM products
WHERE specs @> '{"season":3}' AND (specs->>'weight_g')::int < 2000;

SELECT p.sku, c.colour
FROM products p,
     JSON_TABLE(p.specs, '$.colours[*]' COLUMNS (colour text PATH '$')) AS c;

Key databases, concepts, languages, tools & standards

Name What it does Example
JSON
ECMA-404 and IETF RFC 8259 · Format · Current · Beginner · Document
JavaScript Object Notation: a text format of objects, arrays, strings, numbers, booleans and null. {"weight_g": 1450, "season": 3, "colours": ["green", "grey"]}
json vs jsonb
PostgreSQL (jsonb since 9.4, 2014) · Feature · Current · Intermediate · Relational
json stores exact text; jsonb stores a parsed binary form that is faster to query and can be indexed. Most PostgreSQL apps declare specs jsonb, not json.
-> and ->> operators
PostgreSQL; MySQL 5.7+; SQLite 3.38+ · Command · Current · Intermediate · Relational
-> extracts a JSON value; ->> extracts it as plain text. specs->'colours' returns a JSON array; specs->>'season' returns the text '3'.
@> containment operator
PostgreSQL jsonb · Command · Current · Intermediate · Relational
Tests whether a JSON document contains another JSON fragment; supported by GIN indexes. WHERE specs @> '{"season": 4}' finds four-season products.
GIN index on jsonb
PostgreSQL (Generalized Inverted Index) · Data structure · Current · Advanced · Relational
An inverted index over JSON keys and values that makes containment and existence queries fast. CREATE INDEX ON products USING gin (specs jsonb_path_ops);
JSON_VALUE
ISO SQL:2016; PostgreSQL 17, Oracle, SQL Server, MySQL (8.0.21+) · Command · Current · Intermediate · Relational
Extracts a single scalar value from JSON using a path and returns it as an SQL type. JSON_VALUE(specs, '$.weight_g' RETURNING int)
JSON_QUERY
ISO SQL:2016; PostgreSQL 17, Oracle, SQL Server · Command · Current · Intermediate · Relational
Extracts an object or array (a JSON fragment) from a document. JSON_QUERY(specs, '$.colours') returns ["green","grey"].
JSON_EXISTS
ISO SQL:2016; PostgreSQL 17, Oracle · Command · Current · Intermediate · Relational
Tests whether a JSON path finds anything in a document. WHERE JSON_EXISTS(specs, '$.waterproof_rating')
JSON_TABLE
ISO SQL:2016; Oracle 12c+, MySQL 8.0, PostgreSQL 17 · Command · Current · Advanced · Relational
Turns JSON data into a relational table of rows and typed columns inside FROM. JSON_TABLE(order_doc, '$.lines[*]' COLUMNS (sku text PATH '$.sku', qty int PATH '$.qty'))
JSON_OBJECT and JSON_ARRAYAGG
ISO SQL:2016 constructors; PostgreSQL 16+, Oracle, MySQL, SQL Server 2025 · Command · Current · Intermediate · Relational
Build JSON objects from columns and aggregate rows into JSON arrays. SELECT JSON_ARRAYAGG(JSON_OBJECT('sku': sku, 'name': name)) FROM products; builds an API response.
SQL/JSON path language
ISO SQL:2016 · Language · Current · Advanced · Relational
A path syntax ($.a.b[*] with filters) for navigating JSON inside SQL functions. '$.items[*] ? (@.price > 100)' selects items costing over 100.
IS JSON predicate
ISO SQL:2016; PostgreSQL 16+, Oracle · Command · Current · Intermediate · Relational
Tests whether a text value is valid JSON, optionally an object, array or scalar. CHECK (payload IS JSON OBJECT) on a text column of webhook bodies.
Native JSON data type
ISO SQL:2023; MySQL, Oracle 21c+, SQL Server 2025 · Feature · Current · Intermediate · Relational
A dedicated column type storing parsed, validated JSON, usually in an efficient binary format. SQL Server 2025: CREATE TABLE events (id int, body json);
JSON Relational Duality
Oracle Database 23ai and later · Feature · Emerging · Advanced · Multi-model
Views exposing normalised relational tables as updatable JSON documents, and vice versa. A mobile app reads and writes order documents while reports query the same rows relationally.

2.13 The SQL Standard and Its Dialects

SQL is an international standard, ISO/IEC 9075, revised every few years; the current edition is SQL:2023. Every database implements a different subset plus its own extensions, so each has a dialect — PostgreSQL's SQL, MySQL's, Microsoft's Transact-SQL, Oracle's SQL with PL/SQL, and SQLite's. Core SELECT, JOIN and GROUP BY are portable; limits, dates, string functions and procedural code often are not.

Each edition added features; vendors adopt them years apart.
Editions of the SQL standard. Each edition added features; vendors adopt them years apart.

Think of it like…

SQL dialects are like English in Britain, America and Australia: everyone understands the basics, but 'boot', 'lift' and date formats trip you up when you move.

Real-world example

Developer Femi ports the fictional travel site Wayfarer from SQL Server to PostgreSQL. SELECT TOP 10 becomes LIMIT 10 (or FETCH FIRST 10 ROWS ONLY), GETDATE() becomes now(), ISNULL becomes COALESCE, string concatenation with + becomes ||, square-bracket identifiers become double quotes, and T-SQL procedures are rewritten in PL/pgSQL.

How it works

  1. Learn standard SQL first: SELECT, joins, GROUP BY, CTEs, window functions.
  2. Check your product's documentation for which standard features it supports.
  3. Note dialect differences: row limits, auto-numbering, dates, strings, booleans, quoting.
  4. Prefer standard syntax (FETCH FIRST, COALESCE, CAST, CASE) when it exists everywhere you need.
  5. Isolate vendor-specific code in a few places or let an ORM or tool such as SQLGlot translate.

Who uses it

Developers who switch databases, consultants, data engineers moving data between platforms, and tool builders writing cross-database software.

Key takeaway

Name the current edition (SQL:2023, published June 2023) and its two headline additions: property-graph queries (Part 16, SQL/PGQ) and a native JSON type.

Going deeper

  • ISO/IEC JTC 1/SC 32 Working Group 3 develops the standard in parts: 1 Framework, 2 Foundation (the core language), 4 PSM (procedures), 9 MED, 11 Schemata, 14 XML, 15 MDA and 16 PGQ.
  • Conformance is defined as 'Core SQL' plus optional numbered features (such as T611 for window functions); no mainstream product implements everything, and PostgreSQL publishes a feature-by-feature support table in its documentation.
  • SQL/PGQ (ISO/IEC 9075-16:2023) adds CREATE PROPERTY GRAPH over tables and GRAPH_TABLE pattern queries; Oracle 23ai was an early adopter, while PostgreSQL's implementation was pulled from the version 19 beta in September 2026.
  • GQL (ISO/IEC 39075:2024), published in April 2024, is a separate standalone graph query language sharing its pattern-matching syntax with SQL/PGQ; it was the first new ISO database language since SQL.
  • Common dialect differences: auto-numbering (IDENTITY vs AUTO_INCREMENT vs SERIAL), concatenation (|| vs + vs CONCAT), identifier quoting ("x" vs `x` vs [x]), Boolean types (Oracle added BOOLEAN only in 23ai; SQL Server uses bit) and date arithmetic.
  • Work on the next edition was under way in 2026, with committee drafts of Parts 1 and 2 balloted and vector data types among the proposed additions; corrigenda to the 2023 parts were published in 2026.

Common mistakes & misconceptions

  • Assuming code that works in one database runs unchanged in another — test on the target, especially dates, NULL handling and string functions.
  • Believing 'ANSI SQL' guarantees portability: every vendor claims it while supporting different feature sets.
  • Expecting new standard features to be available immediately — window functions (2003) only reached MySQL and SQLite in 2018.

Try it: The same 'top three' query in four dialects

-- Standard SQL / PostgreSQL / Oracle 12c+ / Db2
SELECT name FROM hotels ORDER BY rating DESC FETCH FIRST 3 ROWS ONLY;
-- PostgreSQL, MySQL, SQLite, DuckDB
SELECT name FROM hotels ORDER BY rating DESC LIMIT 3;
-- SQL Server (T-SQL)
SELECT TOP (3) name FROM hotels ORDER BY rating DESC;
-- Oracle before 12c
SELECT name FROM (SELECT name FROM hotels ORDER BY rating DESC) WHERE ROWNUM <= 3;

Key databases, concepts, languages, tools & standards

Name What it does Example
ISO/IEC 9075 (the SQL standard)
ISO/IEC JTC 1/SC 32/WG 3; ANSI INCITS · Standard · Current · Intermediate · Relational
The multi-part international standard defining SQL's syntax and behaviour; current edition SQL:2023. A university course teaches 'standard SQL' first, then each product's extras.
SQL-92
ISO/IEC 9075:1992 · Standard · Legacy · Intermediate · Relational
The large revision that brought explicit JOIN syntax, CASE, CAST, outer joins and isolation levels. LEFT JOIN … ON … syntax dates from SQL-92.
SQL:1999
ISO/IEC 9075:1999 · Standard · Current · Advanced · Relational
Added recursive queries, triggers, ROLLUP/CUBE, user-defined types, Boolean and the procedural PSM part. WITH RECURSIVE org charts rely on SQL:1999 features.
SQL:2003
ISO/IEC 9075:2003 · Standard · Current · Advanced · Relational
Added window functions, MERGE, identity and generated columns, sequences and SQL/XML. ROW_NUMBER() OVER (…) is a SQL:2003 feature.
SQL:2011
ISO/IEC 9075:2011 · Standard · Current · Advanced · Relational
Added temporal data: system-versioned tables and application-time periods. SQL Server and MariaDB system-versioned tables answer 'what did this row say last March?'.
SQL:2016
ISO/IEC 9075:2016 · Standard · Current · Advanced · Relational
Added SQL/JSON functions and path language, row pattern recognition (MATCH_RECOGNIZE) and polymorphic table functions. Oracle's MATCH_RECOGNIZE finds 'V-shaped' price patterns in stock data.
SQL:2023
ISO/IEC 9075:2023 (published June 2023) · Standard · Current · Intermediate · Relational
Current edition: adds property-graph queries (Part 16), a JSON type, ANY_VALUE, GREATEST/LEAST and UNIQUE NULLS options. PostgreSQL 16's ANY_VALUE() implements a SQL:2023 feature.
SQL/PGQ (Property Graph Queries)
ISO/IEC 9075-16:2023 · Standard · Emerging · Advanced · Graph
Defines property graphs over relational tables and graph pattern matching with GRAPH_TABLE. SELECT * FROM GRAPH_TABLE (bank MATCH (a)-[t IS transfer]->(b) COLUMNS (a.id, b.id));
GQL (ISO/IEC 39075:2024)
ISO/IEC JTC 1/SC 32/WG 3, April 2024 · Standard · Emerging · Advanced · Graph
A standalone ISO graph query language for property-graph databases, sharing pattern syntax with SQL/PGQ. MATCH (p:Person)-[:KNOWS]->(f) RETURN f.name in a GQL-compliant graph database.
Core SQL conformance
ISO/IEC 9075 feature taxonomy · Concept · Current · Advanced · Relational
The mandatory feature set of the standard; everything else is an optional, numbered feature. PostgreSQL's docs list which optional features (e.g. T611 window functions) it supports.
Transact-SQL (T-SQL)
Microsoft and Sybase · Language · Current · Intermediate · Relational
SQL Server's and Azure SQL's dialect with procedural extensions: variables, TRY/CATCH, TOP, OUTPUT. BEGIN TRY … END TRY BEGIN CATCH … END CATCH wraps a transfer in T-SQL.
PL/SQL
Oracle Corporation · Language · Current · Intermediate · Relational
Oracle's procedural language extending SQL with blocks, packages, cursors and exceptions; also supported by some compatible databases. A bank packages its interest calculations as PL/SQL procedures.
PostgreSQL SQL dialect
PostgreSQL Global Development Group · Language · Current · Intermediate · Relational
Close to the standard, with extras like ::casts, ILIKE, RETURNING, DISTINCT ON, arrays and rich types. SELECT DISTINCT ON (customer_id) * … returns each customer's latest order.
MySQL SQL dialect
Oracle Corporation (MySQL) · Language · Current · Intermediate · Relational
MySQL's SQL with backtick quoting, AUTO_INCREMENT, ON DUPLICATE KEY UPDATE and SQL modes changing strictness. INSERT … ON DUPLICATE KEY UPDATE hits = hits + 1;
SQLite SQL dialect
SQLite developers · Language · Current · Intermediate · Relational
Compact SQL with flexible type affinity (unless STRICT tables), PRAGMA commands and limited ALTER TABLE. CREATE TABLE t(x INT) STRICT; rejects text in x since SQLite 3.37.
SQLGlot
Open-source Python project (created by Toby Mao) · Library · Current · Advanced · Relational
A SQL parser and transpiler that converts queries between more than twenty dialects. sqlglot.transpile('SELECT TOP 3 * FROM t', read='tsql', write='postgres')

2.14 Common SQL Interview Problems

SQL interviews reuse a small set of classic problems: second-highest value, top-N per group, finding and removing duplicates, customers with no orders, running totals, month-over-month change, consecutive streaks and hierarchies. Each maps to a pattern you have met — window functions, anti-joins, GROUP BY with HAVING or recursive CTEs — so recognising the pattern is most of the battle.

Clarifying first prevents most wrong answers.
Solving a SQL question in an interview. Clarifying first prevents most wrong answers.

Think of it like…

Like chess openings, a few well-known patterns cover most games; once you recognise the position, you know the strong first moves.

Real-world example

Zainab interviews for an analyst role at the fictional ride-hailing firm Vela. She is asked for each city's top three drivers by October earnings. She clarifies how to treat ties, writes a CTE that sums earnings per driver and city, adds DENSE_RANK() OVER (PARTITION BY city ORDER BY earnings DESC), filters rank <= 3, and mentions NULL earnings and an index on (city, trip_date).

How it works

  1. Clarify: ties, NULLs, date ranges, expected output columns and order.
  2. Sketch tiny sample data and the result you expect.
  3. Recognise the pattern: ranking, anti-join, gaps-and-islands, aggregation, recursion.
  4. Build the answer in readable CTE steps and check each step's output mentally.
  5. Test edge cases aloud and discuss performance (indexes, data volume).

Who uses it

Candidates for data analyst, analytics engineer, data engineer, backend developer and data scientist roles meet these problems in live coding rounds and take-home tests.

Key takeaway

Talk while you write: state assumptions about ties and NULLs out loud — interviewers grade reasoning as much as the final query.

Going deeper

  • Gaps-and-islands problems (login streaks, consecutive bookings) are solved by subtracting ROW_NUMBER() from the date: rows in an unbroken run share the same difference, so GROUP BY that difference gives each streak's start, end and length.
  • Deleting duplicates while keeping one row usually uses ROW_NUMBER() OVER (PARTITION BY the duplicate columns ORDER BY id) and deletes rows numbered above 1; PostgreSQL can also compare ctid and MySQL can use a self-join DELETE.
  • Medians use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) in PostgreSQL, Oracle and SQL Server (as a window function there); MySQL has no built-in median, so candidates combine ROW_NUMBER with COUNT.
  • Pivoting rows into columns is portable with conditional aggregation — SUM(CASE WHEN month = 1 THEN amount END) AS jan — while SQL Server and Oracle also offer a PIVOT operator and PostgreSQL the crosstab() function from tablefunc.
  • Popular practice sites include LeetCode's SQL 50 study plan, HackerRank, StrataScratch, DataLemur and SQLZoo; interviewers often take questions straight from these collections.
  • Conceptual questions appear alongside coding: WHERE versus HAVING, DELETE versus TRUNCATE, UNION versus UNION ALL, inner versus left join, clustered versus non-clustered index, and what normalisation is.

Common mistakes & misconceptions

  • Using LIMIT 1 OFFSET 1 for 'second-highest salary' — it returns the duplicate top salary when two people tie; use DENSE_RANK or MAX below the MAX.
  • Forgetting date-range boundaries: BETWEEN '2026-10-01' AND '2026-10-31' misses times after midnight on the 31st for timestamp columns; use >= and <.
  • Jumping into code without asking how ties and NULLs should be handled.

Try it: Top three per group, duplicates and an anti-join (PostgreSQL)

-- top 3 drivers per city
WITH totals AS (
  SELECT city, driver_id, SUM(fare) AS earnings
  FROM trips WHERE trip_date >= '2026-10-01' AND trip_date < '2026-11-01'
  GROUP BY city, driver_id
)
SELECT * FROM (
  SELECT *, DENSE_RANK() OVER (PARTITION BY city ORDER BY earnings DESC) AS rnk
  FROM totals) t
WHERE rnk <= 3;

-- duplicate emails
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- riders who never took a trip
SELECT r.* FROM riders r
WHERE NOT EXISTS (SELECT 1 FROM trips t WHERE t.rider_id = r.rider_id);

Key databases, concepts, languages, tools & standards

Name What it does Example
Second-highest value
Classic interview problem · Pattern · Current · Beginner · Relational
Find the second-largest distinct value, handling ties and the case where none exists. SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);
Nth-highest with DENSE_RANK
Classic interview problem · Pattern · Current · Intermediate · Relational
Generalises 'second-highest' to any N using DENSE_RANK so ties share a position. Rank salaries with DENSE_RANK in a CTE, then keep rank 3 to find the third-highest pay.
Top-N per group
Classic interview problem · Pattern · Current · Intermediate · Relational
Return the best N rows in each category using a ranking window function and a filter. Top 3 drivers per city by earnings with DENSE_RANK partitioned by city.
Find duplicates
Classic interview problem · Pattern · Current · Beginner · Relational
Group by the columns that should be unique and keep groups with more than one row. SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
Delete duplicates keeping one
Classic interview problem · Pattern · Current · Intermediate · Relational
Number rows within each duplicate group and delete every row numbered above one. DELETE FROM users WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM users) d WHERE rn > 1);
Rows with no match
Classic interview problem · Pattern · Current · Beginner · Relational
Find parents lacking children using NOT EXISTS or LEFT JOIN … IS NULL, avoiding the NOT IN trap. Riders who never booked a trip.
Month-over-month change
Classic interview problem · Pattern · Current · Intermediate · Relational
Compare each period with the previous one using LAG over monthly aggregates. (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) gives growth.
Cumulative totals
Classic interview problem · Pattern · Current · Intermediate · Relational
Produce a running sum per group with SUM() OVER (PARTITION BY … ORDER BY …). Each customer's lifetime spend after every order.
Gaps and islands
Classic interview problem · Pattern · Current · Advanced · Relational
Group consecutive rows into runs ('islands') and find missing ranges ('gaps') between them. Longest daily-login streak: group by login_date minus ROW_NUMBER() days.
Median calculation
Classic interview problem · Pattern · Current · Advanced · Relational
Find the middle value with PERCENTILE_CONT or, where missing, ROW_NUMBER and COUNT. Median trip fare per city in PostgreSQL with PERCENTILE_CONT(0.5).
Pivot with conditional aggregation
Classic interview problem · Pattern · Current · Intermediate · Relational
Turn row values into columns using CASE inside aggregates. SUM(CASE WHEN quarter = 'Q1' THEN sales END) AS q1, … one column per quarter.
Employee-manager hierarchy
Classic interview problem · Pattern · Current · Intermediate · Relational
Use a self-join for direct managers, or a recursive CTE for every level of reports. List employees who earn more than their own manager with a self-join.
Retention and cohorts
Product-analytics interview problem · Pattern · Current · Advanced · Analytical
Group users by first-activity month and count how many return in later months. Of users who joined in January, 42% ordered again in February.
LeetCode SQL 50
LeetCode · Resource · Current · Beginner · Relational
A curated study plan of 50 SQL problems covering joins, aggregation, subqueries and window functions. A candidate completes SQL 50 over two weeks before applying for analyst roles.
SQLZoo and DataLemur
SQLZoo (free tutorial site); DataLemur (Nick Singh) · Resource · Current · Beginner · Relational
Browser-based SQL practice sites — SQLZoo for fundamentals, DataLemur for real company interview questions. A student works through SQLZoo's SELECT and JOIN tutorials, then DataLemur's easy problems.