SQL TUTORIALS

SQL Tutorials: Unions

%UPDATE% Picture of the word python written on a stick note.

DISCLAIMER: ANSI SQL (The SQL that's used universally) includes everything taught in this section. However, some of the syntax shown will vary between your Relational Database Management Service (RDBMS).

Querying multiple tables is a powerful tool. However, what if you want to combine multiple queries to get a larger result set? That's where Unions come into play. In this tutorial, we will cover the following learning objectives:

  • UNION vs. UNION ALL
  • Set Operations
  • INTERSECT vs. EXCEPT

What are Unions?




Summary

  • A Union is used to combine the result sets of two or more SELECT statements. By default, this clause eleminates any duplicate rows that occur in both result sets (e.g., if you have "John Smith" in both result sets, only one with show up). This can be used with the following syntax:
    SELECT *
    FROM [tableA]
    UNION
    SELECT *
    FROM [tableB];
  • The UNION ALL clause is used to keep all the results from all the result sets, regardless of duplicates entries. This can be used with the following syntax:
    SELECT *
    FROM [tableA]
    UNION ALL
    SELECT *
    FROM [tableB];
  • NOTE: When combining the result sets of multiple queries, the result sets must have the same number of columns.

What are Set Operations in SQL?




Summary:

  • The INTERSECT clause is used to get a list of values that are in ALL result sets. Similar to the UNION clause, this also requires all results sets to have the same number of columns and eliminates duplicate entries. This is used with the following syntax:
    SELECT *
    FROM [tableA]
    INTERSECT
    SELECT *
    FROM [tableB];
  • The INTERSECT ALL clause is the same as the INTERSECT clause, except, similar to the UNION ALL clause, it includes duplicate entries. This is used with the following syntax:
    SELECT *
    FROM [tableA]
    INTERSECT ALL
    SELECT *
    FROM [tableB];
  • The EXCEPT clause is used to get a list of value that are NOT in all result sets. Just like the UNION and INTERSECT clauses, this requires all result sets to have the same number of columns and eliminates duplicate entries. This is used with the following syntax:
    SELECT * FROM [tableA]
    EXCEPT
    SELECT *
    FROM [tableB];
  • The EXCEPT ALL clause is the same as the EXCEPT clause, except, similar to the UNION ALL clause, it includes duplicate entries. This is used with the following syntax:
    SELECT *
    FROM [tableA]
    EXCEPT ALL
    SELECT *
    FROM [tableB];

Exercise

Congratulations! You just completed the Joins and Unions Tutorial! To help test your knowledge, let's use the newly created "lego" database to combine multiple result sets.
**It's highly recommended that you complete the exercise outlined in the previous tutorial before beginning this exercise.**

Instructions:


  1. Open your RDBMS Client (e.g., MySQL, PostgreSQL, MS SQL Server).
  2. If you haven't created the "lego" database yet, please view the instructions in the previous tutorial.
  3. "lego" database ERD:

    4. The Database used in this exercise includes data on hundreds of Lego sets and their associated parts, colors, and themes.

  4. Using the "lego" database, Write the Following Queries:
    1. Write a query that shows all the unique values in the following table: sets and parts. Rename the part_num/set_num columns to "id" and include only the part_num/set_num and name columns.
    2. Write a query that uses a set operator (INTERSECT or EXCEPT) to show all the parts that are in inventory.
    3. Write a query that uses a set operator (INTERSECT or EXCEPT) to show all the sets that are NOT in inventory. Include duplicate values.
    4. Write a query that shows all values in the following tables: themes, sets, parts, part_categories. Include only the "name" columns from each table.
    5. Write a query that shows all sets in inventory that have a theme of "Fire". Exclude duplicate values.
  5. Exercise Completed! Click here to view the answers.

    6. Have any issues with the above exercise? Post your question on Discord!

Previous Topic
Next Topic