How to use the IN Operator with a Subquery | Essential SQL

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.

Hi, and welcome to another episode of “Essential SQL Minute.” On today’s episode, we’re going to learn how to use the IN operator with a subquery. So, as you may recall, the IN operator is used to compare a column to a list of values. 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, and 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.

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.

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 gonna 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.

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%'
So, 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.

So, 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.

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 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.

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 and people sometimes get a little nervous with subqueries because they can get overused as sometimes subqueries can 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.

[codingwithtoaster]

This is my query, by it throws an error at line 7. where subquery begins. It says missing expression.

select * from ( select rownum, emplid, amount, descr3 from PS_SPJ_STDNT_REG_T)
pivot
(
sum(amount) for descr3
in
(
select distinct descr3 from PS_SPJ_STDNT_REG_T
)
);