Mastering Oracle SQL

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 ...