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 as follows:
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, C.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 we 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, C.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 we want to use ORDER BY in a query involving set operations, we must ...
Get Mastering Oracle SQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.