第三章 oracle集合运算符基础.pptVIP

The Oracle Server and SET Operators When a query uses SET operators, the Oracle Server eliminates duplicate rows automatically except in the case of the UNION ALL operator. The column names in the output are decided by the column list in the first SELECT statement. By default, the output is sorted in ascending order of the first column of the SELECT clause. The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype. If component queries select character data, the datatype of the return values are determined as follows: If both queries select values of datatype CHAR, the returned values have datatype CHAR. If either or both of the queries select values of datatype VARCHAR2, the returned values  have datatype VARCHAR2. Instructor Note You might want to mention that the output is sorted in ascending order of the first column, then the second column, and so on, of the SELECT clause. Matching the SELECT Statements As the expressions in the select lists of the queries must match in number, you can use dummy columns and the datatype conversion functions to comply with this rule. In the slide, the name location is given as the dummy column heading. The TO_NUMBER function is used in the first query to match the NUMBER datatype of the LOCATION_ID column retrieved by the second query. Similarly, the TO_DATE function in the second query is used to match the DATE datatype of the HIRE_DATE column retrieved by the first query. Instructor Note Demonstration: demo\15_union3.sql, demo\15_dummy.sql Purpose: The demonstration 15_union3.sql illustrates using conversion functions while matching columns in the two select lists. The demonstration 15_dummy.sql uses dummy columns in order to match the select lists. For the 15_dummy.sql, run the script, then uncomment the REMARKS, add ORDER BY 2, and rerun. You might want to mention that the conversion functions in the code shown on the sli