How to use the IN Operator with a SubQuery

In this video we’ll walk you though how to use the IN operator with a Subquery; we’ll show you why you would want to use IN with a subquery.  In it’s simplest form the IN statement matches a column values to a list. TRUE is returned if there is a match.

A main advantage of using subqueries with the IN operator, is the list’s contents are the subquery results.  As we explain below, this provide for a lot of flexibility.

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 with a Subquery

Transcript:

Hi, and welcome to another episode of “Essential SQL Minute.” In today’s episode, we’re going to learn how to use the IN operator with a subquery. You may remember that to compare a list of value you can use the IN operator.

Now, what we’re going to do here is generate that list of values through a query. So as you can see here, I’ve put together a query. The portion that’s listed in green is the subquery.

You can see here where I have a query saying:

 SELECT IsoAlpha3Code FROM Application.Countries where IsoAlpha3Code is like "J% 

So, essentially bring back all the IsoAlpha3Codes that start with the letter J.

So, that’s going to build a list of all the IsoAlpha3Codes beginning with the letter J. I’m going to use that as basically the list for my IN operator.

And then my outer query will then be run and its IsoAlpha3Code compared against the contents of the results from that subquery.

Read More: SQL Subqueries – The Ultimate Guide >>

This will be very similar to running a query where I had, in effect, independently run a query to get all those IsoAlpha3Codes and then manually type them into my query as “JAM”, “JOR”, and “JPN” and then run this as a separate query.

Example Subquery using Like

So, let’s see how this works in Query Manager. So, here I have my subquery with the IN clause. Here’s the subquery.

SELECT CountryID
      ,CountryName
      ,IsoAlpha3Code
FROM  Application.Countries
WHERE IsoAlpha3Code
      IN (SELECT IsoAlpha3Code
            FROM Application.Countries
          WHERE  IsoAlpha3Code Like 'J%')

It’s going to bring back all the IsoAlpha3Codes that begin with the letter J. My outer query here is going to run.

This column IsoAlpha3Code will then have its value compared using the IN operator to the results of the subquery. So, when I run this you see that it comes back with “JAM”, “JPN”, and “JOR” as the results.

Notice that the subquery is enclosed in parentheses, and that multiple values are returned.

I want to mention that if I was to have put, let’s say CountryName in here in my subquery and run it that it will return an error. Because in this case for the subquery, since it’s part of the IN clause, it’s really expecting one value per row being returned.

Example Query using IN

So, let’s see how this works. This is really similar to, again, the query if I run … sort of this query here where I would run SELECT, where I’m looking for the IsoAlpha3Code IN “JAM”, “JOR”, “JPN”.

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

So, in fact, really, to get these three values, probably could have found those three values just by running a query similar to this, right?

Where I would have said

SELECT IsoAlpha3Code FROM Application.Countries WHERE the IsoAlpha3Code is like "J%." 

And there you see JAM, JPN, and JOR. So, those are my three values.

SELECT IsoAlpha3Code
  FROM Application.Countries
 WHERE IsoAlpha3Code Like 'J%'

If I was to take this query and use this as a subquery it can in effect pump the results, these results here into that IN list and then use as a comparison.

Example Subquery Using IN

Now, if I go back to my original query … let me get another copy of it here … there we go …

I’m now taking the results of that query where I’m getting JAM, JPN, and JOR from here, pumping the results into the IN list and then using that to drive the comparison for my outer query.

SELECT CountryID
      ,CountryName
      ,IsoAlpha3Code
FROM  Application.Countries
WHERE IsoAlpha3Code
      IN (SELECT IsoAlpha3Code
            FROM Application.Countries
          WHERE  IsoAlpha3Code Like 'J%')

The benefit of doing that versus just coming in here straight out typing JAM, JPN and JOR is that if we were to add a fourth country, like JYP, then I no longer have to change my query. This is because this query here is flexible in that it will capture any new countries that begin with the letter J.

Where, if you were to add new countries beginning with the letter J and I was hard coding them in, I would always have to come back to my query and remember to always type them in.

And you would always have to remember to tell me to change my query.

Advantages of Using Subqueries

So, to me, a huge advantage of using subqueries in this case, is that they become more robust because they adapt to the changing data.

This makes the subquery very powerful.

Now, on the flip side, the danger of using the subquery is that you really do have a query inside of another query. Also, you can run into performance issues using subqueries.

I know in this specific case it won’t cause a performance issue, but you do have to be careful when using subqueries in general that you will not cause performance issues.

So, beware in general of not overusing subqueries and know in this specific case you’d be all right, so I feel strongly that using subqueries in the IN clauses can be a good thing and it’s definitely a powerful tool to keep your code robust.