The following rules apply to the use of INTERSECT:
- You can use INTERSECT within the following operations:
- Simple queries
- Derived tables
You cannot use the HASH BY or LOCAL ORDER BY clauses in derived tables with set operators.
- Subqueries
- INSERT ... SELECT statements
- View definitions
- Each query connected by INTERSECT runs to produce a result consisting of a set of rows. The intersection must include the same number of columns from each table in each SELECT statement and the data types of these columns must be compatible.
- INTERSECT cannot be used within the following:
- SELECT AND CONSUME statements.
- WITH RECURSIVE clause
- CREATE RECURSIVE VIEW statements