Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Intersect Operations

2:31 with Andrew Chalkley and Kevin Nahm

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.

Teacher's Notes
Video Transcript
Downloads

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