Query Operators

Query operators allow queries to be combined using set operators.

In this Topic:

General Syntax

[ ( ] <query> [ ) ] { INTERSECT | MINUS | UNION [ ALL ] } [ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]

General Usage Notes

  • Each query may itself contain query operators, to allow any number of query expressions to be combined with set operators.
  • The ORDER BY and LIMIT / FETCH clauses are applied to the result of the set operator.

INTERSECT

Returns rows from one query’s result set which also appear in another query’s result set, with duplicate elimination.

Syntax

SELECT ...
INTERSECT
SELECT ...

MINUS , EXCEPT

Removes rows from one query’s result set which appear in another query’s result set, with duplicate elimination.

The MINUS and EXCEPT keywords have the same meaning and can be used interchangeably.

Syntax

SELECT ...
MINUS
SELECT ...

SELECT ...
EXCEPT
SELECT ...

UNION [ ALL ]

Combines the result sets from two queries:

  • UNION combines with duplicate elimination.
  • UNION ALL combines without duplicate elimination.

The default is UNION (i.e. duplicate elimination).

Syntax

SELECT ...
UNION [ ALL ]
SELECT ...