How to use the SQL In Statement with Subquery - SQL Training Online - Quick Tips Ep40

hey joy blue here and today I want to talk about the sequel end statement with a sub-query so in order to do that we'll first start with the or statement then we're going to move into the in statement and then we will use sub query substitution so let's get started here and the first thing I want to do is inside the sequel training online simple database I want to do a select star from the employee table and so when I run that you can see we have 14 rows and what I want to do is I want to go in head and get three different employees here I'm gonna get these first three employees just by selecting out their employee number and so in order to do that I need to put a where clause on here so I put a where clause where employee underscore number equals the 73-69 for the first one or I want the employee number to equal the $74.99 or I want the employee number to equal day 75 21 and so this is how you could write this query and let's go ahead and run it and see if we get back what we think we should so we got the three rows back that we want to get back and you can see there's a lot of type in there and I had to type employee number three times well that is where the in statement comes in so let's substitute that first and so I type in and then I simply put these three values separated by commas and let's get the last one here and I get rid of this and so this is the same query that I just ran using the in statement though so let's go ahead and run it and so now we got our three records and so we've talked about the or statement now we talked about the end statement filter the last thing I want to do is I want to substitute a sub query so let me tell you what the sub-query is real quick if I went and do a select star from like the customer table you can see in the customer table I have a Salesman employee number and so if I want to get the employee numbers out of this table I would just simply go type in select Selman sorry there's no s in that column for some reason employee number from customer and so I got just the salesman and I don't want Knowles so I'll say where Selman employee number is not null and so that'll take me down to just the numbers I want and then we have a repeat and we're just going to go ahead and put a distinct on here and let's get our list of numbers so now we have a list of salesman numbers right and just like we have here we have a list of salesman numbers well instead of listing all the salesmen numbers I want to take the results of this query right here and I want to put them in this list right here and what you can do is you can take this and you can become what's called a sub query so I'll copy that and just paste it right in here and so now what they'll do is they'll run this query first that's why it's called a sub query inside of another query and it will run this get this list of employee numbers and insert it into this in clause so let's go ahead and run that and there you have it so that is the sub query substitution for the in clause as always if you find this helpful please leave a comment below click the like button or subscribe to my channel you can also visit me a sequel training online.com and I hope you enjoyed this free video

Channel: Joey Blue

Views: 22,583

Rating: 4.9643493 out of 5

Keywords: SQL In Statement with Subquery, How-to (Conference Subject), Data, Subject (programming), Business, Technology, SQL, SQL Server 2012, SQL Server, subquery, SQL Subquery, SQL IN Statement, sql, in, statement, with, microsoft, server, 2012, where, clause

Id: ZmEpW-RVWxY

Length: 4min 25sec (265 seconds)

Published: Mon Oct 29 2012