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.