NOT IN vs NOT EXISTS

NOT IN vs NOT EXISTS is a SQL statement in the Subqueries category. NOT IN returns no rows if the subquery returns any NULL. NOT EXISTS works correctly with NULLs. The syntax is SELECT * FROM table1 WHERE col NOT IN (SELECT col FROM table2);. It returns nULL-safe comparison. A typical example: -- NOT IN with NULL risk: SELECT * FROM employees WHERE dept_id NOT IN (SELECT id FROM departments); -- If ANY department.id is NULL, this returns ZERO rows! -- Because NULL comparison = unknown, not true -- Safe alternative: SELECT * FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM departments d WHERE d.id = e.dept_id ); -- Works correctly regardless of NULLs -- Where NOT IN is safe:… 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.