What does WHERE status IN ('new','open') mean, and does repeating a value in the list change the result?

## The definition The `IN` predicate with an explicit value list is defined by the standard as a disjunction of equality comparisons: ```sql WHERE status IN ('new', 'open', 'pending') -- means exactly: WHERE status = 'new' OR status = 'open' OR status = 'pending' ``` (The standard expresses this as an equivalence with the quantified comparison `= ANY (list)`, which is the same idea.) `IN` exists for readability: one column reference instead of three, and one place to edit when the list changes. ## It is a row filter, not a row multiplier This is the property interviewers probe. `IN` is evaluated once per row of the input and produces a single truth value, so each candidate row is either kept or dropped. Consequences: - **Duplicates in the list are irrelevant.** `status IN ('new','new','open')` keeps exactly the rows `status IN ('new','open')` keeps. A row matching `'new'` appears once, not twice. - **Order is irrelevant.** The list is a set of candidate values, not a sort key; it has no effect on the order of the result, which is undefined without `ORDER BY` anyway. - **Cardinality is bounded by the input.** The result can never have more rows than the table being filtered. Compare that with joining to a list of values: ```sql SELECT o.* FROM orders o JOIN (VALUES ('new'), ('new'), ('open')) AS s(status) ON o.status = s.status; -- PostgreSQL syntax ``` Here the duplicate `'new'` on the right side is a second matching row, so every `'new'` order comes back **twice**. Same intent, different row multiplicity. If you ever rewrite an `IN` list into a join, deduplicate the value set or use a semi-join form so the multiplicity is preserved. ## Empty lists `WHERE id IN ()` is a **syntax error** in standard SQL and in the major engines — not a predicate that matches nothing. Any code that assembles the list from a collection must branch on the empty case: either skip the predicate entirely (if "no filter" is the intent) or emit a predicate that is deliberately false, such as `WHERE 1 = 0`. This is one of the most common runtime failures in query-building code, because it only fires when the caller's list happens to come back empty. ## Types in the list Every element is compared against the column, so the column's type governs. `WHERE user_id IN ('1','2')` on an integer column forces an implicit conversion of each literal, and `WHERE code IN (1, 'A')` on a text column may error or convert depending on the engine. Keep the list homogeneous and of the column's type; it removes an entire class of surprises and keeps the comparison honest. ## NOT IN over a value list `x NOT IN (a, b)` negates the disjunction and so expands to `x <> a AND x <> b` — note the `AND`. Every element must be unequal for the row to qualify. Take care that a list which can carry a NULL element changes the picture, because a comparison with NULL yields UNKNOWN rather than TRUE. ## Readability and maintenance A short literal list is fine and is idiomatic — status codes, country codes, a handful of ids from a fixture. When the list is long, or the same list appears in several queries, the value set is really data: putting it in a table (or passing it as a parameterised set) keeps the queries stable while the membership changes. As a rule of thumb, if you would not want to read the list aloud, it does not belong inline in the statement. ## What to say Give the OR expansion first, then the multiplicity point — `IN` filters, it never duplicates — because that is what distinguishes a candidate who understands it from one who has only used it. Add the empty-list syntax error if you have written query-building code, because it is the failure people actually hit in production.