SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

October 29, 2012 by Joey Blue Leave a Comment

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.
[sharebox4 sharetext=”Share This Page”] [/sharebox4]

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

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

Related

Filed Under: SQL Training Tagged With: SQL IN, SQL Subquery

Leave a Question, Comment, or Reply. All are welcome!Cancel reply

Recent Posts

  • SQL Database Normalization – 1NF, 2NF, 3NF, and 4NF
  • SQL Joins Tutorial for Beginners – Inner Join, Left Join, Right Join, Full Outer Join – SQL Training Online
  • Zillow House Price Analysis from CSV Archive – #PowerBI 002
  • Learn Basic SQL – 1 Hour Training Course – SQL Training Online
  • Create Table Statement in SQL Server and Inserting Baseball Homerun Leader Dataset – SQL Training Online

Popular Posts

  • SQL Functions
  • SQL Jobs in Oracle and Microsoft SQL Server
  • Troubleshooting a SQL Join
Copyright © 2025 · SQLTrainingOnline.com · Consulting at EmbarkBlue.com