How to use the SQL IN Operator with an Expression List

In this video we’ll walk you though how to use the SQL IN operator; we’ll show you why you would want to use the IN operator and how to avoid some pitfalls associated with NULL.

The SQL IN operator matches a column to a list.  Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server!

Watch the video and then check the sample code below.

SQL IN Operator

The SQL IN operator is a great way to condense and simplify your SQL WHERE conditions. If you have several OR conditions, then it makes sense to use SQL IN.

To compare a column to several values use the SQL IN Operator

SELECT CountryID
      ,CountryName
      ,IsoAlpha3Code
 FROM  Application.Countries
 WHERE IsoAlpha3Code IN ('JAM','JOR', 'JPN')

Here is the result

This is similar to using the OR operator. You may have done this in the past. Here is the equivalent statement using OR’s:

SELECT CountryID
      ,CountryName
      ,IsoAlpha3Code
 FROM  Application.Countries
 WHERE IsoAlpha3Code = 'JAM' OR
       IsoAlpha3Code = 'JOR' OR
       IsoAlpha3Code =  'JPN'

NOT IN Operator

To exclude a list of values from your result, use NOT IN. In this example we’re returning every country except JAM, JOR, or JPN.

SELECT CountryID
       ,CountryName
       ,IsoAlpha3Code
  FROM  Application.Countries
  WHERE IsoAlpha3Code NOT IN ('JAM','JOR', 'JPN')

When I run this, I get many more countries back.

I can also use the SQL IN Operator with other datatype, such as integers. In the following example we’ll use the counties’ IsoNumericCodes.

SELECT CountryID
       ,CountryName
       ,IsoAlpha3Code
       ,IsoNumericCode
 FROM  Application.Countries
 WHERE IsoNumericCode IN ( 388, 392, 400)

One thing I want to point out is that if your list has null values, you can get some weird results so let me show you a list here where we have NULL in and I run it.

SELECT CountryID
       ,CountryName
       ,IsoAlpha3Code
  FROM  Application.Countries
  WHERE IsoAlpha3Code IN ('JAM','JOR', 'JPN', NULL)

SQL IN and NULL

Here, we have null and I get three rows back, seems expected because I have the three that match, which makes sense, but if I put a NOT here and I run this, you think I would be getting everything back that’s not these three rows like in the past. There are like maybe 100 or so rows, but when I execute this, I don’t get any rows back.

SELECT CountryID
       ,CountryName
       ,IsoAlpha3Code
  FROM  Application.Countries
  WHERE IsoAlpha3Code NOT IN ('JAM','JOR', 'JPN', NULL)

It seems strange, doesn’t it?

…and it’s because none of the rows that I have in my original table have null in the IsoAlpha3Code.

So let me take the NULL out and now run this and as you see, I get all the rows back.

Here’s a case where having the NULL value in the list can cause some confusion and I think you need to be aware that that can cause an issue.

So I hope you enjoyed this SQL minute and you can use IN to help you advance your techniques of using another select statement.