In this video, I show you how to use the SQL IN Statement with Subquery in SQL Server 2012 using SQL Server Management Studio.
How do you use the SQL IN Statement with a Subquery?
I will show you in 3 steps that build on each other:
- Use the SQL OR operator.
- Use the SQL IN operator.
- 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: http://www.sqltrainingonline.com