NOT IN
NOT IN is a SQL predicate that tests whether a value is absent from a specified list or subquery result, subject to null semantics.
PostgreSQL Mistakes and How to Avoid Them
NOT IN is a SQL predicate that tests whether a value is absent from a set of values or subquery results, subject to SQL null semantics.
- How it is used
- Uses a negative predicate to exclude matching values, especially for small constant lists or carefully constrained subqueries.
- How deep it goes
- Explains predicate behavior, NULL failures, diagnosis, plan changes, and NOT EXISTS and anti-join alternatives.
- Examples
- Demonstrates constant lists, supplier states, NULL-state diagnosis, customer emails, and EXPLAIN output.
- What you can do after
- Diagnose NULL-sensitive exclusions, choose NOT EXISTS, and inspect PostgreSQL plans for scalability.
Read how PostgreSQL Mistakes and How to Avoid Them explains NOT IN →
Learn SQL in a Month of Lunches
The NOT IN operator is an SQL membership operator that tests whether an expression matches none of the values in a specified list or set.
- How it is used
- Filters rows by excluding values from a literal list or the results of a subquery.
- How deep it goes
- Demonstrates list and subquery forms, contrasts ANY and ALL, and notes subquery processing costs.
- Examples
- Uses excluded prices, titles absent from order 1034, and equivalent single-value exclusion conditions.
- What you can do after
- Apply it in SQL to exclude known values or values returned by another query.
Related concepts
- nested queryuses · A nested query is a query embedded within another query or statement whose result participates in evaluating the containing operation.
- missing valueuses · A missing value is a marker or condition indicating that an expected data value or reference is absent, unavailable, unknown, or inapplicable.
- membership operatoralternative to · A membership operator is a language construct that tests whether a specified value or property is present in a supported collection, range, or object.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.