SQL / SUBQUERIES AND CTES
Comparing whole rows with row subqueries
Compare several columns as one value against a one-row subquery, using = for exact matches, IN for pairs, and > for lexicographic 'after this row' filters.
What you will learn
- Match several columns at once with (a, b) = (SELECT x, y ...)
- Filter on pairs with (a, b) IN (SELECT x, y ...) instead of two separate IN tests
- Read (a, b) > (c, d) left to right: the first differing column decides the result
- Spot the NULL trap: a row containing NULL fails = even against an identical row
Understanding Comparing whole rows with row subqueries
A row subquery returns one row that has more than one column, and SQL lets you compare that whole row against a parenthesized list of expressions in a single operation. (customer, ship_day, cents) = (SELECT customer, ship_day, cents FROM orders WHERE id = 1) is one comparison, not three: the parentheses on the left build a row value, and the subquery on the right produces another row value of the same shape. The pairing is positional, not by name, so the first expression on the left is compared with the first column of the subquery and reordering the select list quietly changes the meaning.
Equality on rows is defined pairwise: true when every pair is equal, false as soon as one pair is definitely unequal, and unknown when the only thing standing in the way is a NULL. The ordering operators behave differently. They walk the columns left to right and stop at the first pair that differs, which is exactly how ORDER BY a, b sorts. That is why (a, b) > (c, d) is not a > c AND b > d: a row whose a already exceeds c qualifies whatever its b happens to be.
Row comparisons pay off when the thing you are identifying is a composite key or a position in a sort order rather than a single value: duplicate detection over (customer, day, amount), checking line items against an approved (sku, price) list, or resuming a scan after the last row a page showed. Next to a hand-expanded AND/OR chain the row form is shorter and harder to get subtly wrong, and PostgreSQL and MySQL can often turn (a, b) > (...) into a single range scan on a composite index on (a, b), which the OR-expanded form usually loses. The subquery here is uncorrelated, so it is evaluated once and its row is reused for every candidate row.
CREATE TABLE orders (
id INTEGER,
customer TEXT,
ship_day TEXT,
cents INTEGER
);
INSERT INTO orders VALUES
(1, 'acme', '2026-03-01', 12000),
(2, 'globex', '2026-03-01', 7550),
(3, 'acme', '2026-03-04', 12000),
(4, 'initech', '2026-03-05', 21000),
(5, 'acme', '2026-03-01', 12000);
-- every order identical to order 1 on all three columns
SELECT id, customer, ship_day, cents
FROM orders
WHERE (customer, ship_day, cents) = (SELECT customer, ship_day, cents
FROM orders
WHERE id = 1)
ORDER BY id;A parenthesized list of columns is itself one comparable value, so a one-row subquery can be matched against it as a unit, with = meaning every column equal and < / > meaning left-to-right lexicographic order.
Worked examples
Matching pairs with a multi-column IN
Shows that a row-value IN tests combinations, which two independent IN tests do not.
CREATE TABLE line_item (
order_id INTEGER,
sku TEXT,
cents INTEGER
);
CREATE TABLE price_list (
sku TEXT,
cents INTEGER
);
INSERT INTO line_item VALUES
(10, 'kbd', 4500),
(11, 'mou', 1900),
(12, 'kbd', 1900),
(13, 'pad', 500);
INSERT INTO price_list VALUES
('kbd', 4500),
('mou', 1900);
SELECT order_id, sku, cents
FROM line_item
WHERE (sku, cents) IN (SELECT sku, cents FROM price_list)
ORDER BY order_id;Example explained
Line 1(sku, cents) builds a two-column row that is tested against every pair the subquery returns, so unlike =, the subquery may return many rows here.
Line 2Order 12 is rejected: 'kbd' appears in the price list and 1900 appears in the price list, but the pair ('kbd', 1900) never does.
Line 3Rewriting it as sku IN (SELECT sku ...) AND cents IN (SELECT cents ...) would let order 12 through, because the two tests are satisfied by different price_list rows.
Line 4Order 13 fails on both columns, which is the uninteresting case; the pair semantics only show up on rows like 12.
Paging past the last row you saw
Uses lexicographic row ordering to fetch everything sorted after a known row, with the id as the tiebreaker.
CREATE TABLE event_log (
id INTEGER,
logged TEXT,
msg TEXT
);
INSERT INTO event_log VALUES
(7, '2026-05-01 09:00', 'start'),
(3, '2026-05-01 09:00', 'auth'),
(9, '2026-05-01 08:15', 'boot'),
(2, '2026-05-02 10:30', 'stop');
SELECT id, logged, msg
FROM event_log
WHERE (logged, id) > (SELECT logged, id FROM event_log WHERE id = 3)
ORDER BY logged, id;Example explained
Line 1The subquery returns the cursor row ('2026-05-01 09:00', 3), so the WHERE clause means 'sorts after that row under ORDER BY logged, id'.
Line 2Row 7 ties on logged, so the comparison moves to the second column and 7 > 3 makes it qualify.
Line 3Row 2 wins on logged already, so its id is never examined even though 2 < 3; writing logged > ... AND id > ... would wrongly drop it.
Line 4Row 9 loses on logged, so its larger id cannot rescue it: the comparison stops at the first differing column.
A NULL inside the row
Demonstrates that row equality is not null-safe, so a row with a NULL does not even match itself.
CREATE TABLE reading (
sensor TEXT,
slot INTEGER,
note TEXT
);
INSERT INTO reading VALUES
('a', 1, 'ok'),
('a', NULL, 'gap'),
('b', 1, 'ok');
SELECT
(SELECT count(*) FROM reading
WHERE (sensor, slot) = (SELECT sensor, slot FROM reading WHERE note = 'gap')) AS matched,
(SELECT count(*) FROM reading WHERE note = 'gap') AS gap_rows;Example explained
Line 1The inner row subquery returns ('a', NULL), a real row that exists in the table.
Line 2For that same row the first pair is equal and the second pair is NULL = NULL, which is unknown rather than true, so it is not counted.
Line 3For ('b', 1) the first pair is definitely unequal, so the whole comparison is false; an unequal pair decides the result before any NULL matters.
Line 4matched is 0 while gap_rows is 1, so for nullable key columns compare column by column with IS NOT DISTINCT FROM (PostgreSQL), <=> (MySQL) or IS (SQLite).
Important notes
Row equality is not null-safe: NULL = NULL is unknown, so a row containing NULL fails the test even against an identical row. Compare the nullable column separately with a null-safe operator when it is part of the key.
With =, <, > the subquery must yield at most one row: PostgreSQL and MySQL raise an error if it returns more, and zero rows makes the comparison unknown so nothing matches. Row-value comparison works in PostgreSQL, MySQL/MariaDB and SQLite 3.15+, but SQL Server has no row constructor in comparisons, so there you expand it into explicit AND/OR conditions.
Common mistakes
Reading (a, b) > (SELECT a, b ...) as 'both columns bigger'. It is lexicographic, so a row that already wins on a qualifies with any b; the AND version silently drops rows and a keyset pagination loop starts skipping records.
Replacing (sku, cents) IN (SELECT sku, cents FROM price_list) with sku IN (SELECT sku ...) AND cents IN (SELECT cents ...). Each test can be satisfied by a different row, so a line item carrying another product's price passes as a false positive.
Letting the subquery's select list drift out of order, as in (customer, ship_day) = (SELECT ship_day, customer FROM ...). Matching is positional, so with two text columns nothing errors and the query just returns nothing.
Try it yourself
Change, predict, then run
Create students(name TEXT, grade INTEGER, class TEXT) with five rows where two students share Mia's (grade, class) pair, then write one query using a row subquery that returns Mia's classmates on that exact pair while excluding Mia herself.
Open the SQL workspaceCheck your understanding
A table holds the (logged, id) pairs ('09:00', 7), ('08:15', 9) and ('10:30', 2). Which rows satisfy WHERE (logged, id) > ('09:00', 3)?
- ('09:00', 7) and ('10:30', 2)
- Only ('09:00', 7), because ('10:30', 2) has an id that is not greater than 3
- Only ('10:30', 2), because a tie on the first column disqualifies a row
- All three, because each row has at least one column greater than the right-hand side
Show answer
Row ordering is lexicographic: ('09:00', 7) ties on logged so the second column breaks the tie and 7 > 3 wins, while ('10:30', 2) already wins on logged so its id is never examined. Option 1 is the common misreading that treats the test as logged > '09:00' AND id > 3, a stricter and different condition. ('08:15', 9) loses on the first column, so its large id is irrelevant.