1
00:00:00,340 --> 00:00:04,480
So far, we've seen the UNION and
UNION ALL operations.

2
00:00:04,480 --> 00:00:08,780
In this video, we're going to learn
about the INTERSECT operator.

3
00:00:08,780 --> 00:00:12,840
Instead of bringing back all rows
from both subsets like the union,

4
00:00:12,840 --> 00:00:16,670
the intersect is only going to
return the rows that exist in both.

5
00:00:17,790 --> 00:00:21,090
Let's see our sample union again.

6
00:00:21,090 --> 00:00:25,110
Here, it takes all products from
the electronics and furnished tables and

7
00:00:25,110 --> 00:00:26,970
creates one result set.

8
00:00:26,970 --> 00:00:31,795
If we perform an intersect on the same
two tables, we get no results back.

9
00:00:31,795 --> 00:00:35,014
[SOUND] This is because
that is no overlap.

10
00:00:35,014 --> 00:00:38,186
Let's add a piece of furniture
that we might want to market

11
00:00:38,186 --> 00:00:40,535
along with electronics, a TV stand.

12
00:00:40,535 --> 00:00:43,420
Now, if we perform
an INTERSECT operation

13
00:00:43,420 --> 00:00:47,600
we'll get the TV stand as
the only row in our results set.

14
00:00:47,600 --> 00:00:50,870
It's the only record that
exists in both tables.

15
00:00:50,870 --> 00:00:54,050
The Venn diagram of
an intersection looks like this.

16
00:00:54,050 --> 00:00:58,250
Now, let's perform an intersect
operation in our car DB.

17
00:00:58,250 --> 00:01:03,140
Remember, we have car manufacturers
that live in two different tables.

18
00:01:03,140 --> 00:01:05,490
Make and ForeignMake.

19
00:01:05,490 --> 00:01:08,980
Let's see, if we can find the makes
that exist in both tables.

20
00:01:08,980 --> 00:01:12,266
We can do that by using
the INTERSECT keyword.

21
00:01:19,830 --> 00:01:21,210
Let's order them by name.

22
00:01:29,016 --> 00:01:33,640
And for fun, let's see if we can
order that in descending order.

23
00:01:47,093 --> 00:01:51,729
What if we wanted to see the ID's
along with the MakeNames?

24
00:02:04,264 --> 00:02:10,610
We get no records back, because with the
ID's included, the rows no longer match.

25
00:02:10,610 --> 00:02:15,590
Remember, that set operations use all
the columns supplied within the query.

26
00:02:15,590 --> 00:02:21,230
Since the ID values are different for
each make from one to table to another,

27
00:02:21,230 --> 00:02:24,460
the database treats the rows as different.

28
00:02:24,460 --> 00:02:29,060
Like with unions, intersect
statements include two SQL queries

29
00:02:29,060 --> 00:02:31,550
with the keyword INTERSECT in between.