Overview

In this tutorial, you will learn how to use the PostgreSQL Union, Intercept and Except operators to combine result sets of two or more queries. These operators helps to combine result sets of two select statements into a single result .

UNION Operator

The UNION operator helps to combine the result sets of two or more select statements into a single result set.

Basic Syntax

SELECT col1,col2..colN
FROM table A
UNION
SELECT col1,col2..colN
FROM table B;

As Union means in English, this query returns combined result set from both the tables. However, no duplicates are returned. To retain the duplicate rows, the UNION ALL clause must be used instead.

Examples

Let us consider the following tables, books and books2.

Sample query 1

The following query combines and returns all the distinct rows from both the tables combined. It is possible to join 'N' tables in a single query.

select * from books
UNION
select * from books2;

Sample query 2

The following query combines and returns all available rows from both the tables, including the duplicates ones.

select * from books
UNION ALL
select * from books2;

INTERSECT Operator

Like the Union operator, the intersect operator also combines the result sets of multiple select statements into a single result set but in this case the data which is common to the tables is returned.

In order to make INTERSECT operator work, the following conditions must be met:

• The queries must have the same number of columns along with same order.
• The data types of the columns in the queries must be similar.

Basic Syntax

SELECT col1,col2..colN
FROM table A
INTERSECT
SELECT col1,col2..colN
FROM table B;

The INTERSECT operator can work on multiple tables at a time.

Examples

Let us consider the above tables to run the following queries.

Sample query 1

The following query returns the common data between the two tables.

select * from books
INTERSECT
select * from books2;

Sample query 2

Let us now include one more table to the query and check how INTERSECT behaves in both scenarios, when the 3rd table has common data and when does not.

When the 3rd table has common data: 

select * from books
INTERSECT
select * from books2
INTERSECT
select * from books4;

Here we see  all the three tables has one row of common data.

When the 3rd table has uncommon data: 

First, we will modify the data by updating the 'id' of the only record in table books4 and then verify the output of the above query.

On re-executing the above query we see no rows are returned. Thus proves the fact that all criteria of the query must be met to get a result.

EXCEPT Operator

The except operator operates on two or more tables and returns distinct rows from the first (left) query that are not in the second (right) query. It can be said that the EXCEPT operator behaves in a similar fashion as that of the LEFT JOIN.

Basic Syntax

SELECT col1,col2..colN
FROM table A
EXCEPT
SELECT col1,col2..colN
FROM table B;

Similar to the UNION & INTERCEPT operator, the EXCEPT operator can also work on multiple tables at a time and the following conditions must hold true

• The queries must have the same number of columns along with same order.
• The data types of the columns in the queries must be similar.

Examples

Let us consider the above tables to execute the following queries.

Sample Query 1

select * from books
EXCEPT
select * from books2;

Over here we see that the query does not return any result, reason being there is no such data in table 'books' which is not there in the table 'books2'. The 1st image of this article verifies this statement. Now, let us execute one more query by swapping the tables and check the output.

Sample Query 2

select * from books2
EXCEPT
select * from books;

This explains the behavior of the EXCEPT clause which is it returns distinct rows from the first (left) table that are not in the second (right) table.

Conclusion

In this article we study the fundamental behavior of three very important clauses of PostgreSQL namely UNION, INTERCEPT & EXCEPT with suitable examples. Hopefully, this article will help you get started in your journey of these three clauses .