Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Querying Relational Databases

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 on 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:

Intersect:

SQL - INTERSECT Clause
SQL Server: INTERSECT Operator

Cheat Sheet

See all of the SQL used in Querying Relational Databases in the Querying Relational Databases Cheat Sheet.

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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 set. 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
    Let's add a piece of furniture that we might want to market 0:35
    along with electronics, a TV stand. 0:38
    Now, if we perform an INTERSECT operation 0:40
    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 two SQL 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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up