Interactive SQL Tutorial

SQL IN and NOT IN Operators

September 22, 2022August 25, 2026

Last updated: August 25, 2026.

The SQL IN operator tests whether a value matches any value in a list or subquery. It is usually clearer than repeating the same column in several OR conditions.

Match a list of text values

SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name IN ('Mary', 'Sam');

This is equivalent to testing first_name = 'Mary' OR first_name = 'Sam'.

Match numeric values

SELECT employee_id, benefit_amount
FROM employee_statistics
WHERE benefit_amount IN (12000, 15000);

Exclude listed values

SELECT employee_id, department
FROM employees
WHERE department NOT IN ('Sales', 'Support');

NULL warning: if the list or subquery used by NOT IN contains NULL, the comparison can become unknown and return no rows. Exclude nulls in the subquery or use NOT EXISTS.

Use a subquery

SELECT customer_id, company_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_date >= '2026-01-01'
);

Use parameters in application code

Do not build an IN list by joining untrusted strings. Generate one parameter placeholder for each value and bind every value through the database driver. For an empty input list, handle the case explicitly rather than generating IN ().

admin

Leave a Reply Cancel reply