Features
SQL NOT IN excludes rows whose column values do not match any item in a supplied list or subquery.
SQL NOT IN returns rows whose column values do not match any value in a supplied list or subquery. It is the negative form of the IN operator and filters data by exclusion.
Use NOT IN to exclude unwanted status codes, remove test records, or locate rows missing from a related table—situations where a known set of values must be filtered out.
Place NOT IN after the column name and provide a parenthesized list or subquery: SELECT * FROM orders WHERE status NOT IN ('canceled','returned');
If the list or subquery contains NULL, NOT IN returns zero rows because comparisons with NULL are unknown. Remove NULLs or add WHERE col IS NOT NULL inside the subquery.
Yes. A subquery makes NOT IN dynamic: SELECT * FROM customers c WHERE c.id NOT IN (SELECT customer_id FROM orders); This excludes customers who placed any order.
Index the filtering column and the subquery column. For large datasets, test NOT EXISTS or a LEFT JOIN … IS NULL, which often outperform NOT IN.
NOT EXISTS evaluates each outer row and stops on the first match, while NOT IN materializes the entire list. With good indexes, NOT EXISTS is usually faster on big tables.
Use NOT EXISTS, a LEFT JOIN followed by IS NULL, or filter NULLs within the subquery to avoid the “all rows filtered” surprise.
Galaxy’s AI copilot autocompletes NOT IN syntax, warns about NULL pitfalls, and suggests more efficient NOT EXISTS rewrites directly inside its blazing-fast SQL editor.
The query below finds active users who did not log in this month by excluding IDs from the sessions table.SELECT u.id, u.email
FROM users u
WHERE u.status = 'active'
AND u.id NOT IN (
SELECT DISTINCT s.user_id
FROM sessions s
WHERE s.login_at >= DATE_TRUNC('month', CURRENT_DATE)
);
Always remove NULLs, keep lists short, index participating columns, and benchmark NOT EXISTS for large subqueries.
Data engineers regularly need to exclude control values, orphan records, or invalid states. NOT IN offers a readable, set-based way to achieve this goal. Understanding its NULL behavior and performance characteristics prevents silent logic bugs and slow queries that impact dashboards and applications. Mastering NOT IN—and its alternatives—equips analytics teams to deliver reliable results faster, especially when paired with modern tools like Galaxy.
Yes, the logical behavior is standardized, but execution plans and NULL handling quirks can vary. Always test on your specific database.
Add WHERE col IS NOT NULL inside the subquery, or rewrite the logic with NOT EXISTS or LEFT JOIN … IS NULL.
On large tables, NOT EXISTS is often faster because the optimizer stops searching on the first match. Benchmark both with indexes.
Galaxy’s AI copilot generates syntactically correct NOT IN statements, flags potential NULL issues, and recommends faster patterns—all inside its desktop SQL editor.
.avif){kind=logo}
