1 00:00:00,470 --> 00:00:05,760 In this video, we're going to see how to use the IN keyword with a subquery. 2 00:00:05,760 --> 00:00:09,990 Let's have a quick reminder of what the IN clause is. 3 00:00:09,990 --> 00:00:14,891 You can use the IN keyword in your WHERE statement to enumerate of a list of things 4 00:00:14,891 --> 00:00:16,150 to filter by. 5 00:00:16,150 --> 00:00:22,100 Lets say we wanted to see specific car sales for ID's 1, 3, and 5. 6 00:00:23,640 --> 00:00:27,850 Now, let's say we don't know exactly which car IDs we need. 7 00:00:27,850 --> 00:00:30,560 This is where a subquery can help us out. 8 00:00:30,560 --> 00:00:36,170 If we wanted to see all sales of cars with the model year of 2015, 9 00:00:36,170 --> 00:00:40,060 we can replace this enumerated list of IDs with a query 10 00:00:40,060 --> 00:00:43,550 that gets us the car IDs that meets our criteria. 11 00:00:43,550 --> 00:00:46,725 First, let's write the query that gets the IDs we want. 12 00:00:46,725 --> 00:00:54,959 So SELECT FROM 13 00:00:54,959 --> 00:01:02,936 Car WHERE ModelYear = 2015. 14 00:01:02,936 --> 00:01:07,456 This becomes our subquery that we can put inside 15 00:01:07,456 --> 00:01:11,751 the parentheses replacing 1, 3, and 5. 16 00:01:16,424 --> 00:01:20,545 You can also use a subquery in a NOT IN, as well. 17 00:01:20,545 --> 00:01:25,588 Let's say, we really wanted to see all car sales except for the model year 2015. 18 00:01:25,588 --> 00:01:34,201 We just have to add NOT In front of IN to get a different result. 19 00:01:34,201 --> 00:01:38,944 The one trick to remember here is that you can only 20 00:01:38,944 --> 00:01:42,560 select one column in your subquery. 21 00:01:42,560 --> 00:01:45,140 if you have more than one you'll get an error. 22 00:01:46,200 --> 00:01:47,550 So to review. 23 00:01:47,550 --> 00:01:50,840 You can use a subquery in an IN clause. 24 00:01:50,840 --> 00:01:55,710 When writing a subquery in this way you should only return one column of data. 25 00:01:55,710 --> 00:01:59,480 This creates a set of values in the IN clause to search for. 26 00:01:59,480 --> 00:02:02,770 Instead of writing the set manually ourselves, 27 00:02:02,770 --> 00:02:05,675 we can get the database to do the heavy lifting for us. 28 00:02:05,675 --> 00:02:10,620 [SOUND] You can also use a subquery in a NOT IN clause.