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:
UNION – combines two or more result sets without duplicating values.
UNION ALL – combines two or more result sets including duplicate values
INTERSECT – includes ONLY the values present between two or more result sets.
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:
Each SELECT statement must have the same number of columns.
The fields used in the SELECT statements must be in the same order as one another.
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:
