8.4 IN / NOT IN

Level: 8 | Version: v1.1 | Author: Meptrasoft

Overview

The IN operator allows you to easily specify multiple values in a WHERE clause.

Instead of chaining multiple OR conditions, IN provides a clean and compact way to match any value against a list.

IN → Matches ANY value in the list
NOT IN → Excludes ALL values in the list

Practice Table

This topic reuses the employees table from 8.1 The WHERE Clause.

Using IN

Instead of writing verbose multiple OR statements:

-- Long syntax:
SELECT *
FROM employees
WHERE dept_id = 10 OR dept_id = 20;

You can write concise SQL using IN:

-- Clean syntax with IN:
SELECT *
FROM employees
WHERE dept_id IN (10, 20);

This returns all employees belonging to department 10 OR 20:

emp_id | emp_name     | dept_id | salary
1      | John Smith   | 10      | 45000
2      | Emma Brown   | 20      | 60000
3      | Samuel Reed  | 10      | 32000
5      | Sarah Wilson | 20      | 48000
6      | Tom Watson   | 10      | 51000

Using NOT IN

NOT IN returns rows where the column value is not present in the specified list:

SELECT *
FROM employees
WHERE dept_id NOT IN (10, 20);

This filters out all employees in departments 10 and 20, returning only department 30:

emp_id | emp_name | dept_id | salary
4      | Anderson | 30      | 55000

NOT IN Equivalent: dept_id != 10 AND dept_id != 20

Critical Caveat: NOT IN with NULL Values

[!WARNING] If the list in NOT IN contains a NULL, the entire expression evaluates to UNKNOWN, and the query returns zero rows!

-- Returns ZERO rows because (10, NULL) causes UNKNOWN for every comparison:
SELECT * FROM employees WHERE dept_id NOT IN (10, NULL);

Why? Because dept_id != 10 AND dept_id != NULL. Since != NULL is always UNKNOWN, TRUE AND UNKNOWN produces UNKNOWN, which fails the WHERE condition.

Common Beginner Mistakes

Placement Quick Points

IN (A, B, C)
→ col = A OR col = B OR col = C

NOT IN (A, B, C)
→ col != A AND col != B AND col != C

• IN tests membership in a set of values or subquery results.
• NOT IN excludes all values present in the set.
• Much cleaner and more readable than multiple chained OR / AND statements.
• Beware of NULL in NOT IN lists, which causes queries to return zero rows.

Interview Questions

What does the IN operator do in SQL?

IN determines whether a specified value matches any value in a list or subquery.

How is IN related to the OR operator?

column IN (val1, val2) is shorthand syntactic sugar for column = val1 OR column = val2.

How does NOT IN behave compared to AND?

column NOT IN (val1, val2) expands to column != val1 AND column != val2.

What happens if a NOT IN list contains a NULL value?

The condition evaluates to UNKNOWN for all rows, resulting in an empty query result set (zero rows returned).

Can IN be used with subqueries?

Yes! WHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 100000) is standard SQL.

Practice & Hands-On Exercises

Using the employees table:

1. Find all employees who work in department 10 or 30:

SELECT * FROM employees WHERE dept_id IN (10, 30);

2. Find all employees who do not work in department 10:

SELECT * FROM employees WHERE dept_id NOT IN (10);

3. Rewrite emp_name = 'John Smith' OR emp_name = 'Emma Brown' using IN.
4. Test what happens when you run SELECT * FROM employees WHERE dept_id NOT IN (10, NULL);.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

IN     → MATCH ANY (OR)
NOT IN → EXCLUDE ALL (AND !=)

Use IN to match against multiple candidate values cleanly, and ensure NOT IN lists do not contain NULL values.

End of IN / NOT IN