Set Membership (∈ / IN, ∉ / NOT IN)

Syntax

attr ∈ { value1, value2, ... }      attr IN { value1, value2, ... }
attr ∉ { value1, value2, ... }      attr NOT IN { value1, value2, ... }
attr IN (value1, value2, ...)       SQL's parentheses, with at least two values
attr IN (relation)                  attr NOT IN (relation)    a relation of one column

σ dept ∈ {"hr", "eng"} (Employees)
σ dept NOT IN {"mgmt"} (Employees)

Description

The membership test keeps rows whose value is one of a listed set (∈ / IN), or is NOT one of them (∉ / NOT IN). It is the tidy way to write "status is new, pending, or paid" without a long chain of OR equalities.

Technical Description

∈ forms an ElementOfPredicate against a set literal {…}; ∉ is its negated form. Equivalent to a disjunction of equalities but expressed once. ASCII: IN, and NOT IN (two tokens). The set literal holds constant operands.

SQL's x IN (1, 2) is the same set as x IN {1, 2}. A parenthesis is read as a list when it holds a comma; one value in parentheses is a single expression, so a one-member list is written in braces, x IN {1}. status IN ('OPEN') is refused at analysis, naming that rewrite, status IN {'OPEN'}; a quantifier's list is not so read, and status = ANY ('OPEN') is a list of one. A row of columns tests membership of a list of rows, (a, b) IN {(1, 2), (3, 4)}: see row values.

IN over a relation. x IN (π v (S)) tests membership of a one-column relation's values: it is x = ANY (π v (S)), and x NOT IN (…) is x ≠ ALL (…), with the same three-valued answer as SQL's. The relation is a subquery and may read the row being tested through qualified names. Over a relation holding a NULL, NOT IN keeps no row: a value not matching the others might have matched the unknown one. Filter the NULLs out of the relation, (π v (σ v IS NOT NULL (S))), or use NOT EXISTS or an ANTI join, which have no such trap.

Examples

One of several departments:

σ dept ∈ {"hr", "eng", "finance"} (Employees)

SELECT dept IN {"hr", "eng", "finance"} (Employees)

Exclude a set of statuses:

σ status NOT IN {"cancelled", "refunded"} (Orders)

σ status ∉ {"cancelled", "refunded"} (Orders)

SELECT status NOT IN {"cancelled", "refunded"} (Orders)

Numeric set:

σ priority IN {1, 2, 3} (Tickets)

σ priority ∈ {1, 2, 3} (Tickets)

SELECT priority IN {1, 2, 3} (Tickets)

Worked Example

One membership test standing in for a chain of equalities:

Tickets := [
| id | priority | status    |
|----|----------|-----------|
| 1  | 1        | open      |
| 2  | 2        | cancelled |
| 3  | 3        | open      |
| 4  | 5        | refunded  |
];

query { σ priority ∈ {1, 2, 3} (Tickets) };

Tickets := [
| id | priority | status    |
|----|----------|-----------|
| 1  | 1        | open      |
| 2  | 2        | cancelled |
| 3  | 3        | open      |
| 4  | 5        | refunded  |
];

query { SELECT priority IN {1, 2, 3} (Tickets) };

 id  priority  status
 ──  ────────  ─────────
  1         1  open
  2         2  cancelled
  3         3  open
(3 rows)

Select  ~3 rows
└─ Scan Tickets  ~4 rows

The same rows come back from σ priority = 1 ∨ priority = 2 ∨ priority = 3; the set literal is the readable spelling of that disjunction.

∉ is the exclusion form, and reads the way you would say it out loud — "not cancelled or refunded":

query { σ status ∉ {"cancelled", "refunded"} (Tickets) };

query { SELECT status NOT IN {"cancelled", "refunded"} (Tickets) };

 id  priority  status
 ──  ────────  ──────
  1         1  open
  3         3  open
(2 rows)

Select  ~1 rows
└─ Scan Tickets  ~4 rows

Limitations

A NULL value on either side makes the test UNKNOWN and the row is dropped: a NULL column is neither in the set nor out of it, and if the set holds a NULL then failing to match the other elements settles nothing — the unknown one might have been the value — so ∉ keeps that row no more than ∈ does.

The members compare with the column as = does, so a NUMBER column takes no STRING member: seconds IN {1320, '1400'} is refused at analysis, naming the number to write instead. A member of '' is refused the same way, since an empty cell is NULL: test for it with seconds IS NULL.

Alternatives

A SEMI join (⋉) also keeps the rows whose value appears in another relation, and an ANTI join (▷) the rows whose value does not. A chain of = … ∨ = … is the long-hand form IN replaces.

See Also

or, comparison, row-value, quantified, exists, subquery, semi-join, select

Notes

Prefer IN over a long OR chain for readability; for dynamic sets drawn from data, reach for a semi join.