skip to content

How does a row-value comparison like (a, b) = (SELECT x, y FROM t) work?

level: middleimportance: should knowfreq 35%

answer

  1. it compares more than one column at once
  2. the parentheses on the left are not just grouping
  3. both sides have to be the same width
  4. every corresponding pair must be equal
  5. the right-hand side is a single row

basics

~20 s

A row constructor on the left is compared against a one-row subquery of the same width. The comparison is true only when every corresponding pair is equal, letting a composite key be matched in a single predicate instead of several ANDed comparisons.

solid answer

~50 s

`(a, b)` is a **row constructor**, and comparing it with a subquery makes that subquery a **row subquery**: it must return exactly the same number of columns, with comparable types, and at most one row. The equality is true when all corresponding pairs are equal, false when any pair is unequal, and UNKNOWN when a NULL leaves the outcome undecided. A degree mismatch such as `(a, b) = (SELECT x FROM t)` is rejected when the statement is prepared, while too many rows is the usual runtime cardinality error. The same constructor works with `IN` against a multi-row table subquery, which is the idiomatic way to match a composite key against a set of pairs. Support is uneven: PostgreSQL and MySQL implement row comparisons, SQL Server does not, so portable code sometimes has to spell the comparison out column by column.

code

sql · 8 lines
sql
-- match a composite key in one predicate
SELECT *
FROM order_items oi
WHERE (oi.order_id, oi.line_no) = (
    SELECT order_id, line_no
    FROM shipment_lines
    WHERE tracking_code = 'TRK-9001'
);

go deeper

for a junior

Recognise the syntax when you see it and be able to say it compares two columns at once against one row returned by the subquery.

for a middle

Explain the degree rule, the at-most-one-row rule, and the all-pairs meaning of equality, and show the IN variant that matches a composite key against many pairs.

for a senior

Argue why the compact form is safer than two independent scalar subqueries — one source row, no duplicated inner query — and flag the portability caveat before it reaches a codebase that has to run on several engines.

for a principal

Own the convention: composite identifiers should be compared as units across the codebase, and where an engine lacks row comparison the standard rewrite should be agreed once rather than improvised per query.

## The row constructor Standard SQL lets you build a **row value** from a parenthesised list of expressions: `(a, b, c)`, or explicitly `ROW(a, b, c)`. A row value is a first-class thing that can be compared with another row value of the same degree. When the other side is a subquery, that subquery is a **row subquery** — one row, several columns. ```sql SELECT * FROM order_items oi WHERE (oi.order_id, oi.line_no) = ( SELECT order_id, line_no FROM shipment_lines WHERE tracking_code = 'TRK-9001' ); ``` This asks: is this item's composite key equal to the key carried by that one shipment line? ## The rules the comparison follows **Degree must match.** Both sides need the same number of elements. `(a, b) = (SELECT x FROM t)` is a static error — the engine knows both column counts while preparing the statement and rejects it before touching data, with a message about too few or too many columns. **Types must be comparable pairwise.** The first element is compared with the first, the second with the second, and each pair must be of comparable types. **Cardinality still applies.** A row subquery on the right of `=` must return at most one row. Two rows is the same cardinality violation you get from a scalar subquery in a value position; zero rows makes the comparison UNKNOWN, so no row passes the filter. **Equality is all-pairs.** `(a, b) = (c, d)` is TRUE when every pair compares equal, FALSE when any pair compares unequal, and UNKNOWN otherwise — which is what happens when a NULL sits in a position that would have decided the result. ## Why not just write two predicates? The spelled-out equivalent looks like this: ```sql SELECT * FROM order_items oi WHERE oi.order_id = (SELECT order_id FROM shipment_lines WHERE tracking_code = 'TRK-9001') AND oi.line_no = (SELECT line_no FROM shipment_lines WHERE tracking_code = 'TRK-9001'); ``` Three things are worse here. The inner query is written twice, so it can drift when someone edits one copy. The intent — *one key, matched as a unit* — is no longer visible in the syntax. And in the general case two independent subqueries can be satisfied by two different source rows, so the pair you match need not have come from the same row; the row-comparison form cannot have that defect because there is only one row on the right. ## The IN form: matching against many pairs The same constructor works against a multi-row table subquery, which is the version most people meet first: ```sql SELECT * FROM order_items oi WHERE (oi.order_id, oi.line_no) IN ( SELECT order_id, line_no FROM shipment_lines WHERE shipped_on = DATE '2024-03-01' ); ``` Here the subquery may return any number of rows; each is a two-element row value, and the predicate is true if the left row equals any of them. This is the natural spelling for "is this composite key in that set of composite keys", and it is much clearer than an equivalent join written only to test membership. ## Ordering comparisons The standard also defines the ordering operators on rows: `(a, b) < (c, d)` compares left to right, moving on to the next element only when the current pair is equal — the same rule a dictionary uses for words. It is a real part of the row-value feature, though it appears far less often than equality and its support varies more. ## Portability Row-value comparison is standard SQL, but engines differ on how much of it they implement. PostgreSQL and MySQL both document row constructors compared against subqueries. SQL Server has no row-value comparison in predicates, so the same intent is written as ANDed comparisons or as a join against a derived table. If your code has to run on more than one engine, verify the support before reaching for the compact form, and be prepared to spell it out. ## When to reach for it Row comparison earns its place whenever the natural unit of comparison is a tuple rather than a single value: composite primary keys, `(entity_type, entity_id)` polymorphic references, `(year, month)` period keys, `(latitude_band, longitude_band)` bucket keys. In all of those, splitting the comparison into separate predicates is not just more verbose — it obscures that the columns are one identifier that happens to be spread over two storage columns.

  • What is the result of (1, NULL) = (1, 2) in standard SQL?
    UNKNOWN. The first pair compares equal, so the outcome depends on the second, and a NULL there leaves it undecided. Row equality is TRUE only when every pair is equal, FALSE when some pair is definitely unequal, and UNKNOWN otherwise — so in a WHERE clause the row is dropped, exactly as any other UNKNOWN predicate would be.
  • How does the IN form differ from the = form with a row constructor?
    Cardinality. With `=` the right side must be a single row, and two rows raise a cardinality error. With `IN` the subquery is a table subquery of any size: each returned row is a row value, and the predicate is true if the left-hand row equals any of them. The degree rule is unchanged in both.
  • What happens if the two sides have different numbers of columns?
    The statement is rejected before it executes. Degree is fixed by the subquery's SELECT list, so the engine sees the mismatch while preparing the statement and reports too many or too few columns. Unlike a row-count problem, no data is needed to detect it.

saying these in an interview costs you the question

  • Treats (a, b) as ordinary grouping parentheses
  • Thinks the two sides may have different column counts
  • Assumes two separate scalar subqueries are always equivalent
  • Believes a row comparison can match several rows with =
  • Assumes every engine supports row-value comparison

context

resolving links…