Using UNION, UNION ALL, EXCEPT, and INTERSECT to manage SQL query results.
The function of SQL set operators is pretty simple; they allow us to combine results from different SQL queries into one result set.
The best way to understand how set operators work is to use visual methods like the Venn diagram. For those of you not familiar with Venn diagrams, they are two circles that represent items or collections of items. The area where the circles overlap shows what the two groups have in common.
In this article, we’ll look at the following SQL set operators and how they work:
We’ll use Venn diagrams to demonstrate how each operator functions and what makes it different from other set operators. Ready? Let’s start with a basic one,
— Vertabelo (@Vertabelo) January 2, 2017
The UNION operator takes two sets and returns the union of those sets. So if we have a group of celebrities in a table called “
celebrities” (circle A) and a group of politicians in a table called “
politicians” (circle B), a
UNION of people from both tables would list those people who are famous politicians, famous non-politicians, and non-famous politicians – in other words, anyone who appears in either table.
UNION operator answers OR questions affirmatively for either or both parts of the question. So if the question is “Do you know celebrities or politicians?” the answer would be “Yes” if you knew either celebrities or politicians (or both).
To be more concrete, let’s use an example.
Let’s say the table “
celebrities” consists of:
politicians” consists of:
We would use the following statement to get a list of people in either table:
SELECT PERSON FROM POLITICIANS UNION SELECT PERSON FROM CELEBRITIES
Here are the results of that query:
The important thing here is that if the same person is in both tables the
UNION statement will display only one instance of that person. That’s why there is only one Donald Trump record shown.
UNION ALL is closely related to
UNION. The only difference is that
UNION ALL keeps the number of records as found in all tables. So in the above example,
UNION ALL would return two Donald Trump records, not just one.
Notice the code:
SELECT PERSON FROM POLITICIANS UNION ALL SELECT PERSON FROM CELEBRITIES
The returns would be:
In terms of execution time,
UNION ALL is much faster than
UNION. This is because
UNION sorts the data to eliminate any duplicates and sorting takes time.
UNION ALL does not sort data.
EXCEPT statement takes things away. It reduces the result set from the first table by taking away records found in the second table.
EXCEPT tells us who or what is not in a table. For example, it would answer the question “Who is a celebrity but not a politician?”
It is worth knowing that
EXCEPT is replaced by
MINUS in Oracle and some other RDBMS.
The Venn diagram of the
EXCEPT statement for our
politicians (A) and
celebrities (B) sets would look something like this:
The code to find out who is famous but not a politician is:
SELECT PERSON FROM POLITICIANS EXCEPT SELECT PERSON FROM CELEBRITIES
The return set would be:
So there are no Donalds here. This is because Donald Trump is also a politician and the
EXCEPT took the politicians away from the results set.
This statement answers AND questions. For example “Who are both
The Venn diagram for this kind of question looks like this (the darker part is where the intersection occurs):
To find the answer to our question, we would type the following code:
SELECT PERSON FROM POLITICIANS INTERSECT SELECT PERSON FROM CELEBRITIES
The resulting rows would return results who are both celebrities and politicians. In this case, there is one and only one record:
And there you have it: very simple statements. You can find exercises to practice this and other concepts in Vertabelo Academy’s SQL courses.