What does WHERE (dept_id, grade) IN ((1,'A'),(2,'B')) match, and how does it differ from two IN lists?

## Row values Standard SQL lets you build a **row value constructor** — an ordered tuple of expressions written in parentheses — and compare it against another row value. `IN` accepts a list of them: ```sql SELECT * FROM employees WHERE (dept_id, grade) IN ((1, 'A'), (2, 'B')); ``` The comparison is positional and element-wise: the first element of the left tuple is compared with the first element of each right tuple, and so on. Two row values are equal when every corresponding pair is equal. So the predicate above is exactly: ```sql WHERE (dept_id = 1 AND grade = 'A') OR (dept_id = 2 AND grade = 'B') ``` The two tuples must have the same **degree** (number of elements) and pairwise comparable types; a mismatch is an error, not a silent coercion of the whole tuple. ## Why it is not the same as separate IN lists The naive rewrite people reach for is: ```sql WHERE dept_id IN (1, 2) AND grade IN ('A', 'B') ``` That is a different question. It accepts **any combination** of a listed department with a listed grade — the cross product, four pairs here: (1,'A'), (1,'B'), (2,'A'), (2,'B'). If your intent was "grade A in department 1 and grade B in department 2", this quietly returns extra rows. The bigger the two lists, the worse the leak: two lists of 10 values express 100 combinations, while a row-value list of 10 tuples expresses exactly 10. This is the reason the construct exists. Without it you write the OR-of-ANDs by hand, which is verbose and easy to get wrong with mismatched parentheses, or you build a small derived table of the allowed pairs and join to it — which works but changes the shape of the query and reintroduces the row-multiplicity question if the pair list has duplicates. ## Where else row values show up Row value constructors are not specific to `IN`. They also work with the comparison operators, where the comparison is **lexicographic**: ```sql WHERE (created_at, id) > (TIMESTAMP '2024-05-01 10:00:00', 12345) ``` meaning "later than that timestamp, or the same timestamp with a larger id". Recognising the tuple syntax in one place helps you read it in the other. ## NULLs Row comparison inherits SQL's three-valued logic. If an element on either side is NULL, the equality of that pair is UNKNOWN, and the tuple comparison can be UNKNOWN rather than TRUE or FALSE — so a row with a NULL in one of the compared columns will not be returned by the predicate. Treat tuple membership tests the same way you treat any equality on a nullable column: if NULL should participate, handle it explicitly. ## Portability Row value constructors are in the SQL standard, but engine support is uneven, and it is uneven per-context: an engine may accept row values in a comparison but not in an `IN` list, or accept them only in certain positions. PostgreSQL and MySQL both accept the `IN`-list form shown here. Before you use it in code that has to run on more than one engine, verify on each of them — and if it is not available, fall back to the explicit OR-of-ANDs, which is portable everywhere and semantically identical. ## When to reach for it Good fits: filtering on a composite key (`(order_id, line_no) IN (…)`), fetching a specific set of (tenant, entity) pairs, or any "these exact combinations" filter assembled by an application. Poor fits: long generated lists, where the same arguments apply as to any oversized literal list, and cases where the pairs really live in a table already, in which case join to that table instead of inlining the data. ## What to say Name it as a row value constructor, give the OR-of-ANDs expansion, and contrast it with the cross-product behaviour of two independent `IN` lists. That contrast is the whole point of the question, and it is where a candidate either sees the difference in intent or does not.