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

Union Operations

4:40 with Andrew Chalkley and Kevin Nahm

One of the more common set operations in SQL is the UNION statement. Use to combine two data sets into one, stacked one on top of the other, unlike an inner join which puts data together side by side.

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.

Good reads about Set Operations include:

Union and Union All:

SQL UNION Operator
SQL - UNIONS Clause

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

    We're about to dive into our set operation, the UNION operator. 0:00
    Unions combine data from two tables, or sets, into one result set. 0:05
    This may sound a lot like the inner join that we learned about recently, 0:10
    but there's a key difference to point out. 0:13
    If you recall, an inner join uses the foreign key relationship to merge data 0:16
    into rows side by side where they match on common key values. 0:21
    In this case the coming key values are the ID's, 0:27
    a union also brings two data sets together into one result set. 0:30
    But it doesn't use a foreign key relationship at all and 0:35
    it doesn't place data side by side. 0:38
    The union operations stacks data vertically. 0:41
    Because the data is being stacked in this manner, 0:44
    the column definitions of both queries have to match. 0:47
    If they don't have the same number of columns, you'll get an error 0:51
    If we picture the union with a Venn diagram, It looks like this. 0:54
    Now, let's see the union in action, shall we? 0:59
    In our car sales database we have a make table and 1:02
    a table containing only foreign makes. 1:06
    Here they are in two result sets. 1:09
    There are some makes in the main Make table 1:17
    that aren't in the ForeignMake table and there are some in the ForeignMake table 1:21
    that aren't in the main Make table. 1:25
    Let's say we want to see all makes in our database together in one list. 1:28
    We can do that by adding the union keyword between two statements. 1:33
    Let's also select the make name. 1:42
    When we hit run we get a result set with one column 1:54
    containing a distinct list of all car makes, perfect. 1:58
    What if we wanted to see the ID columns along with the names? 2:03
    Let's add make ID and hit run. 2:11
    Notice we got an error, 2:16
    that's because we specified to two columns in our first select statement. 2:19
    And only one in our second. 2:24
    Let's fix that, let's add foreign make ID, 2:28
    That's better, notice now that we include the ID fields. 2:41
    We see a few models more than once. 2:45
    Why is that? 2:48
    That's because the union statement returns a distinct set based on the column 2:53
    specified. 2:58
    In the case for the ID's for BMW and Honda 2:59
    the IDs are different in the Make and ForeignMake table 3:06
    so it displays both instances. When we exclude the ID 3:11
    field, the database only returns the one row for each make. 3:15
    Let's say we only care about car manufacturers 3:29
    that start with letters A, B, or C. 3:32
    We can filter down each portion of the union statement with a WHERE clause. 3:35
    In the WHERE section, we can use the less than operator with the text to get 3:43
    anything that's alphabetically less than D. 3:47
    And finally let's order that alphabetically. 3:58
    When ordering a union statement, you only need to supply it once in the last query. 4:08
    To recap you write a union statement first by writing a SQL query, 4:24
    then the keyword union, and then your second query. 4:29
    Unions require the number of columns in your data set to be the same in your first 4:35
    and second queries. 4:39

    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