7.5 Rules and Restrictions on Set Operations
Other than the union compatibility conditions discussed at the beginning of the chapter, there are some other rules and restrictions that apply to the set operations. These rules and restrictions are described in this section.
Column names for the result set are derived from the first SELECT:
SELECT cust_nbr "Customer ID", name "Customer Name"
FROM customer
WHERE region_id = 5
UNION
SELECT c.cust_nbr "ID", c.name "Name"
FROM customer c
WHERE c.cust_nbr IN (SELECT o.cust_nbr
FROM cust_order o, employee e
WHERE o.sales_emp_id = e.emp_id
AND e.lname = 'MARTIN');
Customer ID Customer Name
----------- ----------------------
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries
8 Zantech Inc.
6 rows selected.Although both SELECTs use column aliases, the result set takes the column names from the first SELECT. The same thing happens when you create a view based on a set operation. The column names in the view are taken from the first SELECT:
CREATE VIEW v_test_cust AS
SELECT cust_nbr "Customer_ID", name "Customer_Name"
FROM customer
WHERE region_id = 5
UNION
SELECT c.cust_nbr "ID", c.name "Name"
FROM customer c
WHERE c.cust_nbr IN (SELECT o.cust_nbr
FROM cust_order o, employee e
WHERE o.sales_emp_id = e.emp_id
AND e.lname = 'MARTIN');
View created.
DESC v_test_cust
Name Null? Type
------------------------------- -------- ----
Customer_ID NUMBER
Customer_Name VARCHAR2(45)If you want to use ORDER BY in a query involving set operations, you 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. The component queries can't have individual ORDER BY clauses. For example:
SELECT cust_nbr, name
FROM customer
WHERE region_id = 5
UNION
SELECT emp_id, lname
FROM employee
WHERE lname = 'MARTIN'
ORDER BY cust_nbr;
CUST_NBR NAME
---------- ---------------------
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries
7654 MARTIN
6 rows selected.Note that the column name used in the ORDER BY clause of this query is taken from the first SELECT. You couldn't order these results by emp_id. If you attempt to ORDER BY emp_id, you will get an error, as in the following example:
SELECT cust_nbr, name
FROM customer
WHERE region_id = 5
UNION
SELECT emp_id, lname
FROM employee
WHERE lname = 'MARTIN'
ORDER BY emp_id;
ORDER BY EMP_ID
*
ERROR at line 8:
ORA-00904: invalid column nameThe ORDER BY clause doesn't recognize the column names of the second SELECT. To avoid confusion over column names, it is a common practice to ORDER BY column positions:
SELECT cust_nbr, name
FROM customer
WHERE region_id = 5
UNION
SELECT emp_id, lname
FROM employee
WHERE lname = 'MARTIN'
ORDER BY 1;
CUST_NBR NAME
---------- ---------------------
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries
7654 MARTIN
6 rows selected.For better readability and maintainability of your queries, we recommend that you explicitly use identical column aliases in all the component queries, and then use these column aliases in the ORDER BY clause.
|
The following list summarizes some simple rules, restrictions, and notes that don't require examples:
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.
Set operations are not allowed on SELECT statements containing TABLE collection expressions.
SELECT statements involved in set operations can't use the FOR UPDATE clause.
The number and size of columns in the SELECT list of component queries are limited by the block size of the database. The total bytes of the columns SELECTed can't exceed one database block.
