SQL Set Operators

Stephanie Warner headshot
Stephanie Warner|Updated May 7, 2020
image (52)
image (52)

Set operators are used to combine or exclude the results of two or more SELECT statement queries into a single result set. They perform by combining rows from two or more tables as opposed to columns in SQL joins. Set operators are similar to a mathematical operation. It is important to understand the difference between the way SQL set operators and joins perform. While SQL JOINs tie tables together horizontally (left to right) using columns, UNIONs effectively combine/exclude rows from tables vertically (top to bottom).

There are four fundamental set operators used in SQL:

  1. UNION – combines two or more result sets without duplicating values.

  2. UNION ALL – combines two or more result sets including duplicate values

  3. INTERSECT – includes ONLY the values present between two or more result sets.

  4. EXCEPT (MINUS on Oracle) – includes ONLY results from first result set that are NOT included in second result set

The same protocols listed in the UNION vs. UNION ALL blog must also be followed using SQL set operators:

  1. Each SELECT statement must have the same number of columns.

  2. The fields used in the SELECT statements must be in the same order as one another.

  3. The fields used in each SELECT statement must have corresponding data types, meaning they must be alike.

Visual Representation of SQL Set Operators

UNION

Screen Shot 2020-02-24 at 3.43.12 PM

These separate queries show results from the ‘Founders’ table (Query 1) and ‘Employees’ table (Query 2) in each of their own result sets before the operator is applied:

image13

These SELECT statements use the UNION operator to combine the names of ‘Founders’ and ‘Employees’ into a single result set:

image6

The Result Set:

unnamed (10)

Notice how ‘Shawn Johnson’ and ‘Shane Johnson’ are present in each table before the UNION operator is applied but are only listed ONCE in the result set.

UNION ALL

Screen Shot 2020-02-24 at 3.43.27 PM
image13

These SELECT statements use the UNION ALL operator to combine the names of ‘Founders’ and ‘Employees’ into a single result set:

image8

The Result Set:

image7

Notice how ‘Shawn Johnson’ and ‘Shane Johnson’ are present in each table before the UNION ALL operator is applied and are duplicated in the result set.

INTERSECT

Screen Shot 2020-02-24 at 3.43.42 PM
image13 (1)

These SELECT statements use the INTERSECT operator to include values from the ‘Founders’ and ‘Employees’ tables that are listed in BOTH tables:

image11

The Result Set:

image7 (1)

EXCEPT

Screen Shot 2020-02-24 at 3.43.53 PM
image13 (2)

These SELECT statements use the EXCEPT operator to exclude values from the ‘Founders’ table (Query 1) if they are also included in the ‘Employees’ table (Query 2):

image12

The Result Set:

image10


This blog post is part of our SQL series:

Stephanie Warner headshot
Stephanie Warner

Stephanie is a big time math nerd, avid snowboarder, and overall winter lover. She enjoys the torture of running and traveling to work Ragnar Relay races. From shredding the gnar to getting far too invested in murder/crime documentaries, you'll also find her petting all the doggos. She was an employee of PDQ.

Related Articles