Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Start a free Courses trial
to watch this video
INTERSECT is similar to an Inner Join. As with a UNION, they must have the same columns in both the left and right side of the SQL operation.
As with the other topics we’ve covered in this series, there is a wealth of information available on the topic of sets, databases, and SQL.
Check out this great read on Set Theory and SQL: “SQL-99 Complete, Really”, by Peter Guzman & Trudy Pelzer hosted on MariaDB
The link above contains more than just UNION, INTERSECT and EXCEPT, but there is a section towards the end that applies those operations to the larger discussion. This is a wonderful article!
Other good reads about Set Operations include:
Union and Union All:
SQL UNION Operator
SQL - UNIONS Clause
Intersect:
SQL - INTERSECT Clause
SQL Server: INTERSECT Operator
Except:
SQL - EXCEPT Clause
SQL Server: EXCEPT
Cheat Sheet
See all of the SQL used in Querying Relational Databases in the Querying Relational Databases Cheat Sheet.
So far, we've seen the union and union all operations. 0:00 In this video, we're going to learn about the intersect operator. 0:04 Instead of bringing back all rows from both subsets like the union. 0:08 The intersect is only going to return the rows that exist in both. 0:12 Let's see our sample union again. 0:17 Here, it takes all products from the electronics and furnished tables and 0:21 creates one result cell. 0:25 If we perform an intersect on the same two tables, we get no results back. 0:26 [SOUND] This is because that is no overlap. 0:31 There's other piece of furniture that we might want to market 0:35 along with electronics. 0:38 The TV stand now if we perform an inspection operation. 0:39 We'll get the TV stand as the only row in our results set. 0:43 It's the only record that exists in both tables. 0:47 The Venn diagram of an intersection looks like this. 0:50 Now, let's perform an intersect operation in our car DB. 0:54 Remember, we have car manufacturers that live in two different tables. 0:58 Make and ForeignMake. 1:03 Let's see, if we can find the makes that exist in both tables. 1:05 We can do that by using the INTERSECT keyword. 1:08 Let's order them by name. 1:19 And for fun, let's see if we can order that in descending order. 1:29 What if we wanted to see the ID's along with the MakeNames 1:47 We get no records back, because with the ID's included, the rows no longer match. 2:04 Remember, that set operations use all the columns supplied within the query. 2:10 Since the ID values are different for each make from one to table to another. 2:15 The database treats the rows as different. 2:21 Like with unions, intersect statements include 2 sequel queries 2:24 with the keyword INTERSECT in between. 2:29
You need to sign up for Treehouse in order to download course files.
Sign up