There are some set operators that are offered by PostgreSQL. The most common set operators offered by PostgreSQL are UNION, INTERSECT, and EXCEPT. These operators are widely used when we want to combine the resultant output of two or more tables. In this post, we will be understanding these operators and discussing the difference between UNION, INTERSECT, and the EXCEPT operator. So let’s get started.

UNION vs. INTERSECT vs. EXCEPT: What's the Difference?

Before moving on to the functioning and the differences between these operators, let’s have a quick glimpse at the rules for these set operations:

● The number of columns for the result set must be equal for all the queries.

● The data types must be the same or at least they should be compatible with each other.

Another point to note is that we can always sort the result according to our wish by using the ORDER BY clause.

Now let’s see how these set operators work.

UNION Set Operator

The UNION operator combines the resulting output set of two or multiple SELECT queries. The resultant/output set of two SELECT queries is merged and returned. Note that in UNION, there is no duplication of records. Let's see the workings of the UNION set operator.

Consider the two tables. The first table is named “test_scores” and contains the record of candidate and their scores who appeared in an entrance test. The table is given as:

Another table contains the list of candidates who passed the entrance test and were successful in getting admission. The table is named “passed_candidates”. The table is as follows:

Now if we apply the UNION operator on both tables the query will be:

SELECT candidate_name
 FROM test_scores
 UNION
 SELECT candidate_name
 FROM passed_candidates;

The query will give the table containing the combined result sets from both tables. In easy words, the query will give a table containing all the entries from both tables. Since in my case, all the entries of the second table are present in the first table it will return no extra entry than the first table. The output is:

We can see that all the entries of the second table are present in the first table all the union operator results in all the entries but no entry is duplicated. The duplication of entries happens in UNION ALL. It works the same as the UNION operator the only difference is it returns all the merged entries that may be duplicated.

So this is how the UNION operator works; we will now see how INTERSECT is different.

INTERSECT Set Operator

The INTERSECT set operator gives a combined table containing the common entries from at least two specified tables. In our considered case, if we apply the INTERSECT operator between the two tables, it will give the entries that are common in both tables i.e. the candidates who have passed the entrance test. The query for the INTERSECT operator looks like this:

SELECT candidate_name
 FROM test_scores
 INTERSECT
 SELECT candidate_name
 FROM passed_candidates;

In the above query, the INTERSECT operator will return the table having the entries common in both the specified tables like this:

We can see that the table only contains those entries that were common in both tables.

Let’s see how the EXCEPT operator is different from both these set operators.

EXCEPT Set Operator

The EXCEPT set operator compares the resultant set of both the SELECT statements and returns all those data entries that are present in the resultant set of the first SELECT query but are absent in the second SELECT statement. Let’s query the EXCEPT operator:

SELECT candidate_name
 FROM test_scores
 EXCEPT
 SELECT candidate_name
 FROM passed_candidates;

The above query will return all those entries that are present in the first table i.e.”test_scores” but are absent in the second table i.e. ”passed_candidates”. The query returns the following output:

The above output advocated the working of EXCEPT operator. The query has returned all those values that were present in the “test_scores” but not in “passed_candidates” i.e. the query has returned all those names of candidates who did not clear the entrance test.

Conclusion

The UNION, INTERSECT, and EXCEPT are the set operators that are applied on at least two tables to get a specific combined result. The UNION operator combines the result of two or multiple SELECT statements. The INTERSECT set operator gives a combined table containing the common entries from at least two specified tables and the EXCEPT set operator compares the resultant sets of both the SELECT statements and returns the entries that are present in the resultant set of the first SELECT query but absent in the second SELECT query. The content of this article demonstrated all these set operators and their differences using practical examples.