How to use the SQL In Statement with Subquery

Video: How to use the SQL In Statement with Subquery - SQL Training Online

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:

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.

![SQL In Statement With Subquery](../../assets/blog/2012/10/SQLInStatementWithSubquery_thumb.png)

So, now I want to define a [subquery](http://blog.sqlauthority.com/2010/06/06/sql-server-subquery-or-join-various-options-sql-server-engine-knows-the-best/). 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.aspx) 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: /

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

Google+:

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

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