How to use the IN operator with an Expression List

In this video we’ll walk you though how to use the 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 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!

Once you have watched the video check out the sample code below.  I’ve also included a transcript for you to use.

This is an SQL Minute on the IN Operator!

Welcome to another Essential SQL Minute. In this segment, we’re going to learn how to use the IN clause with an expression list. We’re going to use the in operator to compare a column to a list of values.

IN Operator Comparison

IN Operator Summary

Let’s go to our example here, so we have the IN clause being used to compare the IsoAlpha3Code to a list of one or three values. This is equivalent to comparing the IsoAlpha3Code to those saved values, but using the equal sign in conjunction with an OR so let’s go see how this is done using the query analyzer.

I brought in the query into the query analyzer. Here, you can see I have WHERE clause with that IsoAlpha3Code, the same line and that’s what I run it up.

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

It is up here. You can see it brings back three countries where the IsoAlpha3Code equals JAM, JPN or JOR.

IN Operator Results

IN Operator Example Results

Now, I could also use the NOT with IN and what it will do is bring back every country whose IsoAlpha3Code is not JAM, JOR or JPN, so basically anything that’s not in this list.   What I can do is just put a NOT here (see red text).

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

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

NOT IN Operator Results

NOT IN Operator Example Results

I can also use the IN clause with integers, so for instance, instead of using the IsoAlpha3Code, maybe I use the IsoNumericCode like we had in some other examples in previous lessons. You can see here I’m using it a numeric code. It’s bringing back rows.

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 in it, 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)

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.

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and an MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He is loves helping others learn SQL.