Oracle SQL中NOT IN子句工作机制及查询实例咨询

Hey there! Let's break down how the NOT IN clause works in Oracle SQL, using your sample data and queries as context.

Basic Mechanism #

At its core, NOT IN filters rows where the column value on the left side does NOT match any value in the list or subquery on the right side.

Using your example data:

• Your employees table has department IDs: 10, 20, 30, ..., 110
• Your departments table has department IDs: 10, 20, ..., 110, 120, ..., 270

If you run this NOT IN query:

SELECT department_id FROM departments 
WHERE department_id NOT IN (SELECT DISTINCT department_id FROM employees)

It will return all department IDs from departments that don't exist in employees — specifically 120, 130, ..., 270.

Here's the step-by-step logic for each row in departments:

• Oracle takes a department ID (e.g., 120)
• It checks if 120 appears anywhere in the subquery result (10-110)
• Since it doesn't, the row is included in the final result.

The Critical NULL Pitfall #

The most common gotcha with NOT IN is how it handles NULL values in the subquery result.

In SQL, NULL represents an unknown value. When you compare any value to NULL (e.g., 120 != NULL), the result is UNKNOWN — not TRUE or FALSE. The WHERE clause only keeps rows where the condition evaluates to TRUE, so UNKNOWN rows get excluded.

Example of the Problem #

Suppose your employees table had a row with department_id = NULL. Your subquery SELECT DISTINCT department_id FROM employees would now include NULL along with 10-110.

Running the same NOT IN query would return no results at all. For every department ID in departments, the check department_id NOT IN (10,20,...,110,NULL) would evaluate to UNKNOWN (because of the NULL comparison), so no rows pass the WHERE filter.

Alternative: NOT EXISTS (Avoids NULL Issues) #

If you want to avoid this NULL problem entirely, use NOT EXISTS instead. It handles NULLs more intuitively because it checks for the existence of matching rows, rather than direct value comparisons.

Here's how to rewrite the query with NOT EXISTS:

SELECT d.department_id 
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 
    FROM employees e 
    WHERE e.department_id = d.department_id
)

Even if employees has NULL department IDs, this query will still correctly return 120-270. That's because when e.department_id is NULL, the condition e.department_id = d.department_id becomes UNKNOWN, and NOT EXISTS treats this as "no matching row found" — so the departments row is included.

内容的提问来源于stack exchange，提问作者Sarun Dahal