INTERSECT
The INTERSECT combines the result sets of two or more SELECT statements, retrieving only the common rows between them.
Unlike UNION, which combines all rows and removes duplicates, INTERSECT focuses on returning rows that appear in all SELECT statements.
The syntax for the INTERSECT is as follows:
The parameters from the syntax are explained below:
- value1, value2, ... value_n: The columns you want to retrieve. You can also use SELECT * FROM to retrieve all columns.
- table1, table2: The tables from which you wish to retrieve records.
Note:
The data types of corresponding columns must be compatible.
Suppose you have two tables: customers_old and customers_new, containing customer data for different periods. You want to find the customers who are present in both tables:
Viewing the inserted values:
Now, letβs combine common customers using the INTERSECT:
The result will include only the names that appear in both tables:
The picture displays a list of customer names that appear in both tables. Only "Bob" and "Charlie" are found in both tables and shown as INTERSECT's final result.

The INTERSECT ALL retrieves all common rows between two or more tables, including duplicates.
This means that if a row appears multiple times in any of the SELECT statements, it will be included in the final result set multiple times.
The syntax for INTERSECT ALL is similar to INTERSECT:
The parameters from the syntax are explained below:
- value1, value2, ... value_n: The columns you wish to retrieve. You can also retrieve all the values using the SELECT * FROM query.
- table1, table2: The tables from which you want to retrieve records.
Note:
The data types of corresponding columns in the SELECT queries must be compatible.
Letβs create three tables of products from different years. You want to find the common products among all three categories, including duplicates.
Display the tables using the query below:
Then, combine common products from all three categories using the INTERSECT ALL:
The result will include the products that are common among all three categories, including duplicates:
The illustration shows a list of product names common to all three years, including duplicates. In this case, the result is the product name "Phone," which appears across all three tables.

ο»Ώ