|Oracle® Database SQL Reference
10g Release 1 (10.1)
Part Number B10759-01
You can combine multiple queries using the set operators
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 corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character).
If component queries select character data, then the datatype of the return values are determined as follows:
If both queries select values of datatype
CHAR, then the returned values have datatype
If either or both of the queries select values of datatype
VARCHAR2, then the returned values have datatype
If component queries select numeric data, then the datatype of the return values is determined by numeric precedence:
If any query selects values of type
BINARY_DOUBLE, then the returned values have datatype
If no query selects values of type
BINARY_DOUBLE but any query selects values of type
BINARY_FLOAT, then the returned values have datatype
If all queries select values of type
number, then the returned values have datatype
In queries using set operators, Oracle does not perform implicit conversion across datatype groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error.
The following query is valid:
SELECT 3 FROM DUAL INTERSECT SELECT 3f FROM DUAL;
This is implicitly converted to the following compound query:
SELECT TO_BINARY_FLOAT(3) FROM DUAL INTERSECT SELECT 3f FROM DUAL;
The following query returns an error:
SELECT '3' FROM DUAL INTERSECT SELECT 3f FROM DUAL;
The set operators are not valid on columns of type
VARRAY, or nested table.
MINUS operators are not valid on
If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the
You cannot also specify the
for_update_clause with the set operators.
You cannot specify the
order_by_clause in the
subquery of these operators.
You cannot use these operators in
SELECT statements containing
TABLE collection expressions.
Note:To comply with emerging SQL standards, a future release of Oracle will give the
The following statement combines the results of two queries with the
UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the
TO_CHAR function) when columns do not exist in one or the other table:
SELECT location_id, department_name "Department", TO_CHAR(NULL) "Warehouse" FROM departments UNION SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name FROM warehouses; LOCATION_ID Department Warehouse ----------- --------------------- -------------------------- 1400 IT 1400 Southlake, Texas 1500 Shipping 1500 San Francisco 1600 New Jersey 1700 Accounting 1700 Administration 1700 Benefits 1700 Construction ...
UNION operator returns only distinct rows that appear in either result, while the
ALL operator returns all rows. The
ALL operator does not eliminate duplicate selected rows:
SELECT product_id FROM order_items UNION SELECT product_id FROM inventories; SELECT location_id FROM locations UNION ALL SELECT location_id FROM departments;
location_id value that appears multiple times in either or both queries (such as '
1700') is returned only once by the
UNION operator, but multiple times by the
The following statement combines the results with the
INTERSECT operator, which returns only those rows returned by both queries:
SELECT product_id FROM inventories INTERSECT SELECT product_id FROM order_items;
The following statement combines results with the
MINUS operator, which returns only rows returned by the first query but not by the second:
SELECT product_id FROM inventories MINUS SELECT product_id FROM order_items;