1
00:00:00,820 --> 00:00:04,890
In this video, we will learn
about another type of subquery,

2
00:00:04,890 --> 00:00:07,250
the temporary table or the derived table.

3
00:00:08,260 --> 00:00:12,512
Most of the time,
when we create derived tables in a query

4
00:00:12,512 --> 00:00:15,907
it's because the data we need isn't
stored in the exact format or

5
00:00:15,907 --> 00:00:17,970
structure that we want.

6
00:00:17,970 --> 00:00:22,890
This happens a lot when doing a lot
of aggregation or reporting queries.

7
00:00:22,890 --> 00:00:26,150
Let's start with a simple
example of a derived table.

8
00:00:27,620 --> 00:00:31,440
In our last video, we used
a subquery in the IN clause.

9
00:00:31,440 --> 00:00:36,420
Let's take that query and make it
a derived table just to see how it works.

10
00:00:36,420 --> 00:00:41,148
This is our query where we used a subquery
to get all cars that are model year

11
00:00:41,148 --> 00:00:41,894
2015.

12
00:00:46,776 --> 00:00:50,650
Now, we can move the subquery
to the FROM clause,

13
00:00:51,860 --> 00:00:54,720
and make it behave just
like another table.

14
00:00:54,720 --> 00:00:57,050
Let's delete the WHERE clause

15
00:00:57,050 --> 00:00:59,231
and Alias the sale table as S.

16
00:01:02,704 --> 00:01:04,500
Let's add an INNER JOIN.

17
00:01:06,330 --> 00:01:12,000
This is to join the Sales table with
the resulting dataset from the subquery.

18
00:01:12,000 --> 00:01:16,750
In order to have join criteria,
we have to give the dataset a name.

19
00:01:16,750 --> 00:01:22,950
So we have to create an alias t for

20
00:01:22,950 --> 00:01:26,210
temp, we could pick anything here.

21
00:01:27,350 --> 00:01:33,290
This alias here has created
a temporary table or a derived table.

22
00:01:41,610 --> 00:01:45,240
You have to alias derived tables so
you can refer to it in the join criteria.

23
00:01:54,486 --> 00:01:58,353
Otherwise, the database would
not be able to parse the query.

24
00:02:03,248 --> 00:02:07,630
Notice, that we can now see
the car ID column twice.

25
00:02:09,020 --> 00:02:13,610
The second instance of the CarID
column is from the derived table.

26
00:02:13,610 --> 00:02:18,300
Using a subquery in the IN clause,
didn't give you this ability.

27
00:02:18,300 --> 00:02:22,480
Since derived tables are, basically,
temporary in-memory tables,

28
00:02:22,480 --> 00:02:25,300
we can add any number of columns we want,

29
00:02:25,300 --> 00:02:27,890
and display them in our final results.

30
00:02:27,890 --> 00:02:31,569
Let's add model year to the temporary
table to say how to do this.

31
00:02:38,149 --> 00:02:42,766
Using temporary tables in this manner
are a little more complex than

32
00:02:42,766 --> 00:02:44,740
the simple IN statement,

33
00:02:44,740 --> 00:02:47,100
but they give you much more flexibility.

34
00:02:47,100 --> 00:02:49,937
To review, to create a temporary
or derived table,

35
00:02:49,937 --> 00:02:54,743
[SOUND] where you'd,

36
00:02:54,743 --> 00:02:58,710
normally, put the second table's
name, you write a subquery.

37
00:02:58,710 --> 00:03:03,720
Unlike writing subqueries in an IN clause,
you can add as many columns as you'd like.

38
00:03:03,720 --> 00:03:10,000
Then to use it as a table in your join
criteria, you must alias the table.

39
00:03:10,000 --> 00:03:13,350
That alias can be used
in the join criteria.

40
00:03:13,350 --> 00:03:14,210
In our next video,

41
00:03:14,210 --> 00:03:18,480
you will see a much more complex
example of using derived tables.