How do you rewrite WHERE city = 'Berlin' OR zip = '10115' so each predicate can use its index?
answer
- no single index decides the whole predicate
- disjunction distributes over the query
- each branch gets its own SELECT
- watch rows that satisfy both branches
- the exclusion guard must survive NULLs
basics
~20 sSplit the OR into two separate SELECT statements, each with a single-column predicate, and combine them with UNION. Each branch can then be served by its own index, while UNION removes the rows that satisfy both conditions and would otherwise appear twice.
solid answer
~50 sAn `OR` across **different columns** cannot be satisfied by one index seek: neither index alone can prove a row is excluded, so the engine either combines two index scans (where it supports and chooses that) or falls back to reading the whole table. The portable rewrite is to give each disjunct its own query: ```sql SELECT ... FROM addresses WHERE city = 'Berlin' UNION SELECT ... FROM addresses WHERE zip = '10115'; ``` Now each branch is a single-column equality that a matching index can seek. The correctness catch is duplicates: a row with both `city = 'Berlin'` and `zip = '10115'` qualifies in both branches. `UNION` deduplicates and fixes that, at the price of a dedup step — and it also collapses genuine duplicate rows that `OR` would have returned. `UNION ALL` is cheaper but needs a NULL-safe guard in the second branch to exclude rows the first already returned. An `OR` over the *same* column is different: write it as `IN (…)`, which optimizers handle natively.
code
sql · 9 lines-- Anti-pattern: one predicate no single index can decide
SELECT id, city, zip
FROM addresses
WHERE city = 'Berlin' OR zip = '10115';
-- Rewrite: one indexable branch each, UNION removes the overlap
SELECT id, city, zip FROM addresses WHERE city = 'Berlin'
UNION
SELECT id, city, zip FROM addresses WHERE zip = '10115';go deeper
Know that an OR across two different columns is harder for the engine than a single equality, and that the standard rewrite is one SELECT per condition combined with UNION.
Explain why no single index decides the predicate, produce the UNION rewrite, and handle the overlap: rows matching both branches, and the NULL-safe form of the exclusion guard.
Show judgment about when not to rewrite — poor selectivity, ON-clause ORs, readability cost — and confirm with a plan that the split actually changed the access path rather than assuming it did.
Decide how much such hand-rewriting a codebase should carry. Duplicated query bodies are a maintenance liability, so weigh them against schema changes or a narrower query surface that removes the need.
## Why an OR across columns is awkward An index is an ordered structure over specific columns. A predicate on `city` can be answered by an index on `city` because the matching rows sit together in the index. The moment you write `city = 'Berlin' OR zip = '10115'`, no single index can decide membership: a row rejected by the `city` index may still qualify through `zip`, so scanning the `city` index alone would return a wrong answer. The engine's options are to combine results from two index scans, or to read the table and evaluate the whole expression per row. Which of those it can do, and whether it chooses to, varies by engine and by how selective the predicates look — but as a query author you should recognise the shape and know the rewrite before you look at a plan. ## The rewrite Disjunction distributes over the query: `WHERE p OR q` is the union of the rows matching `p` and the rows matching `q`. Written out: ```sql SELECT id, city, zip FROM addresses WHERE city = 'Berlin' UNION SELECT id, city, zip FROM addresses WHERE zip = '10115'; ``` Each branch is a bare-column equality. An index on `city` serves the first, an index on `zip` serves the second, and the engine reads only matching rows in each. ## The duplicate trap This is the part interviewers probe. A Berlin address whose zip is `10115` satisfies **both** branches. With `UNION ALL` it comes back twice — a result the original `OR` never produced. Two ways out: **`UNION`** removes duplicate rows across the combined result. That is correct for the overlap, but note it removes *all* duplicate rows, including two genuinely distinct rows that happen to be identical in the selected columns. If your select list includes a unique key, that risk disappears. **`UNION ALL` plus a guard** keeps the cheaper set operator by excluding from the second branch anything the first already returned: ```sql SELECT id, city, zip FROM addresses WHERE city = 'Berlin' UNION ALL SELECT id, city, zip FROM addresses WHERE zip = '10115' AND (city <> 'Berlin' OR city IS NULL); ``` The `OR city IS NULL` is not decoration. `city <> 'Berlin'` evaluates to unknown when `city` is NULL, and `WHERE` keeps only rows that are true, so without it every row with a NULL city and the right zip would silently vanish. Where an engine implements the standard's `IS DISTINCT FROM`, `city IS DISTINCT FROM 'Berlin'` says the same thing more compactly; not every engine has it. ## What the rewrite costs you The query body is duplicated. On a five-table join with a long select list, that is a real maintenance burden and a real chance of the two branches drifting apart. So this is not a style rule to apply everywhere — it is a targeted fix for a query you have measured. Reach for it when the table is large, the individual predicates are selective, and the plan shows a full scan where you expected index access. ## The cases that need no rewrite **Same column, several values.** `status = 'NEW' OR status = 'HOLD'` is exactly `status IN ('NEW','HOLD')`, and optimizers normalise between the two forms routinely. One index on `status` serves it as a small set of seeks. Write the `IN` for readability, not for speed. **One branch is not selective.** If `city = 'Berlin'` matches 40% of the table, splitting achieves nothing: reading the table once is cheaper than reading 40% of it through an index and then unioning. The rewrite only pays when both branches are narrow. **The OR is inside an outer join's ON clause or spans two tables** — `a.x = 1 OR b.y = 2` — which changes which rows survive; splitting that requires far more care than a two-branch union, and is usually better solved by restructuring the query. ## Related shapes to recognise The same disjunction problem drives the "optional filter" idiom `(:city IS NULL OR city = :city)`, and the `OR` chain built by an ORM from a list of conditions. Both are the same smell: a `WHERE` clause whose truth cannot be decided from one ordered column. ## How to present this in an interview Name the shape (`OR` across different columns), state why one index cannot serve it, give the `UNION` rewrite, and volunteer the duplicate trap and the NULL-safe guard before you are asked — that last detail is what separates a memorised rewrite from an understood one. Then add the caveat that you would confirm with a plan first, and that the same-column case is a non-issue.
- Why is UNION ALL not a drop-in replacement for UNION in this rewrite?Because the branches overlap. A row satisfying both disjuncts qualifies in both SELECTs, and `UNION ALL` keeps both copies, so the rewrite returns rows the original `OR` returned once. Either use `UNION` to deduplicate, or keep `UNION ALL` and add a NULL-safe predicate to the second branch excluding rows the first already matched.
- Does an OR between two values of the same column need this treatment?No. `status = 'NEW' OR status = 'HOLD'` is equivalent to `status IN ('NEW','HOLD')`, and optimizers normalise between the forms. A single index on `status` can serve it as two narrow seeks, so the union rewrite adds duplication for nothing. Write `IN` for readability.
- When would you leave the OR alone?When either disjunct matches a large share of the table, so index access would be no cheaper than one scan; when the predicates sit inside an outer join's ON clause, where splitting changes which rows survive; and whenever the plan already shows acceptable access. The rewrite duplicates the query body, so it needs a measured reason.
saying these in an interview costs you the question
- Claims OR always prevents any index use
- Uses UNION ALL and returns overlap rows twice
- Writes the guard as city <> 'Berlin', dropping NULL rows
- Treats same-column OR as needing the union rewrite
- Applies the rewrite everywhere without checking a plan