NOT IN is a negative predicate for excluding matching values from a constant list or subquery result. It is concise for small, known lists, but NULL values can make an apparently valid exclusion return no rows. section 2.1 section 2.11
The supplier-state trap
A promotion query attempts to find customers in states without suppliers: `WHERE state NOT IN (SELECT state FROM erp.suppliers)`. Although a customer exists in Kansas, the query returns zero rows because at least one supplier has a NULL state. The NULL makes the NOT IN predicate unable to resolve TRUE. Diagnose the cause with `SELECT * FROM erp.suppliers WHERE state IS NULL;`. section 2.1
Use NOT IN safely when:
- The excluded values are constants and the compared value is not unexpectedly NULL. section 2.1 section 2.1.2
- The subquery is guaranteed not to return NULL values; otherwise prefer NOT EXISTS. section 2.1.2
- You have checked scale and the execution plan, because NOT IN subqueries may switch from a fast hashed subplan to a much slower plain subplan. section 2.1.1
Listing 2.1 Example queries with IN/NOT IN
SELECT * FROM (VALUES ('Harper', 'salesperson'),
('Parker', 'driver'),
('Riley', 'electrician'),
('Skyler', 'manager'))
AS t(first_name, job_title)
WHERE job_title IN ('salesperson', 'driver', 'electrician');
first_name | job_title
------------+-------------
Harper | salesperson
Parker | driver
Riley | electrician
(3 rows)
SELECT * FROM (VALUES ('Harper', 'salesperson'),
('Parker', 'driver'),
('Riley', 'electrician'),
('Skyler', 'manager'))
AS t(first_name, job_title)
WHERE job_title NOT IN ('manager');
first_name | job_title
------------+-------------
Harper | salesperson
Parker | driver
Riley | electrician
(3 rows)
Compare ordinary IN and NOT IN with constant values before applying the predicate to nullable subquery results. section 2.1
For relational exclusion, rewrite the supplier query with NOT EXISTS and guard the outer value: `WHERE NOT EXISTS (SELECT FROM suppliers s WHERE ccd.state = s.state) AND ccd.state IS NOT NULL`. PostgreSQL can plan this form as a hash anti-join, producing predictable exclusion and a more scalable strategy. section 2.1.2
