SQL Operators: Checking Lists of Values with IN and NOT IN

SQL Operators: Checking Lists of Values with IN and NOT IN

If you’re writing a query that has one value you want to check, then it can be a simple WHERE clause to write:

SELECT id, first_name, last_name
FROM customer
WHERE first_name = 'John';

What if you want to find records that match multiple conditions?

You can use the OR keyword to check if a value matches one or more values.

SELECT id, first_name, last_name
FROM customer
WHERE first_name = 'John'
OR first_name = 'Mary'
OR first_name = 'Susan';

This isn’t so bad when there is only a small list. But it can get messy when the list gets longer.

Surely there’s a better way!

Yes, there is!

It’s called the IN keyword.

The SQL IN Keyword

The IN keyword in SQL lets you check if an expression matches any of the specified values, in a single criteria. It avoids the need for many separate WHERE clauses and has a few other advantages.

You use the IN keyword, with some brackets, and specify your values inside those brackets.

WHERE expression IN (value_1, value_n….)

You can put a lot of values inside the brackets – up to 1,000 actually.

So, to rewrite the earlier example to use the IN clause, it would look like this:

SELECT id, first_name, last_name
FROM customer
WHERE first_name IN ('John', 'Mary', 'Susan');

This will show all customers that have a first_name of John, Mary, or Susan.

This will perform an “equal to” match. It will check if the first_name is equal to any of these values.

If you’re looking to perform other operators, such as greater than, have a read of the ANY and ALL section below.

So why would you use the IN keyword?

It’s easier to type when you have more than one value to check.
It’s easier to add and remove values from your query, rather than removing entire lines of a WHERE clause.
It can be used with multiple values from subqueries.

A common way to use the IN clause is to use subqueries. I’ll have a guide on subqueries coming out soon that goes deeper into that topic, but a subquery is a query inside another query.

Using it with an IN keyword could look like this:

SELECT id, first_name, last_name
FROM customer
WHERE first_name IN (
  SELECT first_name
  FROM common_names
);

This means that all of the first_name values in the common_names table will be used as an input into the WHERE clause on the customer table.

Now, this may not be the most realistic example, but it’s just used to demonstrate the concept.

The SQL NOT IN Keyword

We just looked at the IN keyword, which checks if a value matches one of a range of values.

What if we want to find records that don’t match a range of values?

We can do this with the NOT IN keyword.

In SQL, many of the keywords can be reversed by adding the word NOT in front of them, as you’ll see in this article.

So, while the IN acts like an = on multiple values, NOT IN acts like a <> on multiple values.

WHERE expression NOT IN (value_1, value_n…)

Like the IN, you can put up to 1,000 values inside the NOT IN clause.

Let’s say you had a query like this:

SELECT id, first_name, last_name
FROM customer
WHERE first_name <> 'John'
AND first_name <> 'Mary'
AND first_name <> 'Susan';

This would find all of the customer records where the name is not John, and not Mary and not Susan.

This example uses AND because we want to find records that don’t match all of these values.

We can convert this to NOT IN by adding the values inside the brackets.

SELECT id, first_name, last_name
FROM customer
WHERE first_name NOT IN ('John', 'Mary', 'Susan');

This would show us the same results.

NOT IN can also be used with subqueries in the same way as IN:

SELECT id, first_name, last_name
FROM customer
WHERE first_name NOT IN (
  SELECT first_name
  FROM common_names
);

This means that all of the first_name values in the common_names table will be used as an input into the WHERE clause on the customer table, and all customer records that don’t match those values are returned.

More Information

Here are some more resources on learning about IN: