Filtering data with IN and NOT IN

Imagine you're working with a university database and you need to find students who are taking specific courses. For example, "Programming", "Math", and "Physics". Sure, you could write a long query with a bunch of conditions like:

SELECT *
FROM students 
WHERE course = 'Programming'
   OR course = 'Math'
   OR course = 'Physics';

But let's be real. Writing stuff like that is a pain and it doesn't look very slick. Luckily, the IN operator comes to the rescue, letting you write the same query in a much cleaner way and saving you some time:

SELECT *
FROM students 
WHERE course IN ('Programming', 'Math', 'Physics');

Sounds like magic, right? Instead of a bunch of OR conditions, we just tell SQL to look for values in this list. And if you want to check that a value is not in the list, use NOT IN — Find everything that's not in this list.

Syntax of the IN operator

Here's the general syntax for the IN operator:

SELECT columns
FROM table
WHERE column IN (value1, value2, value3, ...);

Now let's check out some examples.

Example 1: Students taking several courses

Let's say we have a students table:

We want to find all students who are taking "Programming", "Math", or "Physics". Let's use IN:

SELECT name, course
FROM students
WHERE course IN ('Programming', 'Math', 'Physics');

Result:

As you can see, the IN operator made things way easier. We don't have to write a bunch of OR conditions, just list the values we're interested in.

Example 2: Students not taking certain courses

Now, let's say you want to find students who aren't taking "Programming", "Math", or "Physics". That's where NOT IN comes in handy:

SELECT name, course
FROM students
WHERE course NOT IN ('Programming', 'Math', 'Physics');

Result:

So, the NOT IN operator returns all rows where the course column's value isn't in the given list.

Using IN and NOT IN with subqueries

The IN and NOT IN operators are especially useful when you need to compare data between two tables. For example, let's say we have two tables:

students table:

courses table:

Let's say we need to find students who are registered for courses that exist in the courses table. Here's where a subquery with IN comes in:

SELECT name
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
);

This query works like this: the subquery SELECT id FROM courses returns a list of all course IDs. Then the IN operator checks if course_id is in that list.

Result:

Why was Dan skipped? Because his course_id (104) isn't in the courses table.

Special cases with NULL

The IN operator has one important quirk: if there's a NULL in the list of values, it can affect your query's result. Let's check out an example.

grades table:

A query looking for students with a grade in ('A', 'B', 'C') might look like this:

SELECT student_id
FROM grades
WHERE grade IN ('A', 'B', 'C');

Result:

The row with NULL in the grade column gets ignored, because NULL isn't considered part of any list.

Now imagine you use the NOT IN operator. For example:

SELECT student_id
FROM grades
WHERE grade NOT IN ('A', 'B', 'C');

You'd expect to see the row with student_id = 2, but the result will be empty! Why? Because NULL gets compared to each value in the list, and the result of that comparison is always unknown (UNKNOWN). This can be confusing, so when working with NOT IN, keep in mind if your columns have NULL values. The best way in these cases is to explicitly check for NULL:

SELECT student_id
FROM grades
WHERE grade NOT IN ('A', 'B', 'C')
   OR grade IS NULL;

Result:

Tips for using IN and NOT IN

Use IN to make your SQL code more readable
If you need to check if a column is in a certain list of values, always go for IN instead of a bunch of OR conditions.

Be careful with NOT IN and NULL
If your data has columns with NULL values, they can cause unexpected results. It's better to handle NULL explicitly when using NOT IN.

Use indexes to speed up subqueries
If you're using IN with a subquery, make sure the column in the subquery is indexed to avoid performance issues.

Real-world example

Imagine you're working in an online store system. You have orders and users tables. You want to find all users who have never placed an order.

users table:

orders table:

Let's use NOT IN to solve this:

SELECT name
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM orders
);

Result:

This query works like this: first, the subquery SELECT user_id FROM orders returns the IDs of all users who placed orders (1 and 3). Then the NOT IN operator filters them out, leaving only those who never placed an order (Mello and Dan).