SQL NOT IN with NULL Handling

SQL NOT IN with NULL Handling is a SQL statement in the Subqueries category. NOT IN returns zero rows if the subquery contains any NULL. Use NOT EXISTS or ensure subquery excludes NULLs. The syntax is SELECT * FROM t WHERE col NOT IN (SELECT col FROM t2); -- NULL risk!. It returns nULL-safe comparison. A typical example: CREATE TABLE depts (id INT); INSERT INTO depts VALUES (10), (20), (NULL); -- SURPRISE: returns 0 rows! SELECT * FROM employees WHERE dept_id NOT IN (SELECT id FROM depts); -- Why? NULL comparison produces UNKNOWN, not TRUE -- WHERE 5 NOT IN (10, 20, NULL) = WHERE 5 != 10 AND 5 != 20 AND 5 != NULL -- = TRUE AND TRUE AND UNKNOWN = UNKNOWN = not returned… A close relative is Scalar Subquery, which a subquery that returns a single value (one row, one column). Can be used in SELECT, WHERE, HAVING clauses.