PDQ.com mobilePDQ.com desktop
Support

SQL Set Operators

Stephanie WarnerStephanie Warner
·

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

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:

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

The Result Set:

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

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

The Result Set:

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

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

The Result Set:

EXCEPT

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

The Result Set:


This blog post is part of our SQL series:

SQL UNION vs UNION ALL

SQL JOINS

SQL INNER JOIN

SQL LEFT JOIN

SQL Right Join

SQL Full Outer Join

Ready to get started?

Take our 14-day Free Trial.
This round is on us!

Don't miss the next post!

Using PowerShell to Install Printers

When installing printers, we will need to do the four things; Add Driver to the Store, Install the Driver, Create Printer Port, and Install the Printer