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.
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.
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
last_name columns along with the ORDER BY clause.
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
last_name are the same in both
Person tables and the result set is sorted by the
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