PostgreSQL: INTERSECT Operator

PostgreSQL INTERSECT operator is used to combine the result set of two or more SELECT statements into a single result set. The INTERSECT operator returns only rows that are common in both result sets.

Syntax

SELECT Query 1
INTERSECT
SELECT Query 2
INTERSECT
SELECT Query 3
...

The following rule must be applied to combine the result set of two or more quires using the INTERSECT operator.

There must be the same number of columns or expressions in each SELECT statement.
The corresponding columns in each SELECT statement must have similar data types.

Consider we have Employee and Person tables as bellow with ID as primary key in both tables.

Now let's use INTERSECT operator to fetch common data from both the tables.

Example: INTERSECT Operator

SELECT * FROM Employee
INTERSECT
SELECT * FROM Person;

The following displays the result of the above statement in pgAdmin.

Now let's intersect the result only by first_name and last_name columns along with the ORDER BY clause.

Example: INTERSECT with Order By

SELECT first_name, last_name FROM Employee
INTERSECT
SELECT first_name, last_name FROM Person ORDER BY first_name;

The above query returns two rows, as first_name and last_name are the same in both Employee and Person tables and the result set is sorted by the first_name column.

Note that the number of columns and the types of columns must be the same in all SELECT queries; otherwise, an error would be raised.

In the following example, the type of each column in a sequence in the first query does not match the type of columns in a sequence in the second query, and so it would raise an error.

SELECT id, first_name FROM Employee
INTERSECT  
SELECT first_name, last_name FROM Person