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.