PostgreSQL – INTERSECT Operator
The INTERSECT operator is used to retrieve the common rows between the result sets of two or more SELECT queries. It returns only the distinct rows that are present in all specified queries. Unlike UNION and UNION ALL, the INTERSECT operator doesn’t include duplicate rows.
The basic syntax of the INTERSECT operator is as follows:
SELECT columns FROM table_name1 INTERSECT SELECT columns FROM table_name2;Here’s an example to illustrate how to use the INTERSECT operator:
Consider two tables named fruits and vegetables, both with columns name and category. You want to find the names of items that exist both in the “fruits” category and the “vegetables” category.
SELECT name FROM fruits INTERSECT SELECT name FROM vegetables;In this query, the INTERSECT operator combines the distinct names that appear in both the fruits and vegetables tables.
The number of columns and their data types must be the same in both SELECT statements when using the INTERSECT operator.
The INTERSECT operator is useful when you want to identify the overlapping elements between two or more sets of data. It provides a way to find common elements across different categories or conditions.