How to use the SQL In Statement with Subquery

How do you use the SQL IN Statement with a Subquery?

I will show you in 3 steps that build on each other:

1. Use the SQL OR operator.
2. Use the SQL IN operator.
3. Substitute a SQL Subquery into the IN Clause.

I will start with a simple query of the SQL Training Online Simple DB employee table where I want to retrieve 3 employee numbers: 7369,7499,7521

select * from employee
where employee_number=7369
or employee_number=7499
or employee_number=7521

Here, you can see that I used the SQL OR statement to get the 3 employees.

But, I can make this a little cleaner by using the SQL IN statement:

select * from employee
where employee_number in (7369,7499,7521)

This query returns the same data as before, but it has less typing.

So, now I want to define a subquery. It is simply:

“A subquery is a query inside of another query.”

Let’s start the example. First, I will look at the customer table and specifically, the salesman employee number:

select saleman_employee_number from customer

This query returns a list of employee numbers attached to a customer. But, I want to filter out the nulls and get rid of the repeated employee numbers. So, here is the query for that.

select distinct saleman_employee_number from customer
where saleman_employee_number is not null

Now, this is where the magic of a subquery comes in. Since this query returns a list of values, you can simply use it in the original query as the SQL IN statement. It looks like this:

select * from employee
where employee_number in (select distinct saleman_employee_number from customer where saleman_employee_number is not null)

And, that’s it. You have now used the SQL IN statement with Subquery.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline