SQL IN Operator

Basic IN Example

The IN operator in SQL is used in the WHERE clause to match a column's value against a list of specific values. It simplifies multiple OR conditions and improves readability. Instead of writing:

SELECT * FROM employees
WHERE department = 'HR' OR department = 'Finance' OR department = 'IT';

You can write the same logic more cleanly using IN:

SELECT * FROM employees
WHERE department IN ('HR', 'Finance', 'IT');

This query returns all employees whose department is either HR, Finance, or IT. The list inside IN (...) can contain any number of values.

The IN operator is especially useful when working with values from dropdowns, filters, or user selections in applications.

It can also be used with subqueries:

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'Canada');

This example fetches orders placed by Canadian customers.

IN with Numbers

Filters rows where the order ID matches any value in the list.

SELECT * FROM orders
WHERE order_id IN (101, 103, 109);

IN with Strings

Matches the name column against a list of strings.

SELECT * FROM students
WHERE name IN ('Alice', 'Bob');

NOT IN

Excludes rows that match values in the list.

SELECT * FROM products
WHERE category NOT IN ('Electronics', 'Furniture');

IN with Subqueries

The IN operator can be combined with subqueries to dynamically filter results based on values from another table. This is extremely useful when filtering rows based on relationships between tables.

SELECT name FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE location = 'NY'
);

In this example:

The outer query selects the name of employees from the employees table.
The subquery fetches id values from the departments table where the location is 'NY'.
The IN operator checks if each employee’s department_id exists in the list of department IDs returned by the subquery.

This query effectively returns employees who work in departments located in New York, without needing a direct join.

Using IN (subquery) is a concise alternative to JOINs when you only need to filter based on values from another table, not retrieve data from it.

Best Practices

Use IN for clean alternatives to multiple ORs
Avoid NOT IN if the list may contain NULLs
For large subqueries, consider EXISTS or JOINs for better performance

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic

Menu