In a query mixing UNION, INTERSECT and EXCEPT, which operator is evaluated first?

## The precedence rule SQL's set operators are not all equal. The standard ranks `INTERSECT` above `UNION` and `EXCEPT`; `UNION` and `EXCEPT` share a level and associate left to right. Reading a mixed query therefore means applying the same kind of rule you already apply to `AND` binding tighter than `OR`: ```sql SELECT id FROM a UNION SELECT id FROM b INTERSECT SELECT id FROM c; ``` This is `a UNION (b INTERSECT c)` — the intersection of b and c, unioned with a. It is **not** `(a UNION b) INTERSECT c`, which is what most readers expect from scanning downward. With `EXCEPT` and `UNION` at the same level, left-to-right associativity decides: ```sql SELECT id FROM a EXCEPT SELECT id FROM b UNION SELECT id FROM c; -- means (a EXCEPT b) UNION c ``` Here the leftmost operator applies first, so rows of c come back even if they were in b. ## Why the difference matters Take concrete sets: a = {1}, b = {1, 2}, c = {2}. - `a UNION (b INTERSECT c)` = {1} ∪ {2} = {1, 2}. - `(a UNION b) INTERSECT c` = {1, 2} ∩ {2} = {2}. Same text under two readings, different results, and neither is obviously wrong on inspection. The failure mode in production is a query that returns plausible-looking data and is simply answering the wrong question — the sort of bug that survives review because nobody's eye catches the missing parentheses. ## The portability wrinkle This is where the question stops being trivia. Set-operator precedence has not been implemented uniformly across engines and versions; implementations have existed that evaluate all set operators strictly left to right, giving `(a UNION b) INTERSECT c` for the first query above. A query that is correct on one engine can therefore be silently wrong on another, or after an upgrade, with no error and no warning. Because this is exactly the class of divergence you cannot see in a diff, the defensive rule is worth stating flatly: > Any statement that combines more than one kind of set operator gets explicit parentheses. With parentheses the grouping is not a matter of engine behaviour at all — every implementation honours them, and the reader no longer has to know the rule. ```sql (SELECT id FROM a) UNION (SELECT id FROM b INTERSECT SELECT id FROM c); ``` ## Formatting that makes grouping visible Parentheses only help if the reader can see them, and a three-branch set operation reads badly as a flat block. Two habits help. Indent nested operands so the structure is visually apparent, as above. Or lift each operand into a CTE and combine the named results, which turns the grouping into something you can read aloud: ```sql WITH shared AS ( SELECT id FROM b INTERSECT SELECT id FROM c ) SELECT id FROM a UNION SELECT id FROM shared; ``` The second form has the extra benefit that each piece can be run and checked on its own, which is how you debug a mixed set-operator query that is returning the wrong count. ## Related rules to keep separate Precedence among set operators is a distinct question from two others that come up alongside it: - **`ALL` versus the default.** `UNION ALL` and `UNION` have identical precedence; the `ALL` keyword changes duplicate handling, not grouping. Mixing `UNION ALL` and `UNION` in one statement is legal and often accidental — worth flagging, because the deduplicating operator's placement changes which duplicates survive downstream. - **Where `ORDER BY` attaches.** It applies to the entire query expression regardless of the operators inside it, so it always goes last, never inside a grouping. ## What an interviewer is checking Partly whether you know the rule — `INTERSECT` first, then `UNION`/`EXCEPT` left to right. Mostly whether you recognise that *knowing the rule is not the answer*. The senior answer is that you parenthesize regardless, because the cost is zero, the intent becomes explicit for the next reader, and you stop depending on a behaviour that has not been uniform across implementations. A candidate who recites the precedence table and then writes the unparenthesized query has missed the point of the question.