INTERSECT
INTERSECT returns only rows that appear in both queries. The rules are the same as those listed for
UNION.
Syntax
<query_expression>
INTERSECT [ALL]
<query_expression>
Remarks
• According to SQL standards, INTERSECT takes precedence over UNION and EXCEPT.
• With INTERSECT ALL, if a row appears x times in the first table and y times in the second table, the row appears z times in the result table, where z is the lesser of x and y.
• INTERSECT is similar to INTERSECT ALL, plus INTERSECT eliminates duplicate rows.
Example (INTERSECT)
The following query lists the cities where suppliers and customers are found, and eliminates duplicate rows.
SELECT City
FROM /shared/examples/ds_inventory/suppliers
INTERSECT
SELECT City
FROM /shared/examples/ds_orders/customers
Example (INTERSECT ALL)
The following query lists the cities where suppliers and customers are found, but does not eliminate duplicate rows.
SELECT City
FROM /shared/examples/ds_inventory/suppliers
INTERSECT ALL
SELECT City
FROM /shared/examples/ds_orders/customers