Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic 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.
-
0:00
So far, we've seen the union and union all operations.
-
0:04
In this video, we're going to learn about the intersect operator.
-
0:08
Instead of bringing back all rows from both subsets like the union.
-
0:12
The intersect is only going to return the rows that exist in both.
-
0:17
Let's see our sample union again.
-
0:21
Here, it takes all products from the electronics and furnished tables and
-
0:25
creates one result cell.
-
0:26
If we perform an intersect on the same two tables, we get no results back.
-
0:31
[SOUND] This is because that is no overlap.
-
0:35
There's other piece of furniture that we might want to market
-
0:38
along with electronics.
-
0:39
The TV stand now if we perform an inspection operation.
-
0:43
We'll get the TV stand as the only row in our results set.
-
0:47
It's the only record that exists in both tables.
-
0:50
The Venn diagram of an intersection looks like this.
-
0:54
Now, let's perform an intersect operation in our car DB.
-
0:58
Remember, we have car manufacturers that live in two different tables.
-
1:03
Make and ForeignMake.
-
1:05
Let's see, if we can find the makes that exist in both tables.
-
1:08
We can do that by using the INTERSECT keyword.
-
1:19
Let's order them by name.
-
1:29
And for fun, let's see if we can order that in descending order.
-
1:47
What if we wanted to see the ID's along with the MakeNames
-
2:04
We get no records back, because with the ID's included, the rows no longer match.
-
2:10
Remember, that set operations use all the columns supplied within the query.
-
2:15
Since the ID values are different for each make from one to table to another.
-
2:21
The database treats the rows as different.
-
2:24
Like with unions, intersect statements include 2 sequel queries
-
2:29
with the keyword INTERSECT in between.
You need to sign up for Treehouse in order to download course files.
Sign up