SQL Reference - INTERSECT

(query_1) INTERSECT (query_2)

query_1: It specifies the first SELECT statement. It is the initial set of data that will be compared to the data provided in query_2 for any identical rows.
query_2: This is the second SELECT statement. It defines the second set of data which is compared with query_1’s results. Only common rows between both queries will be included in the final output.

Example

SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;

Output

 column_name
-------------
 Value1
 Value2
(2 rows)

Explanation

The INTERSECT function in SQL is used to return the common rows from two or more SELECT statements. The above example selects a specific column from table1 and table2, and the INTERSECT command returns only the common rows that appear in both tables.

(SELECT statement)INTERSECT(SELECT statement)

select statement: A standard SQL SELECT operation that is used to retrieve rows from one or more tables.
intersect: A set operator that returns the common records that are output by the SELECT queries on either side of it.
select statement: The second SELECT operation that is compared against the first to discover and return their intersection.

Example

SELECT city
FROM Employees
INTERSECT
SELECT city
FROM Customers;

Output

CITY
----
New York
Seattle
Dallas

Explanation

The INTERSECT operator is used in SQL to combine two SELECT statements, but returns rows only from the first SELECT statement that exactly match the rows in the second SELECT statement. In this example, the cities returned are those where both employees and customers reside.

(SELECT column1 [, column2 …])FROM table1 [, table2 …]INTERSECT(SELECT column1 [, column2 …])FROM table3 [, table4 …];

column1 [, column2 …]: These are the column names in the tables that you want to compare. They are used to find common records (based on the columns selected) between the specified tables.
table1 [, table2 …]: Serves as the source of the first dataset. You can specify more than one table from which to select.
intersect: This is an SQL operator that returns the common records between two select statements.
(select column1 [, column2 …]): This is the first select statement which is used on table1 [, table2 …].
from table3 [, table4 …]: Specifies the source of the second dataset. Similarly, more than one table can be selected.
(select column1 [, column2 …]): This is the second select statement which is used on table3 [, table4 …].

Example

SELECT City FROM Customers
INTERSECT
SELECT City FROM Suppliers;

Output

City
New York
Paris

Explanation

The INTERSECT operator returns the common records (i.e., records that exist in both the SELECT statement before and after the INTERSECT operator). In this example, it results in returning the common cities from the Customers and Suppliers tables.