SQL (PostgreSQL, MySQL, SQLite) Module 22 – SQL interview preparation

SQL theory questions and output prediction

Short, exact answers to classic SQL theory questions, and ten predict-the-output puzzles on NULLs, joins and aggregates, each run in three engines.

Intermediate
30 minutes
Examples run with sql.js 1.14.2 and Python 3.14.8
By MySmartCoPilot

Free: the whole lesson. With a pass: the model answer to its interview question.

What you will learn

Answer classic SQL theory questions in two or three exact sentences
Predict the output of queries with NULLs, duplicates, joins and empty inputs
Explain why an answer differs between SQLite, PostgreSQL and MySQL

Before you start

What you’ll learn · Before you start

What you will learn

Answer classic SQL theory questions in two or three exact sentences
Predict the output of queries with NULLs, duplicates, joins and empty inputs
Explain why an answer differs between SQLite, PostgreSQL and MySQL

Before you start

On this page

SQL theory questions ask you to say exactly what a feature does, and predict-the-output questions ask you to run a query in your head. Both reward the same habit: knowing the precise rule, including what happens with NULLs, duplicates and empty inputs, and saying which engine you mean when the engines disagree. A strong theory answer has three parts: a sentence that defines the thing, a sentence on the difference or consequence that matters, and a small example. A reliable way to predict an output is to walk the query in its logical order, from FROM and the joins to ORDER BY and LIMIT, and count the rows after each step. This lesson answers ten classic theory questions in that shape, then sets ten original predict-the-output puzzles. Each puzzle was run on SQLite 3.49.1 in the page, and all ten again on PostgreSQL 18.6 and MySQL 8.4.11, where two of them print something else.

Ten theory questions, answered briefly

Each answer below is the length an interviewer wants: two to four sentences, with the engine named where the answer depends on it, and a link to the lesson that goes deeper.

Keys: superkey, candidate key, primary key and foreign key

A superkey is any set of columns whose values differ in every row, and a candidate key is a minimal superkey: take away any one of its columns and uniqueness is no longer guaranteed. The primary key is the candidate key a table chooses as its identifier, which PostgreSQL’s manual defines as unique and not null together, at most one per table; the other candidate keys are alternate keys, declared UNIQUE and NOT NULL. A foreign key is a column, or a set of columns, whose values must match a row of the referenced table or be NULL. In orders(id, customer_id), id is the primary key and customer_id a foreign key to customers.

Normal forms: 1NF, 2NF, 3NF and BCNF

First normal form asks for one value from the column’s domain in every cell, with no lists or repeating groups, the property Codd’s relational model starts from. Second normal form adds that no non-key column depends on only part of a composite candidate key, and third that no non-key column depends on another non-key column. Boyce-Codd normal form is stricter: in every non-trivial functional dependency X → Y, X must be a superkey. A table (student, course, teacher) where each teacher teaches one course can be in 3NF but not in BCNF, because teacher determines course without being a key; the lesson on normalisation works through the decompositions.

ACID

A transaction is atomic: all of its changes happen or none do. It keeps the database consistent: every constraint holds when it commits. It is isolated: work that another transaction has not committed yet stays invisible to it, as far as the chosen isolation level guarantees. And it is durable: once committed, it survives a crash, because the commit is written to a log before it is acknowledged. PostgreSQL’s tutorial calls a transaction an all-or-nothing operation, and MySQL’s manual lists, for each letter, the InnoDB features that provide it.

Isolation levels and their defaults

The SQL standard defines four levels by the anomalies they allow: read uncommitted allows dirty reads, read committed prevents those but allows non-repeatable reads, repeatable read prevents those too but allows phantoms, and serializable allows none of them. The defaults differ: PostgreSQL uses read committed (and runs read uncommitted as read committed), InnoDB in MySQL uses repeatable read, and SQLite runs every transaction as serializable. Name the engine whenever you answer, because PostgreSQL’s repeatable read also prevents phantoms, which the standard does not require.

Index types

A B-tree keeps keys sorted, so it serves equality, ranges, ORDER BY and prefix matches, and it is the default in all three engines: PostgreSQL’s CREATE INDEX builds one unless told otherwise, InnoDB’s ordinary indexes are B-trees, and SQLite stores every table and index as a B-tree. PostgreSQL adds hash indexes for equality only, GIN for values with many parts such as arrays, JSON and full-text documents, GiST and SP-GiST for geometry and ranges, and BRIN for huge tables whose rows arrive in key order. A composite index helps queries that use its leading columns, which is why (city, placed_at) serves WHERE city = ? but not WHERE placed_at > ? on its own.

Clustered tables and heap tables

In a clustered table the rows live inside the primary-key index, in key order; in a heap the rows sit wherever there is room, and every index points to them. InnoDB clusters every table on its primary key, or else on its first unique index whose columns are all NOT NULL, or else on a hidden row id, so its secondary indexes store the clustering key. PostgreSQL tables are heaps, and its CLUSTER command sorts a table once without keeping it sorted. SQLite’s ordinary tables are B-trees keyed by the rowid, and a WITHOUT ROWID table is clustered on its declared primary key.

WHERE and HAVING

WHERE decides which input rows enter the grouping, so no aggregate exists yet when it runs; HAVING decides which finished groups are returned, so it may test count(*), sum() and the other aggregates. WHERE amount > 100 removes small orders before anything is counted, while HAVING count(*) > 100 keeps the customers with more than 100 orders. A condition on a grouped column can sit in either clause, but in WHERE it removes rows before the grouping work is done.

UNION and UNION ALL

UNION appends the second result to the first and removes duplicate rows, the way DISTINCT does; UNION ALL keeps every row. Removing duplicates needs a sort or a hash of the whole result, so UNION ALL is the right default when the two parts cannot overlap or when repeats must be counted. Both need the same number of columns in each part, matched by position, not by name.

Views and materialised views

A view is a stored query: reading it runs the query, so it always shows current data and stores no rows. A materialised view stores the result when it is created and when it is refreshed, so reads are fast but can be stale; in PostgreSQL, REFRESH MATERIALIZED VIEW updates it, and its CONCURRENTLY option needs a unique index. MySQL and SQLite have no materialised views; a summary table refreshed by a job plays that role there.

Correlated and uncorrelated subqueries

An uncorrelated subquery does not refer to the outer query, so the engine can run it once, as in WHERE id IN (SELECT customer_id FROM orders). A correlated subquery refers to a column of the outer query, as in WHERE amount > (SELECT avg(amount) FROM orders AS o2 WHERE o2.customer_id = o.customer_id), so logically it runs once per outer row. Optimisers can rewrite both kinds into joins, as MySQL’s manual describes for its semijoin transformations, so the plan, not the spelling, tells you what actually runs.

Predicting an output: walk the query in its logical order

Predicting an output, one logical step at a time

Text description of the diagram

The diagram lists the logical steps of a query from top to bottom, each with the rule to check when you predict what it returns.

FROM and JOIN: count the rows. A key that appears twice on the left and three times on the right gives six joined rows.
WHERE keeps only the rows for which the condition is true. A comparison with NULL is unknown, so those rows are dropped.
GROUP BY makes one row per group, and all the NULLs of a grouped column form one group together.
Aggregates and HAVING: count(*) counts rows, while count of a column and the other aggregates skip NULLs. With no input rows, an aggregate query without GROUP BY still returns one row, and with GROUP BY it returns none.
SELECT, DISTINCT and UNION: duplicate rows are removed, and here two NULLs count as equal.
ORDER BY and LIMIT: whether NULL sorts first or last depends on the engine and the direction of the sort.

Read the query in the order it is evaluated, not the order it is written, and write down the rows after each step. Most surprises come from four rules. A join multiplies matching rows, so duplicate keys on both sides multiply too. A comparison with NULL is neither true nor false, and WHERE keeps only the rows for which it is true. Aggregates skip NULLs, except count(*), which counts rows. And an aggregate over no rows still returns one row when there is no GROUP BY, but no rows when there is one. For ORDER BY, also ask the engine: NULL sorts first in ascending order in SQLite and MySQL, and last in PostgreSQL.

Ten predict-the-output puzzles

Each puzzle shows a short script; choose what it prints. The puzzles are original, and each answer and its reason appear once you have chosen. The runs follow the puzzles, with a short explanation of each.

Predict the output: ten puzzles

10 questions about this lesson. Every answer and why it is right is on the page, behind “Show the answer”. Your score stays in this browser.

Question 1 of 10 Puzzle 1. Two customers never ordered, and one order has no customer recorded. What does the script print?
What does this program print? Choose one answer.
```
-- Customers who never ordered, with one order whose customer is not recorded.
CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Bilal'), (3, 'Chitra');
CREATE TABLE orders (id INTEGER, customer_id INTEGER);
INSERT INTO orders VALUES (10, 1), (11, NULL);

SELECT count(*) AS never_ordered
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
```
┌───────────────┐ │ never_ordered │ ├───────────────┤ │ 3 │ └───────────────┘ ┌───────────────┐ │ never_ordered │ ├───────────────┤ │ 2 │ └───────────────┘ ┌───────────────┐ │ never_ordered │ ├───────────────┤ │ 0 │ └───────────────┘ ┌───────────────┐ │ never_ordered │ ├───────────────┤ │ 1 │ └───────────────┘
Show the answer to question 1
Answer: it prints
```
┌───────────────┐
│ never_ordered │
├───────────────┤
│ 0             │
└───────────────┘
```
NOT IN (1, NULL) is true only if the id differs from 1 and from NULL, and a comparison with NULL is unknown, so no row qualifies and the count is 0.
Question 2 of 10 Puzzle 2. What do the three counts return?
What does this program print? Choose one answer.
```
-- Five rows: two in Pune, one in Kochi and two without a city.
CREATE TABLE visits (city TEXT);
INSERT INTO visits VALUES ('Pune'), ('Pune'), (NULL), ('Kochi'), (NULL);

SELECT count(*) AS all_rows, count(city) AS with_city, count(DISTINCT city) AS cities
FROM visits;
```
┌──────────┬───────────┬────────┐ │ all_rows │ with_city │ cities │ ├──────────┼───────────┼────────┤ │ 5 │ 5 │ 4 │ └──────────┴───────────┴────────┘ ┌──────────┬───────────┬────────┐ │ all_rows │ with_city │ cities │ ├──────────┼───────────┼────────┤ │ 5 │ 3 │ 3 │ └──────────┴───────────┴────────┘ ┌──────────┬───────────┬────────┐ │ all_rows │ with_city │ cities │ ├──────────┼───────────┼────────┤ │ 3 │ 3 │ 2 │ └──────────┴───────────┴────────┘ ┌──────────┬───────────┬────────┐ │ all_rows │ with_city │ cities │ ├──────────┼───────────┼────────┤ │ 5 │ 3 │ 2 │ └──────────┴───────────┴────────┘
Show the answer to question 2
Answer: it prints
```
┌──────────┬───────────┬────────┐
│ all_rows │ with_city │ cities │
├──────────┼───────────┼────────┤
│ 5        │ 3         │ 2      │
└──────────┴───────────┴────────┘
```
count(*) counts all 5 rows, count(city) skips the two NULLs, and count(DISTINCT city) counts Pune and Kochi: 5, 3 and 2.
Question 3 of 10 Puzzle 3. How many rows does the inner join return?
What does this program print? Choose one answer.
```
-- The key 1 appears twice on the left and three times on the right.
CREATE TABLE lefty (k INTEGER);
INSERT INTO lefty VALUES (1), (1), (2);
CREATE TABLE righty (k INTEGER);
INSERT INTO righty VALUES (1), (1), (1), (3);

SELECT count(*) AS joined_rows
FROM lefty JOIN righty ON lefty.k = righty.k;
```
┌─────────────┐ │ joined_rows │ ├─────────────┤ │ 5 │ └─────────────┘ ┌─────────────┐ │ joined_rows │ ├─────────────┤ │ 6 │ └─────────────┘ ┌─────────────┐ │ joined_rows │ ├─────────────┤ │ 3 │ └─────────────┘ ┌─────────────┐ │ joined_rows │ ├─────────────┤ │ 4 │ └─────────────┘
Show the answer to question 3
Answer: it prints
```
┌─────────────┐
│ joined_rows │
├─────────────┤
│ 6           │
└─────────────┘
```
Two left rows with key 1 each meet three right rows with key 1, so 2 × 3 = 6 rows; keys 2 and 3 have no partner.
Question 4 of 10 Puzzle 4. The same LEFT JOIN, with the status test in ON and then in WHERE. What does it print?
What does this program print? Choose one answer.
```
-- Three customers; Asha has a paid and a refunded order, Bilal a refunded one, Chitra none.
CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Bilal'), (3, 'Chitra');
CREATE TABLE orders (id INTEGER, customer_id INTEGER, status TEXT);
INSERT INTO orders VALUES (10, 1, 'paid'), (11, 1, 'refunded'), (12, 2, 'refunded');

SELECT
  (SELECT count(*) FROM customers AS c
     LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid') AS filter_in_on,
  (SELECT count(*) FROM customers AS c
     LEFT JOIN orders AS o ON o.customer_id = c.id
   WHERE o.status = 'paid') AS filter_in_where;
```
┌──────────────┬─────────────────┐ │ filter_in_on │ filter_in_where │ ├──────────────┼─────────────────┤ │ 3 │ 3 │ └──────────────┴─────────────────┘ ┌──────────────┬─────────────────┐ │ filter_in_on │ filter_in_where │ ├──────────────┼─────────────────┤ │ 3 │ 1 │ └──────────────┴─────────────────┘ ┌──────────────┬─────────────────┐ │ filter_in_on │ filter_in_where │ ├──────────────┼─────────────────┤ │ 1 │ 1 │ └──────────────┴─────────────────┘ ┌──────────────┬─────────────────┐ │ filter_in_on │ filter_in_where │ ├──────────────┼─────────────────┤ │ 4 │ 1 │ └──────────────┴─────────────────┘
Show the answer to question 4
Answer: it prints
```
┌──────────────┬─────────────────┐
│ filter_in_on │ filter_in_where │
├──────────────┼─────────────────┤
│ 3            │ 1               │
└──────────────┴─────────────────┘
```
In ON, the test only decides which orders match, so every customer keeps a row: 3. In WHERE, it runs after the join and removes the rows with NULL order columns, leaving Asha's paid order: 1.
Question 5 of 10 Puzzle 5. What do the three counts return?
What does this program print? Choose one answer.
```
-- Two orders without a coupon and one with the coupon NEW10.
CREATE TABLE carts (id INTEGER, coupon TEXT);
INSERT INTO carts VALUES (1, NULL), (2, 'NEW10'), (3, NULL);

SELECT
  (SELECT count(*) FROM carts WHERE coupon = NULL)     AS equals_null,
  (SELECT count(*) FROM carts WHERE coupon IS NULL)    AS is_null,
  (SELECT count(*) FROM carts WHERE coupon <> 'NEW10') AS not_new10;
```
┌─────────────┬─────────┬───────────┐ │ equals_null │ is_null │ not_new10 │ ├─────────────┼─────────┼───────────┤ │ 0 │ 2 │ 2 │ └─────────────┴─────────┴───────────┘ ┌─────────────┬─────────┬───────────┐ │ equals_null │ is_null │ not_new10 │ ├─────────────┼─────────┼───────────┤ │ 0 │ 2 │ 0 │ └─────────────┴─────────┴───────────┘ ┌─────────────┬─────────┬───────────┐ │ equals_null │ is_null │ not_new10 │ ├─────────────┼─────────┼───────────┤ │ 2 │ 2 │ 0 │ └─────────────┴─────────┴───────────┘ ┌─────────────┬─────────┬───────────┐ │ equals_null │ is_null │ not_new10 │ ├─────────────┼─────────┼───────────┤ │ 2 │ 2 │ 2 │ └─────────────┴─────────┴───────────┘
Show the answer to question 5
Answer: it prints
```
┌─────────────┬─────────┬───────────┐
│ equals_null │ is_null │ not_new10 │
├─────────────┼─────────┼───────────┤
│ 0           │ 2       │ 0         │
└─────────────┴─────────┴───────────┘
```
= NULL and <> on a NULL coupon are both unknown, so they count nothing; only IS NULL finds the 2 carts without a coupon, and the only coupon present is NEW10 itself.
Question 6 of 10 Puzzle 6. How many rows does each set operation keep?
What does this program print? Choose one answer.
```
-- Rows kept by UNION and by UNION ALL, and by UNION of two NULLs.
SELECT
  (SELECT count(*) FROM (SELECT 1 AS x UNION SELECT 1 UNION SELECT 2) AS s)             AS with_union,
  (SELECT count(*) FROM (SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 2) AS s)     AS with_union_all,
  (SELECT count(*) FROM (SELECT NULL AS x UNION SELECT NULL) AS s)                       AS null_rows;
```
┌────────────┬────────────────┬───────────┐ │ with_union │ with_union_all │ null_rows │ ├────────────┼────────────────┼───────────┤ │ 2 │ 2 │ 1 │ └────────────┴────────────────┴───────────┘ ┌────────────┬────────────────┬───────────┐ │ with_union │ with_union_all │ null_rows │ ├────────────┼────────────────┼───────────┤ │ 2 │ 3 │ 2 │ └────────────┴────────────────┴───────────┘ ┌────────────┬────────────────┬───────────┐ │ with_union │ with_union_all │ null_rows │ ├────────────┼────────────────┼───────────┤ │ 3 │ 3 │ 2 │ └────────────┴────────────────┴───────────┘ ┌────────────┬────────────────┬───────────┐ │ with_union │ with_union_all │ null_rows │ ├────────────┼────────────────┼───────────┤ │ 2 │ 3 │ 1 │ └────────────┴────────────────┴───────────┘
Show the answer to question 6
Answer: it prints
```
┌────────────┬────────────────┬───────────┐
│ with_union │ with_union_all │ null_rows │
├────────────┼────────────────┼───────────┤
│ 2          │ 3              │ 1         │
└────────────┴────────────────┴───────────┘
```
UNION removes the repeated 1, UNION ALL keeps all three rows, and for removing duplicates two NULLs count as the same row: 2, 3 and 1.
Question 7 of 10 Puzzle 7. The ratings are stored as integers. What does SQLite print?
What does this program print? Choose one answer.
```
-- Two ratings, 4 and 5 stars, stored as integers.
CREATE TABLE ratings (stars INTEGER);
INSERT INTO ratings VALUES (4), (5);

SELECT 9 / 2 AS nine_halves, avg(stars) AS avg_stars, sum(stars) / count(*) AS sum_over_count
FROM ratings;
```
┌─────────────┬───────────┬────────────────┐ │ nine_halves │ avg_stars │ sum_over_count │ ├─────────────┼───────────┼────────────────┤ │ 4 │ 4.5 │ 4 │ └─────────────┴───────────┴────────────────┘ ┌─────────────┬───────────┬────────────────┐ │ nine_halves │ avg_stars │ sum_over_count │ ├─────────────┼───────────┼────────────────┤ │ 4.5 │ 4.5 │ 4 │ └─────────────┴───────────┴────────────────┘ ┌─────────────┬───────────┬────────────────┐ │ nine_halves │ avg_stars │ sum_over_count │ ├─────────────┼───────────┼────────────────┤ │ 4 │ 4 │ 4 │ └─────────────┴───────────┴────────────────┘ ┌─────────────┬───────────┬────────────────┐ │ nine_halves │ avg_stars │ sum_over_count │ ├─────────────┼───────────┼────────────────┤ │ 4.5 │ 4.5 │ 4.5 │ └─────────────┴───────────┴────────────────┘
Show the answer to question 7
Answer: it prints
```
┌─────────────┬───────────┬────────────────┐
│ nine_halves │ avg_stars │ sum_over_count │
├─────────────┼───────────┼────────────────┤
│ 4           │ 4.5       │ 4              │
└─────────────┴───────────┴────────────────┘
```
SQLite divides two integers as integers, so 9 / 2 and sum / count are both 4, while avg() returns the floating-point 4.5.
Question 8 of 10 Puzzle 8. No order is above 1000. What do the two queries print, one after the other?
What does this program print? Choose one answer.
```
-- No order is above 1000, so both queries read no rows.
CREATE TABLE orders (city TEXT, amount INTEGER);
INSERT INTO orders VALUES ('Pune', 400), ('Kochi', 650);

SELECT count(*) AS orders, sum(amount) AS total, coalesce(sum(amount), 0) AS total_or_zero
FROM orders
WHERE amount > 1000;

SELECT city, count(*) AS orders
FROM orders
WHERE amount > 1000
GROUP BY city;
```
┌────────┬───────┬───────────────┐ │ orders │ total │ total_or_zero │ ├────────┼───────┼───────────────┤ │ 0 │ 0 │ 0 │ └────────┴───────┴───────────────┘ ┌──────┬────────┐ │ city │ orders │ ├──────┼────────┤ │ │ 0 │ └──────┴────────┘ ┌────────┬───────┬───────────────┐ │ orders │ total │ total_or_zero │ ├────────┼───────┼───────────────┤ │ 0 │ │ 0 │ └────────┴───────┴───────────────┘ ┌────────┬───────┬───────────────┐ │ orders │ total │ total_or_zero │ ├────────┼───────┼───────────────┤ │ 0 │ 0 │ 0 │ └────────┴───────┴───────────────┘ ┌────────┬───────┬───────────────┐ │ orders │ total │ total_or_zero │ ├────────┼───────┼───────────────┤ │ 0 │ │ 0 │ └────────┴───────┴───────────────┘ ┌──────┬────────┐ │ city │ orders │ ├──────┼────────┤ │ │ 0 │ └──────┴────────┘
Show the answer to question 8
Answer: it prints
```
┌────────┬───────┬───────────────┐
│ orders │ total │ total_or_zero │
├────────┼───────┼───────────────┤
│ 0      │       │ 0             │
└────────┴───────┴───────────────┘
```
Without GROUP BY, the aggregate query returns one row even from no input: count(*) is 0 and sum() is NULL, which coalesce() turns into 0. With GROUP BY there is no group, so the second query prints nothing.
Question 9 of 10 Puzzle 9. Bilal has no score yet. What does SQLite print?
What does this program print? Choose one answer.
```
-- Four players; Bilal has no score yet.
CREATE TABLE players (name TEXT, score INTEGER);
INSERT INTO players VALUES ('Asha', 70), ('Bilal', NULL), ('Chitra', 90), ('Dev', 70);

SELECT name, score
FROM players
ORDER BY score, name
LIMIT 2;
```
┌────────┬───────┐ │ name │ score │ ├────────┼───────┤ │ Chitra │ 90 │ │ Asha │ 70 │ └────────┴───────┘ ┌──────┬───────┐ │ name │ score │ ├──────┼───────┤ │ Asha │ 70 │ │ Dev │ 70 │ └──────┴───────┘ ┌───────┬───────┐ │ name │ score │ ├───────┼───────┤ │ Bilal │ │ │ Asha │ 70 │ └───────┴───────┘ ┌───────┬───────┐ │ name │ score │ ├───────┼───────┤ │ Asha │ 70 │ │ Bilal │ │ └───────┴───────┘
Show the answer to question 9
Answer: it prints
```
┌───────┬───────┐
│ name  │ score │
├───────┼───────┤
│ Bilal │       │
│ Asha  │ 70    │
└───────┴───────┘
```
SQLite sorts NULL below every value, so in ascending order Bilal comes first, then Asha, who comes before Dev by name. PostgreSQL would print Asha and Dev.
Question 10 of 10 Puzzle 10. The email column is UNIQUE, and two rows have no email. What does the script print?
What does this program print? Choose one answer.
```
-- A UNIQUE column, with one address and two rows without one.
CREATE TABLE subscribers (email VARCHAR(100) UNIQUE);
INSERT INTO subscribers VALUES ('asha@example.in'), (NULL), (NULL);

SELECT count(*) AS stored_rows,
       count(DISTINCT email) AS distinct_emails,
       (SELECT count(*) FROM (SELECT DISTINCT email FROM subscribers) AS d) AS distinct_rows
FROM subscribers;
```
┌─────────────┬─────────────────┬───────────────┐ │ stored_rows │ distinct_emails │ distinct_rows │ ├─────────────┼─────────────────┼───────────────┤ │ 2 │ 1 │ 2 │ └─────────────┴─────────────────┴───────────────┘ ┌─────────────┬─────────────────┬───────────────┐ │ stored_rows │ distinct_emails │ distinct_rows │ ├─────────────┼─────────────────┼───────────────┤ │ 3 │ 2 │ 3 │ └─────────────┴─────────────────┴───────────────┘ ┌─────────────┬─────────────────┬───────────────┐ │ stored_rows │ distinct_emails │ distinct_rows │ ├─────────────┼─────────────────┼───────────────┤ │ 3 │ 1 │ 1 │ └─────────────┴─────────────────┴───────────────┘ ┌─────────────┬─────────────────┬───────────────┐ │ stored_rows │ distinct_emails │ distinct_rows │ ├─────────────┼─────────────────┼───────────────┤ │ 3 │ 1 │ 2 │ └─────────────┴─────────────────┴───────────────┘
Show the answer to question 10
Answer: it prints
```
┌─────────────┬─────────────────┬───────────────┐
│ stored_rows │ distinct_emails │ distinct_rows │
├─────────────┼─────────────────┼───────────────┤
│ 3           │ 1               │ 2             │
└─────────────┴─────────────────┴───────────────┘
```
UNIQUE does not compare NULLs, so all 3 rows are stored. count(DISTINCT email) ignores the NULLs and counts 1, while SELECT DISTINCT keeps one NULL row beside the address: 2 rows.

The ten runs, explained

Puzzle 1: NOT IN with a NULL in the subquery SQL · sqlite/p01_not_in_null.sql

-- Customers who never ordered, with one order whose customer is not recorded.
CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Bilal'), (3, 'Chitra');
CREATE TABLE orders (id INTEGER, customer_id INTEGER);
INSERT INTO orders VALUES (10, 1), (11, NULL);

SELECT count(*) AS never_ordered
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Output

┌───────────────┐
│ never_ordered │
├───────────────┤
│ 0             │
└───────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < p01_not_in_null.sql

No customer is returned, although Bilal and Chitra never ordered. id NOT IN (1, NULL) means id <> 1 AND id <> NULL, and the second comparison is unknown for every id, so the condition is never true. NOT EXISTS, or adding WHERE customer_id IS NOT NULL to the subquery, returns the two customers.

Puzzle 2: three ways to count SQL · sqlite/p02_count_three_ways.sql

-- Five rows: two in Pune, one in Kochi and two without a city.
CREATE TABLE visits (city TEXT);
INSERT INTO visits VALUES ('Pune'), ('Pune'), (NULL), ('Kochi'), (NULL);

SELECT count(*) AS all_rows, count(city) AS with_city, count(DISTINCT city) AS cities
FROM visits;

Output

┌──────────┬───────────┬────────┐
│ all_rows │ with_city │ cities │
├──────────┼───────────┼────────┤
│ 5        │ 3         │ 2      │
└──────────┴───────────┴────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < p02_count_three_ways.sql

count(*) counts the 5 rows, count(city) the 3 that have a city, and count(DISTINCT city) the 2 different cities: NULL is not a value, so it is neither counted nor counted as distinct.

Puzzle 3: a join with duplicate keys SQL · sqlite/p03_join_duplicates.sql

-- The key 1 appears twice on the left and three times on the right.
CREATE TABLE lefty (k INTEGER);
INSERT INTO lefty VALUES (1), (1), (2);
CREATE TABLE righty (k INTEGER);
INSERT INTO righty VALUES (1), (1), (1), (3);

SELECT count(*) AS joined_rows
FROM lefty JOIN righty ON lefty.k = righty.k;

Output

┌─────────────┐
│ joined_rows │
├─────────────┤
│ 6           │
└─────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < p03_join_duplicates.sql

Each of the two left rows with key 1 matches each of the three right rows with key 1, so the join returns 2 × 3 = 6 rows; keys 2 and 3 match nothing. This is the fan-out that makes a SUM over a join too large.

Puzzle 4: a filter in ON or in WHERE SQL · sqlite/p04_left_join_filter.sql

-- Three customers; Asha has a paid and a refunded order, Bilal a refunded one, Chitra none.
CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Bilal'), (3, 'Chitra');
CREATE TABLE orders (id INTEGER, customer_id INTEGER, status TEXT);
INSERT INTO orders VALUES (10, 1, 'paid'), (11, 1, 'refunded'), (12, 2, 'refunded');

SELECT
  (SELECT count(*) FROM customers AS c
     LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid') AS filter_in_on,
  (SELECT count(*) FROM customers AS c
     LEFT JOIN orders AS o ON o.customer_id = c.id
   WHERE o.status = 'paid') AS filter_in_where;

Output

┌──────────────┬─────────────────┐
│ filter_in_on │ filter_in_where │
├──────────────┼─────────────────┤
│ 3            │ 1               │
└──────────────┴─────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < p04_left_join_filter.sql

With the status test in ON, the outer join keeps every customer: Asha with her paid order, and Bilal and Chitra with NULLs on the order side, 3 rows. In WHERE, the test runs after the join, and the NULL rows fail it, so only Asha’s paid order remains and the outer join has quietly become an inner one.

Puzzle 5: = NULL, IS NULL and <> SQL · sqlite/p05_equals_null.sql

-- Two orders without a coupon and one with the coupon NEW10.
CREATE TABLE carts (id INTEGER, coupon TEXT);
INSERT INTO carts VALUES (1, NULL), (2, 'NEW10'), (3, NULL);

SELECT
  (SELECT count(*) FROM carts WHERE coupon = NULL)     AS equals_null,
  (SELECT count(*) FROM carts WHERE coupon IS NULL)    AS is_null,
  (SELECT count(*) FROM carts WHERE coupon <> 'NEW10') AS not_new10;

Output

┌─────────────┬─────────┬───────────┐
│ equals_null │ is_null │ not_new10 │
├─────────────┼─────────┼───────────┤
│ 0           │ 2       │ 0         │
└─────────────┴─────────┴───────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < p05_equals_null.sql

coupon = NULL is never true, so it counts 0 rows; IS NULL counts the 2 carts without a coupon. coupon <> 'NEW10' also counts 0: the two NULL coupons make the comparison unknown, and the only coupon present is NEW10 itself.

Puzzle 6: UNION, UNION ALL and two NULLs SQL · sqlite/p06_union_counts.sql

-- Rows kept by UNION and by UNION ALL, and by UNION of two NULLs.
SELECT
  (SELECT count(*) FROM (SELECT 1 AS x UNION SELECT 1 UNION SELECT 2) AS s)             AS with_union,
  (SELECT count(*) FROM (SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 2) AS s)     AS with_union_all,
  (SELECT count(*) FROM (SELECT NULL AS x UNION SELECT NULL) AS s)                       AS null_rows;

Output

┌────────────┬────────────────┬───────────┐
│ with_union │ with_union_all │ null_rows │
├────────────┼────────────────┼───────────┤
│ 2          │ 3              │ 1         │
└────────────┴────────────────┴───────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < p06_union_counts.sql

UNION keeps the 2 distinct values, UNION ALL all 3 rows, and the UNION of two NULLs keeps 1 row. For removing duplicates, two NULLs count as the same, even though NULL = NULL is not true; DISTINCT and GROUP BY treat them the same way.

Puzzle 7: integer division and avg() SQL · sqlite/p07_integer_average.sql

-- Two ratings, 4 and 5 stars, stored as integers.
CREATE TABLE ratings (stars INTEGER);
INSERT INTO ratings VALUES (4), (5);

SELECT 9 / 2 AS nine_halves, avg(stars) AS avg_stars, sum(stars) / count(*) AS sum_over_count
FROM ratings;

Output

┌─────────────┬───────────┬────────────────┐
│ nine_halves │ avg_stars │ sum_over_count │
├─────────────┼───────────┼────────────────┤
│ 4           │ 4.5       │ 4              │
└─────────────┴───────────┴────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < p07_integer_average.sql

SQLite divides two integers as integers, so 9 / 2 is 4, and so is sum(stars) / count(*), the “average” written by hand. avg() returns a floating-point value, 4.5. In MySQL all three are 4.5000, because its / divides two integers exactly and shows four decimal places by default.

Puzzle 8: aggregates over no rows SQL · sqlite/p08_aggregates_no_rows.sql

-- No order is above 1000, so both queries read no rows.
CREATE TABLE orders (city TEXT, amount INTEGER);
INSERT INTO orders VALUES ('Pune', 400), ('Kochi', 650);

SELECT count(*) AS orders, sum(amount) AS total, coalesce(sum(amount), 0) AS total_or_zero
FROM orders
WHERE amount > 1000;

SELECT city, count(*) AS orders
FROM orders
WHERE amount > 1000
GROUP BY city;

Output

┌────────┬───────┬───────────────┐
│ orders │ total │ total_or_zero │
├────────┼───────┼───────────────┤
│ 0      │       │ 0             │
└────────┴───────┴───────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < p08_aggregates_no_rows.sql

The first query has no GROUP BY, so it returns one row even though no order matches: count(*) is 0, sum() is NULL, shown as an empty cell, and coalesce() turns it into 0. The second query groups by city, and there is no group to show, so it returns no rows at all and prints nothing.

Puzzle 9: where the NULL sorts SQL · sqlite/p09_null_sort.sql

-- Four players; Bilal has no score yet.
CREATE TABLE players (name TEXT, score INTEGER);
INSERT INTO players VALUES ('Asha', 70), ('Bilal', NULL), ('Chitra', 90), ('Dev', 70);

SELECT name, score
FROM players
ORDER BY score, name
LIMIT 2;

Output

┌───────┬───────┐
│ name  │ score │
├───────┼───────┤
│ Bilal │       │
│ Asha  │ 70    │
└───────┴───────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < p09_null_sort.sql

SQLite treats NULL as smaller than any value, so in ascending order Bilal comes first and Asha second, and name breaks the tie between Asha and Dev. PostgreSQL sorts NULL as larger than any value, so the same query returns Asha and Dev there.

Puzzle 10: a UNIQUE column with two NULLs SQL · sqlite/p10_unique_and_null.sql

-- A UNIQUE column, with one address and two rows without one.
CREATE TABLE subscribers (email VARCHAR(100) UNIQUE);
INSERT INTO subscribers VALUES ('asha@example.in'), (NULL), (NULL);

SELECT count(*) AS stored_rows,
       count(DISTINCT email) AS distinct_emails,
       (SELECT count(*) FROM (SELECT DISTINCT email FROM subscribers) AS d) AS distinct_rows
FROM subscribers;

Output

┌─────────────┬─────────────────┬───────────────┐
│ stored_rows │ distinct_emails │ distinct_rows │
├─────────────┼─────────────────┼───────────────┤
│ 3           │ 1               │ 2             │
└─────────────┴─────────────────┴───────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < p10_unique_and_null.sql

Both NULL rows were accepted: a UNIQUE constraint compares values, and NULL is not equal to anything, so SQLite, PostgreSQL by default and MySQL all allow several NULLs. count(DISTINCT email) ignores them and returns 1, while SELECT DISTINCT keeps one NULL row beside the address, so it returns 2 rows.

The same puzzles in PostgreSQL and MySQL

The scripts below run the ten puzzles one after another, dropping each puzzle’s tables before the next, and add an eleventh that SQLite cannot run: comparisons with ALL and ANY against a subquery that returns no rows.

Eleven puzzles in PostgreSQL 18.6

pg/eleven_items.py

"""The ten items of this lesson, and an eleventh with ALL and ANY, run in PostgreSQL 18.6."""

from pg_server import psql

psql(r"""
\echo '-- 1.'
-- Customers who never ordered, with one order whose customer is not recorded.
CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Bilal'), (3, 'Chitra');
CREATE TABLE orders (id INTEGER, customer_id INTEGER);
INSERT INTO orders VALUES (10, 1), (11, NULL);

SELECT count(*) AS never_ordered
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
DROP TABLE customers, orders;

\echo '-- 2.'
-- Five rows: two in Pune, one in Kochi and two without a city.
CREATE TABLE visits (city TEXT);
INSERT INTO visits VALUES ('Pune'), ('Pune'), (NULL), ('Kochi'), (NULL);

SELECT count(*) AS all_rows, count(city) AS with_city, count(DISTINCT city) AS cities
FROM visits;
DROP TABLE visits;

\echo '-- 3.'
-- The key 1 appears twice on the left and three times on the right.
CREATE TABLE lefty (k INTEGER);
INSERT INTO lefty VALUES (1), (1), (2);
CREATE TABLE righty (k INTEGER);
INSERT INTO righty VALUES (1), (1), (1), (3);

SELECT count(*) AS joined_rows
FROM lefty JOIN righty ON lefty.k = righty.k;
DROP TABLE lefty, righty;

\echo '-- 4.'
-- Three customers; Asha has a paid and a refunded order, Bilal a refunded one, Chitra none.
CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Bilal'), (3, 'Chitra');
CREATE TABLE orders (id INTEGER, customer_id INTEGER, status TEXT);
INSERT INTO orders VALUES (10, 1, 'paid'), (11, 1, 'refunded'), (12, 2, 'refunded');

SELECT
  (SELECT count(*) FROM customers AS c
     LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid') AS filter_in_on,
  (SELECT count(*) FROM customers AS c
     LEFT JOIN orders AS o ON o.customer_id = c.id
   WHERE o.status = 'paid') AS filter_in_where;
DROP TABLE customers, orders;

\echo '-- 5.'
-- Two orders without a coupon and one with the coupon NEW10.
CREATE TABLE carts (id INTEGER, coupon TEXT);
INSERT INTO carts VALUES (1, NULL), (2, 'NEW10'), (3, NULL);

SELECT
  (SELECT count(*) FROM carts WHERE coupon = NULL)     AS equals_null,
  (SELECT count(*) FROM carts WHERE coupon IS NULL)    AS is_null,
  (SELECT count(*) FROM carts WHERE coupon <> 'NEW10') AS not_new10;
DROP TABLE carts;

\echo '-- 6.'
-- Rows kept by UNION and by UNION ALL, and by UNION of two NULLs.
SELECT
  (SELECT count(*) FROM (SELECT 1 AS x UNION SELECT 1 UNION SELECT 2) AS s)             AS with_union,
  (SELECT count(*) FROM (SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 2) AS s)     AS with_union_all,
  (SELECT count(*) FROM (SELECT NULL AS x UNION SELECT NULL) AS s)                       AS null_rows;

\echo '-- 7.'
-- Two ratings, 4 and 5 stars, stored as integers.
CREATE TABLE ratings (stars INTEGER);
INSERT INTO ratings VALUES (4), (5);

SELECT 9 / 2 AS nine_halves, avg(stars) AS avg_stars, sum(stars) / count(*) AS sum_over_count
FROM ratings;
DROP TABLE ratings;

\echo '-- 8.'
-- No order is above 1000, so both queries read no rows.
CREATE TABLE orders (city TEXT, amount INTEGER);
INSERT INTO orders VALUES ('Pune', 400), ('Kochi', 650);

SELECT count(*) AS orders, sum(amount) AS total, coalesce(sum(amount), 0) AS total_or_zero
FROM orders
WHERE amount > 1000;

SELECT city, count(*) AS orders
FROM orders
WHERE amount > 1000
GROUP BY city;
DROP TABLE orders;

\echo '-- 9.'
-- Four players; Bilal has no score yet.
CREATE TABLE players (name TEXT, score INTEGER);
INSERT INTO players VALUES ('Asha', 70), ('Bilal', NULL), ('Chitra', 90), ('Dev', 70);

SELECT name, score
FROM players
ORDER BY score, name
LIMIT 2;
DROP TABLE players;

\echo '-- 10.'
-- A UNIQUE column, with one address and two rows without one.
CREATE TABLE subscribers (email VARCHAR(100) UNIQUE);
INSERT INTO subscribers VALUES ('asha@example.in'), (NULL), (NULL);

SELECT count(*) AS stored_rows,
       count(DISTINCT email) AS distinct_emails,
       (SELECT count(*) FROM (SELECT DISTINCT email FROM subscribers) AS d) AS distinct_rows
FROM subscribers;
DROP TABLE subscribers;

\echo '-- 11.'
-- Comparisons with ALL and ANY against a subquery that returns no rows (SQLite has neither).
CREATE TABLE players (name VARCHAR(20), score INTEGER);
INSERT INTO players VALUES ('Asha', 70), ('Bilal', NULL), ('Chitra', 90), ('Dev', 70);

SELECT
  (SELECT count(*) FROM players WHERE score > ALL (SELECT score FROM players WHERE name = 'Nobody')) AS above_all_of_none,
  (SELECT count(*) FROM players WHERE score > ANY (SELECT score FROM players WHERE name = 'Nobody')) AS above_any_of_none;
DROP TABLE players;
""")

Output

-- 1.
 never_ordered 
---------------
             0
(1 row)

-- 2.
 all_rows | with_city | cities 
----------+-----------+--------
        5 |         3 |      2
(1 row)

-- 3.
 joined_rows 
-------------
           6
(1 row)

-- 4.
 filter_in_on | filter_in_where 
--------------+-----------------
            3 |               1
(1 row)

-- 5.
 equals_null | is_null | not_new10 
-------------+---------+-----------
           0 |       2 |         0
(1 row)

-- 6.
 with_union | with_union_all | null_rows 
------------+----------------+-----------
          2 |              3 |         1
(1 row)

-- 7.
 nine_halves |     avg_stars      | sum_over_count 
-------------+--------------------+----------------
           4 | 4.5000000000000000 |              4
(1 row)

-- 8.
 orders | total | total_or_zero 
--------+-------+---------------
      0 |       |             0
(1 row)

 city | orders 
------+--------
(0 rows)

-- 9.
 name | score 
------+-------
 Asha |    70
 Dev  |    70
(2 rows)

-- 10.
 stored_rows | distinct_emails | distinct_rows 
-------------+-----------------+---------------
           3 |               1 |             2
(1 row)

-- 11.
 above_all_of_none | above_any_of_none 
-------------------+-------------------
                 4 |                 0
(1 row)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 eleven_items.py

pg/pg_server.py

"""Runs SQL on a private, throwaway PostgreSQL 18.6 server and prints what psql printed.

Each run makes a new database cluster in its own temporary folder (initdb), starts a server that listens only on a
Unix socket in that folder (no TCP port), sends the SQL to psql, then stops the server and deletes the folder. Every
run therefore starts from an empty database and prints the same text.

The settings keep the output the same on every run: no autovacuum (statistics change only when the SQL runs ANALYZE
or VACUUM), statistics taken from every row instead of a random sample (default_statistics_target = 10000 samples up
to 3,000,000 rows), no parallel workers, no JIT compilation, and WAL buffers large enough never to fill up during a
run. The cluster uses the C locale, UTF-8 and UTC.

PostgreSQL 18.6 comes from the folder where mise installs it (mise install postgres@18.6). The output is psql's own,
except that the "psql:<stdin>:<line>:" in front of each message is left out.
"""

import atexit
import os
import pwd
import re
import shutil
import subprocess
import sys

BIN = os.path.join(pwd.getpwuid(os.getuid()).pw_dir, ".local", "share", "mise", "installs", "postgres", "18.6", "bin")
WHERE = re.compile(r"^psql:<stdin>:\d+: ", re.MULTILINE)

# Short names: a Unix socket path may not be longer than about 100 characters.
_base = os.path.join(os.environ.get("TMPDIR", os.getcwd()), "pg")
_data = os.path.join(_base, "d")
_sock = os.path.join(_base, "s")
_env = dict(os.environ, PGHOST=_sock, PGUSER="postgres", PGDATABASE="postgres", LC_ALL="C", LANG="C", TZ="UTC")
_running = False


def _quiet(*argv: str) -> None:
    subprocess.run(argv, env=_env, check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)


def _start() -> None:
    global _running
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_sock)
    _quiet(os.path.join(BIN, "initdb"), "-D", _data, "-U", "postgres", "-A", "trust", "-E", "UTF8", "--locale=C", "--no-sync", "--no-instructions")
    options = (
        f"-k {_sock} -c listen_addresses='' -c fsync=off -c timezone=UTC -c autovacuum=off"
        " -c default_statistics_target=10000 -c max_parallel_workers_per_gather=0 -c jit=off -c wal_buffers=64MB"
    )
    _quiet(os.path.join(BIN, "pg_ctl"), "start", "-w", "-s", "-D", _data, "-l", os.path.join(_base, "server.log"), "-o", options)
    _running = True
    atexit.register(_stop)


def _stop() -> None:
    global _running
    if _running:
        subprocess.run([os.path.join(BIN, "pg_ctl"), "stop", "-s", "-m", "fast", "-D", _data], env=_env, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        _running = False
    shutil.rmtree(_base, ignore_errors=True)


def psql(sql: str) -> None:
    """Sends a block of SQL to psql and prints what it printed, errors included, in order."""
    if not _running:
        _start()
    run = subprocess.run(
        [os.path.join(BIN, "psql"), "-X", "-q", "-v", "ON_ERROR_STOP=0", "-P", "pager=off"],
        input=sql.strip() + "\n", env=_env, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )
    sys.stdout.write(WHERE.sub("", run.stdout))
    sys.stdout.flush()

Eleven puzzles in MySQL 8.4.11

mysql/eleven_items.py

"""The ten items of this lesson, and an eleventh with ALL and ANY, run in MySQL 8.4.11."""

from mysql_server import mysql

mysql(r"""
SELECT '1.' AS item;
-- Customers who never ordered, with one order whose customer is not recorded.
CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Bilal'), (3, 'Chitra');
CREATE TABLE orders (id INTEGER, customer_id INTEGER);
INSERT INTO orders VALUES (10, 1), (11, NULL);

SELECT count(*) AS never_ordered
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
DROP TABLE customers, orders;

SELECT '2.' AS item;
-- Five rows: two in Pune, one in Kochi and two without a city.
CREATE TABLE visits (city TEXT);
INSERT INTO visits VALUES ('Pune'), ('Pune'), (NULL), ('Kochi'), (NULL);

SELECT count(*) AS all_rows, count(city) AS with_city, count(DISTINCT city) AS cities
FROM visits;
DROP TABLE visits;

SELECT '3.' AS item;
-- The key 1 appears twice on the left and three times on the right.
CREATE TABLE lefty (k INTEGER);
INSERT INTO lefty VALUES (1), (1), (2);
CREATE TABLE righty (k INTEGER);
INSERT INTO righty VALUES (1), (1), (1), (3);

SELECT count(*) AS joined_rows
FROM lefty JOIN righty ON lefty.k = righty.k;
DROP TABLE lefty, righty;

SELECT '4.' AS item;
-- Three customers; Asha has a paid and a refunded order, Bilal a refunded one, Chitra none.
CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Bilal'), (3, 'Chitra');
CREATE TABLE orders (id INTEGER, customer_id INTEGER, status TEXT);
INSERT INTO orders VALUES (10, 1, 'paid'), (11, 1, 'refunded'), (12, 2, 'refunded');

SELECT
  (SELECT count(*) FROM customers AS c
     LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid') AS filter_in_on,
  (SELECT count(*) FROM customers AS c
     LEFT JOIN orders AS o ON o.customer_id = c.id
   WHERE o.status = 'paid') AS filter_in_where;
DROP TABLE customers, orders;

SELECT '5.' AS item;
-- Two orders without a coupon and one with the coupon NEW10.
CREATE TABLE carts (id INTEGER, coupon TEXT);
INSERT INTO carts VALUES (1, NULL), (2, 'NEW10'), (3, NULL);

SELECT
  (SELECT count(*) FROM carts WHERE coupon = NULL)     AS equals_null,
  (SELECT count(*) FROM carts WHERE coupon IS NULL)    AS is_null,
  (SELECT count(*) FROM carts WHERE coupon <> 'NEW10') AS not_new10;
DROP TABLE carts;

SELECT '6.' AS item;
-- Rows kept by UNION and by UNION ALL, and by UNION of two NULLs.
SELECT
  (SELECT count(*) FROM (SELECT 1 AS x UNION SELECT 1 UNION SELECT 2) AS s)             AS with_union,
  (SELECT count(*) FROM (SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 2) AS s)     AS with_union_all,
  (SELECT count(*) FROM (SELECT NULL AS x UNION SELECT NULL) AS s)                       AS null_rows;

SELECT '7.' AS item;
-- Two ratings, 4 and 5 stars, stored as integers.
CREATE TABLE ratings (stars INTEGER);
INSERT INTO ratings VALUES (4), (5);

SELECT 9 / 2 AS nine_halves, avg(stars) AS avg_stars, sum(stars) / count(*) AS sum_over_count
FROM ratings;
DROP TABLE ratings;

SELECT '8.' AS item;
-- No order is above 1000, so both queries read no rows.
CREATE TABLE orders (city TEXT, amount INTEGER);
INSERT INTO orders VALUES ('Pune', 400), ('Kochi', 650);

SELECT count(*) AS orders, sum(amount) AS total, coalesce(sum(amount), 0) AS total_or_zero
FROM orders
WHERE amount > 1000;

SELECT city, count(*) AS orders
FROM orders
WHERE amount > 1000
GROUP BY city;
DROP TABLE orders;

SELECT '9.' AS item;
-- Four players; Bilal has no score yet.
CREATE TABLE players (name TEXT, score INTEGER);
INSERT INTO players VALUES ('Asha', 70), ('Bilal', NULL), ('Chitra', 90), ('Dev', 70);

SELECT name, score
FROM players
ORDER BY score, name
LIMIT 2;
DROP TABLE players;

SELECT '10.' AS item;
-- A UNIQUE column, with one address and two rows without one.
CREATE TABLE subscribers (email VARCHAR(100) UNIQUE);
INSERT INTO subscribers VALUES ('asha@example.in'), (NULL), (NULL);

SELECT count(*) AS stored_rows,
       count(DISTINCT email) AS distinct_emails,
       (SELECT count(*) FROM (SELECT DISTINCT email FROM subscribers) AS d) AS distinct_rows
FROM subscribers;
DROP TABLE subscribers;

SELECT '11.' AS item;
-- Comparisons with ALL and ANY against a subquery that returns no rows (SQLite has neither).
CREATE TABLE players (name VARCHAR(20), score INTEGER);
INSERT INTO players VALUES ('Asha', 70), ('Bilal', NULL), ('Chitra', 90), ('Dev', 70);

SELECT
  (SELECT count(*) FROM players WHERE score > ALL (SELECT score FROM players WHERE name = 'Nobody')) AS above_all_of_none,
  (SELECT count(*) FROM players WHERE score > ANY (SELECT score FROM players WHERE name = 'Nobody')) AS above_any_of_none;
DROP TABLE players;
""")

Output

+------+
| item |
+------+
| 1.   |
+------+
+---------------+
| never_ordered |
+---------------+
|             0 |
+---------------+
+------+
| item |
+------+
| 2.   |
+------+
+----------+-----------+--------+
| all_rows | with_city | cities |
+----------+-----------+--------+
|        5 |         3 |      2 |
+----------+-----------+--------+
+------+
| item |
+------+
| 3.   |
+------+
+-------------+
| joined_rows |
+-------------+
|           6 |
+-------------+
+------+
| item |
+------+
| 4.   |
+------+
+--------------+-----------------+
| filter_in_on | filter_in_where |
+--------------+-----------------+
|            3 |               1 |
+--------------+-----------------+
+------+
| item |
+------+
| 5.   |
+------+
+-------------+---------+-----------+
| equals_null | is_null | not_new10 |
+-------------+---------+-----------+
|           0 |       2 |         0 |
+-------------+---------+-----------+
+------+
| item |
+------+
| 6.   |
+------+
+------------+----------------+-----------+
| with_union | with_union_all | null_rows |
+------------+----------------+-----------+
|          2 |              3 |         1 |
+------------+----------------+-----------+
+------+
| item |
+------+
| 7.   |
+------+
+-------------+-----------+----------------+
| nine_halves | avg_stars | sum_over_count |
+-------------+-----------+----------------+
|      4.5000 |    4.5000 |         4.5000 |
+-------------+-----------+----------------+
+------+
| item |
+------+
| 8.   |
+------+
+--------+-------+---------------+
| orders | total | total_or_zero |
+--------+-------+---------------+
|      0 |  NULL |             0 |
+--------+-------+---------------+
+------+
| item |
+------+
| 9.   |
+------+
+-------+-------+
| name  | score |
+-------+-------+
| Bilal |  NULL |
| Asha  |    70 |
+-------+-------+
+------+
| item |
+------+
| 10.  |
+------+
+-------------+-----------------+---------------+
| stored_rows | distinct_emails | distinct_rows |
+-------------+-----------------+---------------+
|           3 |               1 |             2 |
+-------------+-----------------+---------------+
+------+
| item |
+------+
| 11.  |
+------+
+-------------------+-------------------+
| above_all_of_none | above_any_of_none |
+-------------------+-------------------+
|                 4 |                 0 |
+-------------------+-------------------+

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 eleven_items.py

mysql/mysql_server.py

"""Runs SQL on a private, throwaway MySQL 8.4.11 server and prints what the mysql client printed.

Each run makes a new data folder in its own temporary folder (mysqld --initialize-insecure), starts a server that
listens only on a Unix socket in that folder (--skip-networking: no TCP port), creates the database "shop", sends
the SQL to the mysql client, then shuts the server down and deletes the folder. Every run starts from an empty
database.

Two settings keep the plans the same on every run: InnoDB recomputes statistics only when the SQL runs ANALYZE TABLE
(innodb_stats_auto_recalc=OFF), and ANALYZE TABLE reads every page instead of a random sample of 20
(innodb_stats_persistent_sample_pages). EXPLAIN ANALYZE prints how long each step took, which differs from run to
run, so this file prints "actual time=…" in its place; everything else is the client's own output.

MySQL 8.4.11 comes from the folder where mise installs it (mise install mysql@8.4.11).
"""

import atexit
import os
import pwd
import re
import shutil
import subprocess
import sys
import time

HOME = os.path.join(pwd.getpwuid(os.getuid()).pw_dir, ".local", "share", "mise", "installs", "mysql", "8.4.11")
BIN = os.path.join(HOME, "bin")
TIMES = re.compile(r"actual time=[0-9.e+-]+\.\.[0-9.e+-]+")

_base = os.path.join(os.environ.get("TMPDIR", os.getcwd()), "my")
_data = os.path.join(_base, "d")
_sock = os.path.join(_base, "m.sock")
_server = None


def _client(*args: str, sql: str = "") -> subprocess.CompletedProcess:
    return subprocess.run(
        [os.path.join(BIN, "mysql"), "--no-defaults", f"--socket={_sock}", "-uroot", *args],
        input=sql, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )


def _start() -> None:
    global _server
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_base)
    server = [
        os.path.join(BIN, "mysqld"), "--no-defaults", f"--basedir={HOME}", f"--datadir={_data}",
        f"--socket={_sock}", "--skip-networking", "--mysqlx=OFF", f"--pid-file={os.path.join(_base, 'pid')}",
        f"--log-error={os.path.join(_base, 'error.log')}", "--skip-log-bin", "--performance-schema=OFF",
        "--innodb-stats-auto-recalc=OFF", "--innodb-stats-persistent-sample-pages=100000",
        "--default-time-zone=+00:00",
    ]
    subprocess.run(server[:4] + ["--initialize-insecure", f"--log-error={os.path.join(_base, 'init.log')}"], check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    _server = subprocess.Popen(server, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    atexit.register(_stop)
    for _ in range(600):
        if _client("-e", "CREATE DATABASE shop").returncode == 0:
            return
        time.sleep(0.1)
    raise RuntimeError("the MySQL server did not start")


def _stop() -> None:
    global _server
    if _server is not None:
        subprocess.run([os.path.join(BIN, "mysqladmin"), "--no-defaults", f"--socket={_sock}", "-uroot", "shutdown"], stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        try:
            _server.wait(timeout=60)
        except subprocess.TimeoutExpired:
            _server.kill()
        _server = None
    shutil.rmtree(_base, ignore_errors=True)


def mysql(sql: str) -> None:
    """Sends a block of SQL to the mysql client (tables as boxes, \\G for one row per block) and prints its output."""
    if _server is None:
        _start()
    run = _client("--table", "--force", "--comments", "shop", sql=sql.strip() + "\n")
    sys.stdout.write(TIMES.sub("actual time=…", run.stdout))
    sys.stdout.flush()

Where the recorded runs differ
Criterion	SQLite 3.49.1	PostgreSQL 18.6	MySQL 8.4.11
Puzzle 7: 9 / 2 and sum / count	4 and 4	4 and 4	4.5000 and 4.5000
Puzzle 7: avg(stars)	4.5	4.5000000000000000	4.5000
Puzzle 9: the first two rows	Bilal (NULL), Asha	Asha, Dev	Bilal (NULL), Asha
Puzzle 11: > ALL and > ANY of no rows	Not supported	4 and 0	4 and 0
Puzzles 1 to 6, 8 and 10	The same numbers	The same numbers	The same numbers

Puzzle 11 surprises people in both directions. A comparison with ALL is true when it holds for every row of the subquery, which is trivially the case when there are no rows, so all four players pass, even Bilal with his NULL score. A comparison with ANY needs at least one row that makes it true, so with no rows nobody passes. PostgreSQL’s manual states both cases, and SQLite has no ALL or ANY at all: write NOT EXISTS for the first and EXISTS for the second there.

SQL questions in GATE CS

The Databases section of the GATE 2027 CS syllabus names ER modelling, then relational algebra, tuple calculus and SQL as parts of the relational model, then integrity constraints, normal forms, file organisation, B and B+ tree indexing, transactions and concurrency control. The puzzles here practise the SQL part: counting the rows a query returns when NULLs, duplicates and aggregates meet. Relational algebra and tuple calculus are taught in the DBMS track.

SQLite Online (SQL Playground) Change a puzzle's rows, add a NULL or a duplicate, and predict the new output before you run it in your browser. SQL Formatter Lay out a dense query clause by clause before you walk through it in its logical order.

Your turn

Exercise · Easy · SQL

Paid orders for every product, including products with none

A shop has two tables:

products(id, name): one row per product; two products may share a name;
orders(id, product_id, status): one row per order, with status one of 'paid', 'refunded' or 'pending'.

The report below should list every product with its number of paid orders, and 0 for a product that has none, but a colleague's first draft leaves some products out. Predict what the draft returns, then fix it.

Return two columns, name and paid_orders, one row per product (not per name), sorted by paid_orders, highest first, then by name. The order of the rows is checked.

Starter code · paid_orders.sql

-- Every product with its number of paid orders. This draft leaves out some products.
SELECT p.name, count(*) AS paid_orders
FROM products AS p
LEFT JOIN orders AS o ON o.product_id = p.id
WHERE o.status = 'paid'
GROUP BY p.name
ORDER BY paid_orders DESC, p.name;

The sample tests · tests.yaml

# Sample tests: each one builds small products and orders tables, runs your query on them and compares the rows it
# returns, in order.
tests:
  - name: keeps products without a paid order, with 0
    ordered: true
    setup: |
      CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
      CREATE TABLE orders (id INTEGER PRIMARY KEY, product_id INTEGER, status TEXT NOT NULL);
      INSERT INTO products VALUES (1, 'Kettle'), (2, 'Lamp'), (3, 'Mixer');
      INSERT INTO orders VALUES (10, 1, 'paid'), (11, 1, 'paid'), (12, 1, 'refunded'), (13, 2, 'refunded');
    expected:
      columns: [name, paid_orders]
      rows:
        - ['Kettle', 2]
        - ['Lamp', 0]
        - ['Mixer', 0]
  - name: counts only paid orders
    ordered: true
    setup: |
      CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
      CREATE TABLE orders (id INTEGER PRIMARY KEY, product_id INTEGER, status TEXT NOT NULL);
      INSERT INTO products VALUES (1, 'Fan'), (2, 'Iron');
      INSERT INTO orders VALUES (20, 1, 'paid'), (21, 1, 'paid'), (22, 1, 'paid'), (23, 2, 'paid'), (24, 2, 'pending');
    expected:
      columns: [name, paid_orders]
      rows:
        - ['Fan', 3]
        - ['Iron', 1]
  - name: keeps two products that share a name apart
    ordered: true
    setup: |
      CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
      CREATE TABLE orders (id INTEGER PRIMARY KEY, product_id INTEGER, status TEXT NOT NULL);
      INSERT INTO products VALUES (1, 'Bulb'), (2, 'Bulb'), (3, 'Torch');
      INSERT INTO orders VALUES (30, 1, 'paid'), (31, 1, 'paid'), (32, 2, 'paid'), (33, 3, 'refunded');
    expected:
      columns: [name, paid_orders]
      rows:
        - ['Bulb', 2]
        - ['Bulb', 1]
        - ['Torch', 0]

A hint

Puzzle 4 of the lesson shows where the draft loses rows: a condition on the right-hand table of a LEFT JOIN belongs in its ON clause. Then puzzle 2 shows which count() gives 0 for a product without a match.

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Interview questions

Fresher · What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows, all of them or those its WHERE clause selects, one by one: it fires delete triggers, checks foreign keys and can be rolled back. TRUNCATE empties a whole table without scanning it, which is much faster on a large table, but it fires no ON DELETE triggers and refuses a table that other tables reference. DROP TABLE removes the table itself, with its rows, indexes, constraints and triggers. The engines differ on TRUNCATE: PostgreSQL’s can be rolled back inside a transaction and keeps identity counters unless you add RESTART IDENTITY, while MySQL’s commits implicitly, cannot be rolled back and resets AUTO_INCREMENT. SQLite has no TRUNCATE; a DELETE without WHERE gets the same fast path there.

Fresher · Why is NULL = NULL not true, and how do you test for NULL?

NULL stands for a value that is missing or unknown, so comparing it with anything, another NULL included, gives unknown rather than true or false. WHERE keeps only the rows for which its condition is true, so WHERE x = NULL matches nothing, and so does WHERE x <> NULL. Test with x IS NULL and x IS NOT NULL instead. To compare two columns that may both be NULL as if NULLs were equal, use a IS NOT DISTINCT FROM b in PostgreSQL, a IS b in SQLite, or a <=> b in MySQL. Operations that remove duplicates, such as DISTINCT, GROUP BY and UNION, are the exception: there, two NULLs are treated as the same.

Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.

Choose a pass

Fresher to mid

On a staging copy you run BEGIN, then TRUNCATE orders, then ROLLBACK, once on PostgreSQL 18 and once on MySQL 8.4. What is left in orders in each engine, what id does the next new order get, and when would you choose DELETE instead?

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups

Key takeaways

Answer a theory question in three sentences: the definition, the difference that matters, a small example, with the engine named when it changes the answer.
Predict an output by walking the logical order, FROM and joins first, and counting the rows after every step.
A join multiplies duplicate keys; WHERE keeps only true conditions, so NULL comparisons drop rows; aggregates skip NULLs except count(*).
An aggregate over no rows returns one row without GROUP BY and none with it; sum() of nothing is NULL.
Integer division and NULL sorting differ between engines, and SQLite has no ALL or ANY: say which engine you assume.

References

PostgreSQL documentation: Constraints (primary keys and foreign keys) (The PostgreSQL Global Development Group)
A Relational Model of Data for Large Shared Data Banks (E. F. Codd, Communications of the ACM 13(6), 1970) (Association for Computing Machinery)
PostgreSQL documentation: Transactions (tutorial) (The PostgreSQL Global Development Group)
MySQL 8.4 Reference Manual: InnoDB and the ACID Model (Oracle Corporation)
PostgreSQL documentation: Transaction Isolation (The PostgreSQL Global Development Group)
MySQL 8.4 Reference Manual: Transaction Isolation Levels (Oracle Corporation)
SQLite: Isolation In SQLite (SQLite)
PostgreSQL documentation: Index Types (The PostgreSQL Global Development Group)
MySQL 8.4 Reference Manual: Clustered and Secondary Indexes (Oracle Corporation)
PostgreSQL documentation: CLUSTER (The PostgreSQL Global Development Group)
SQLite: Clustered Indexes and the WITHOUT ROWID Optimization (SQLite)
PostgreSQL documentation: Aggregate Functions (tutorial: WHERE and HAVING) (The PostgreSQL Global Development Group)
PostgreSQL documentation: Combining Queries (UNION, INTERSECT, EXCEPT) (The PostgreSQL Global Development Group)
PostgreSQL documentation: CREATE MATERIALIZED VIEW (The PostgreSQL Global Development Group)
PostgreSQL documentation: REFRESH MATERIALIZED VIEW (The PostgreSQL Global Development Group)
MySQL 8.4 Reference Manual: Optimizing Subqueries with Semijoin Transformations (Oracle Corporation)
MySQL 8.4 Reference Manual: Correlated Subqueries (Oracle Corporation)
PostgreSQL documentation: Subquery Expressions (IN, NOT IN, ANY, ALL) (The PostgreSQL Global Development Group)
PostgreSQL documentation: Comparison Functions and Operators (IS NULL, IS DISTINCT FROM) (The PostgreSQL Global Development Group)
PostgreSQL documentation: Aggregate Functions (The PostgreSQL Global Development Group)
PostgreSQL documentation: Sorting Rows (ORDER BY) (The PostgreSQL Global Development Group)
PostgreSQL documentation: Mathematical Functions and Operators (The PostgreSQL Global Development Group)
PostgreSQL documentation: TRUNCATE (The PostgreSQL Global Development Group)
MySQL 8.4 Reference Manual: TRUNCATE TABLE Statement (Oracle Corporation)
MySQL 8.4 Reference Manual: Working with NULL Values (Oracle Corporation)
MySQL 8.4 Reference Manual: Arithmetic Operators (Oracle Corporation)
SQLite: NULL Handling in SQLite Versus Other Database Engines (SQLite)
SQLite: DELETE (the truncate optimization) (SQLite)
SQLite: Datatypes In SQLite (sort order of NULL) (SQLite)
GATE 2027 Computer Science and Information Technology (CS) syllabus (Indian Institute of Technology Madras (organising institute, GATE 2027))

Report a problem with this lesson

In this module: SQL interview preparation

How to approach SQL interview questions Free lesson, Pro answers: 25 minutes
Org chart drills: self-joins and recursion Free intro, Pro: 7 of 30 minutes free
Interview drills: retention, growth, rolling Free intro, Pro: 8 of 35 minutes free
Interview drills: 'all', 'none' and division Free intro, Pro: 6 of 30 minutes free
Interview drills: intervals and as-of lookups Free intro, Pro: 8 of 35 minutes free
Interview drills: reports, pivots, percentages Free intro, Pro: 7 of 30 minutes free
Interview drills: Oracle SQL syntax Free intro, Pro: 8 of 35 minutes free
SQL theory questions and output prediction Free lesson, Pro answers: 30 minutes

Your country

SQL theory questions and output prediction

What you will learn

Before you start

What you will learn

Before you start

Ten theory questions, answered briefly

Keys: superkey, candidate key, primary key and foreign key

Normal forms: 1NF, 2NF, 3NF and BCNF

ACID

Isolation levels and their defaults

Index types

Clustered tables and heap tables

WHERE and HAVING

UNION and UNION ALL

Views and materialised views

Correlated and uncorrelated subqueries

Predicting an output: walk the query in its logical order

Ten predict-the-output puzzles

Predict the output: ten puzzles

The ten runs, explained

The same puzzles in PostgreSQL and MySQL

Your turn

Paid orders for every product, including products with none

Results of the sample tests

Interview questions

On a staging copy you run BEGIN, then TRUNCATE orders, then ROLLBACK, once on PostgreSQL 18 and once on MySQL 8.4. What is left in orders in each engine, what id does the next new order get, and when would you choose DELETE instead?

Key takeaways

References

Related tools