Skip to content Skip to main navigation Skip to footer

Using Set Operators in SQL

Objectives

This lesson covers the following objectives:

  • Define and explain the purpose of Set Operators
  • Use a set operator to combine multiple queries into a single query
  • Control the order of rows returned using set operators

Purpose

  • Set operators are used to combine the results from different SELECT statements into one single result output.
  • Sometimes you want a single output from more than one table.
  • If you join the tables, the rows that meet the join criteria are returned, but what if a join will return a result set that doesn’t meet your needs?
  • This is where SET operators come in.
  • They can return the rows found in multiple SELECT statements, the rows that are in one table and not the other, or the rows common to both statements.

Setting the Stage

  • In order to explain the SET operators, the following two lists will be referred to throughout this lesson:

A = {1, 2, 3, 4, 5}
B = {4, 5, 6, 7, 8}

  • Or in reality: two tables, one called A and one called B.
Using Set Operators

Rules to Remember

  • There are a few rules to remember when using SET operators:
    • The number of columns and the data types of the columns must be identical in all of the SELECT statements used in the query.
    • The names of the columns need not be identical.
    • Column names in the output are taken from the column names in the first SELECT statement.
  • So any column aliases should be entered in the first statement as you would want to see them in the finished report.

UNION

  • The UNION operator returns all rows from both tables, after eliminating duplicates.
The UNION operator
  • The result of listing all elements in A and B eliminating duplicates is {1, 2, 3, 4, 5, 6, 7, 8}.
  • If you joined A and B you would get only {4, 5}. You would have to perform a full outer join to get the same list as above.

UNION ALL

  • The UNION ALL operator returns all rows from both tables, without eliminating duplicates.
UNION ALL
  • The result of listing all elements in A and B without eliminating duplicates is {1, 2, 3, 4, 5, 4, 5, 6, 7, 8}.

INTERSECT

  • The INTERSECT operator returns all rows common to both tables.
INTERSECT
  • The result of listing all elements found in both A and B is {4, 5}.

MINUS

  • The MINUS operator returns all rows found in one table but not the other.
MINUS
  • The result of listing all elements found in A but not B is {1, 2, 3}.
  • The result of B MINUS A would give {6, 7, 8}.

Set Operator Examples

  • Sometimes if you are selecting rows from tables that do not have columns in common, you may have to create your own columns in order to match the number of columns in the queries.
  • The easiest way to do this is to include one or more NULL values in the select list.
  • Remember to give each one a suitable alias and matching data type.
  • For example:
    • The employees table contains a hire date, employee id and a job id.
    • The job history table contains employee id and job id, but does not have a hire date column.
    • The two tables have the employee id and job id in common, but job history does not have a start date .
  • You can use the TO_CHAR(NULL) function to create matching columns as in the next slide.
SELECT hire_date, employee_id, job_id
FROM employees
UNION
SELECT TO_DATE(NULL),employee_id, job_id
FROM job_history;
Set Operator Examples

The job_history table contains 10 rows, so when added to the 20 rows from the employees table, 30 rows are returned. The rows with NULL values for hire_date are from the job_history table.

  • The keyword NULL can be used to match columns in a SELECT list.
  • One NULL is included for each missing column.
  • Furthermore, NULL is formatted to match the data type of the column it is standing in for, so TO_CHAR, TO_DATE, or TO_NUMBER functions are used to achieve identical SELECT lists.

SET Operations ORDER BY

  • If you want to control the order of the returned rows when using SET operators in your query, the ORDER BY statement must only be used once, in the last SELECT statement in the query.
  • Using the previous query example, we could ORDER BY employee_id to see the jobs each employee has held.
SELECT hire_date, employee_id, job_id
FROM employees
UNION
SELECT TO_DATE(NULL),employee_id, job_id
FROM job_history
ORDER BY employee_id;
SET Operations ORDER BY

Note: for ease of reading, only a partial query result is shown.

  • We could improve the readability of the output, by including the start date and end date columns from the job history table, to do this, we would need to match the columns in both queries by adding two more TO_DATE(NULL) columns to the first query.
SELECT hire_date, employee_id, TO_DATE(null) start_date,
TO_DATE(null) end_date, job_id, department_id
FROM employees
UNION
SELECT TO_DATE(null), employee_id, start_date, end_date, job_id,
department_id
FROM job_history
ORDER BY employee_id;

As the column headings for the query output are taken from the first query, they have been given aliases of the same name as the matching columns in the second query.

SET Operations ORDER BY

Note: for ease of reading, only a partial query result is shown.

Terminology

Key terms used in this lesson included:

  • INTERSECT
  • MINUS
  • SET operators
  • TO_CHAR(null) – matching the select list
  • UNION
  • UNION ALL

Summary

In this lesson, you should have learned how to:

  • Define and explain the purpose of Set Operators
  • Use a set operator to combine multiple queries into a single query
  • Control the order of rows returned using set operators

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *