Details: Enhanced SQL Set Operators

This page provides more detailed information about new SQL set operators and enhancements to existing operators.

Until Oracle Database 21c, only the UNION set operator could be combined with ALL. Oracle Database 21c introduces two set operators, MINUS ALL (same as EXCEPT ALL) and INTERSECT ALL.

In the examples in the graphic, the first and second statements combine results from two queries with the EXCEPT operator (being equivalent to MINUS) and return only unique rows returned by the first query but not by the second query.

The third and fourth statements combine results from two queries with the EXCEPT ALL operator (being equivalent to MINUS ALL) and return only rows returned by the first query but not by the second query, even if not unique.

The fifth and sixth statements combine results from two queries with the INTERSECT operator and return only unique rows returned by both queries.