SET operators in Oracle

SET operators in Oracle

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

The following list briefly describes the four set operations supported by Oracle SQL:

UNION ALL : Combines the results of two SELECT statements into one result set.

UNION : Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.

MINUS : Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

INTERSECT : Returns only those rows that are returned by each of two SELECT statements

SQL statements containing these set operators are referred to as compound queries, and each SELECT statement in a compound query is referred to as a component query. Two SELECTs can be combined into a compound query by a set operation only if they satisfy the following two conditions:

  1. The result sets of both the queries must have the same number of columns.  
  2. The datatype of each column in the second result set must match the datatype of its corresponding column in the first result set.

Rules and Restrictions on Set Operations:

  • Column names for the result set are derived from the first SELECT.
  • If we want to use ORDER BY in a query involving set operations, we must place the ORDER BY at the end of the entire statement. The ORDER BY clause can appear only once at the end of the compound query.
  • Component queries are executed from top to bottom. If we want to alter the sequence of execution, use parentheses appropriately.
  • Set operations are not permitted on columns of type BLOB, CLOB, BFILE, and VARRAY, nor are set operations permitted on nested table columns.
  • Since UNION, INTERSECT, and MINUS operators involve sort operations, they are not allowed on LONG columns. However, UNION ALL is allowed on LONG columns.