SQL IN and SQL NOT IN – Guide & Examples

The SQL IN and SQL NOT IN keywords allow you to compare against a list of values, instead of a single value.

Learn all about how to use them and see some examples in this guide.

Sample Data

For this guide, we’ll use a simple table called “food”, which has a few rows in it.

You can get the scripts to set up this sample data on my GitHub repository here.

Here are the records in the table:

id	product_name	price
1	Apple	2
2	Chocolate	3
3	Pie	12
4	Bread	5
5	Banana	1
6	Pear	2

There are three columns: a unique ID, the name of the product, and a price.

Let’s see how to use the IN and NOT IN keywords with this data.

 

SQL IN

The SQL IN keyword allows you to compare against multiple values, instead of a single value.

What does this mean? Let’s see an example.

 

Simple Example of SQL IN

Let’s say you wanted to find records in the food table above that were fruit. You know that both Apple, Banana, and Pear are fruit, so you might write a SELECT query that would look for these three values.

SELECT id, product_name, price
FROM food
WHERE product_name = 'Apple'
OR product_name = 'Banana'
OR product_name = 'Pear';

This would show the following results:

id	product_name	price
1	Apple	2
5	Banana	1
6	Pear	2

However, the query we used to write this is could be improved.

We had three values to check, so we wrote several OR keywords and specified the product_name column a few times:

WHERE product_name = 'Apple'
OR product_name = 'Banana'
OR product_name = 'Pear';

This might seem OK for 3 values, but what if we want to check 10 or 50 values?

Our query would get pretty long and messy.

Fortunately, there’s an easier way to do it.

We can use the SQL IN keyword.

The SQL IN keyword allows you to check that a value matches at least one of the specified values inside the IN keyword. It’s one of many operators available in SQL.

You use it in the WHERE clause like this:

WHERE column IN (list_of_values)

Instead of an = sign, you specify IN, and then a pair of brackets. Inside the brackets, you specify one or more values, separated by a comma.

For our example, we can update our query to use IN and it will look like this:

SELECT id, product_name, price
FROM food
WHERE product_name IN (
  'Apple',
  'Banana',
  'Pear'
);

You don’t need to have each entry on a new line, but I find it easier to read, especially as the list gets longer.

If you run this query, you’ll get the same results as above.

id	product_name	price
1	Apple	2
5	Banana	1
6	Pear	2

This SQL query using the SQL IN keyword is cleaner, easier to read, and has less code to read and write.

 

SQL IN Using a SELECT Subquery

Another way you can use the SQL IN keyword is by specifying a SELECT query inside the brackets, instead of a list of values.

You can do this if the values you want to check in your main query exist in your database somewhere.

Here’s an example. Let’s say we have another table that stores the names of food for the bakery section, called “bakery”. The “bakery” table looks like this:

food_name

Bread

Cake

Pie

Cookie

Now we have this table, let’s say we want to see a list of food products from the food table that exist in this bakery table.

One way to do that would be to manually type each value from this table into the IN clause:

... IN (
  'Bread',
  'Cake',
  'Pie',
  'Cookie'
);

This would work but would take a while to enter if it’s a long list. If the data in the table changed, we would need to update our query.

A better way to do this is to get the data from this bakery table and put it into our query. We can do this by adding a SELECT query inside the IN clause (which is a subquery):

SELECT id, product_name, price
FROM food
WHERE product_name IN (
  SELECT food_name
  FROM bakery
);

The results of this query are:

id	product_name	price
3	Pie	12
4	Bread	5

It shows the records in the food table where the name of the product is in the bakery table. In this case, it shows Pie and Bread.

This is a handy technique if you have a list of values in one table and want to filter another table based on the list.

 

SQL IN vs EXISTS – What’s the Difference?

The SQL IN keyword is slightly different to the SQL EXISTS keyword.

The IN keyword checks against all values in the list to find a match. The EXISTS keyword will check against the list inside EXISTS but will return TRUE when the first match is found.

EXISTS is therefore much faster when working with a large result set.

They achieve the same outcome, so you may want to test your query with both IN and EXISTS to see which one performs better and which one you prefer.

 

SQL NOT IN

The SQL NOT IN keyword allows you to check if a value is not in a specified list of values. Just like the IN keyword, you can use either a hard-coded set of values or a subquery.

 

Simple Example of SQL NOT IN

Using our sample data of food, let’s say you wanted to find foods that were not fruit. So, you want to see records that were not Apple, Banana, or Pear.

A query to do that could look like this:

SELECT id, product_name, price
FROM food
WHERE product_name != 'Apple'
AND product_name != 'Banana'
AND product_name != 'Pear';

We check if the product name is not equal to Apple, and it is not equal to Banana, and it is not equal to Pear.

We’ve used the != sign for not equals, but we could alternatively use <> for not equals.

The results of this query are:

id	product_name	price
2	Chocolate	3
3	Pie	12
4	Bread	5

We can see the records that don’t match the names we mentioned.

However, just like with IN, once this list starts growing it can be hard to maintain, and it can make the query a lot longer.

We can replace it with an SQL NOT IN keyword.

We specify NOT IN, then the list of values we want to check against inside brackets.

Our query looks like this:

SELECT id, product_name, price
FROM food
WHERE product_name NOT IN (
  'Apple',
  'Banana',
  'Pear'
);

The results for this are the same as the earlier query:

id	product_name	price
2	Chocolate	3
3	Pie	12
4	Bread	5

It shows foods that are not Apple, Banana, or Pear.

Just like with the IN query, this NOT IN query is easier to maintain and has less code.

 

SQL NOT IN Using a SELECT Subquery

Another way you can use the SQL NOT IN keyword is by specifying a SELECT query inside the brackets, instead of a list of values. This works in a similar way to the IN subquery.

You can do this if the values you want to check in your main query exist in your database somewhere.

Here’s an example, using the same data as the IN example. Let’s say we have another table that stores the names of food for the bakery section, called “bakery”. The “bakery” table looks like this:

food_name

Bread

Cake

Pie

Cookie

Let’s say we want to see a list of food products from the food table that do not exist in this bakery table.

One way to do that would be to manually type each value from this table into the IN clause:

... NOT IN (
  'Bread',
  'Cake',
  'Pie',
  'Cookie'
);

This would work but would take a while to enter if it’s a long list. If the data in the table changed, we would need to update our query.

A better way to do this is to get the data from this bakery table and put it into our query. We can do this by adding a SELECT query inside the IN clause:

Like the IN example, a better way to do this is to get the data from this bakery table and put it into our query. We can do this by adding a SELECT query inside the NOT IN clause:

SELECT id, product_name, price
FROM food
WHERE product_name NOT IN (
  SELECT food_name
  FROM bakery
);

The results of this query are:

id	product_name	price
1	Apple	2
2	Chocolate	3
5	Banana	1
6	Pear	2

It shows products that are not in the bakery table.

 

NOT IN and NULL Values

One thing to know when working with NOT IN is how NULL values are treated:

If there are any NULL values inside the brackets of a NOT IN keyword, then the entire check returns as “not found”, even if a match is found.

For example, we add a record to our “bakery” table that has a NULL value for the food_name:

food_name

Bread

Cake

Pie

Cookie

(NULL)

We can then run the same NOT IN query as before:

SELECT id, product_name, price
FROM food
WHERE product_name NOT IN (
  SELECT food_name
  FROM bakery
);

If we run this, we’ll get this result:

no rows found

This is because there is one row in the bakery table that has a NULL food_name value. It has caused the entire WHERE clause to return false – even though there are other food values that are not in the bakery table.

So, that’s something to keep in mind.

To overcome this, you can add an IS NOT NULL check to the subquery:

SELECT id, product_name, price
FROM food
WHERE product_name NOT IN (
  SELECT food_name
  FROM bakery
  WHERE food_name IS NOT NULL );

Here are the results:

id	product_name	price
1	Apple	2
2	Chocolate	3
5	Banana	1
6	Pear	2

It shows products that are not in the bakery table.

 

How Many Values Can Go In the IN Keyword?

Most databases can support up to 1,000 values inside the IN or NOT IN keyword. If you add more than that, you’ll get an error when you run the query.

To avoid this, you can either:

• Separate the query into separate queries.
• Add a second IN with an OR keyword.
• Add the values to a table, and use a subquery to select from the table

 

Conclusion

The SQL IN and SQL NOT IN keywords are a handy way to simplify your query when checking for multiple values, and can be used with either a hard-coded list of values or a SELECT subquery.