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