INTERSECT
INTERSECT
Overview
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.
Syntax
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 useSELECT * FROM
to retrieve all columns. -
table1, table2
: The tables from which you wish to retrieve records.
Example
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.
INTERSECT ALL
Overview
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.
Syntax
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 theSELECT * FROM
query. -
table1, table2
: The tables from which you want to retrieve records.
SELECT
queries must be compatible.Example
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.