oracle教程Les15_chinese.pptVIP

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 slide are not mandatory. The code will work fine even without the conversion functions, but it is recommended to explicitly convert values for performance benefits. Matching the SELECT Statement: Example The EMPLOYEES and JOB_HISTORY tables have several columns in common; for example, EMPLOYEE_ID, JOB_ID and DEPARTMENT_ID. But what if you want the query to display the EMPLOYEE_ID, JOB_ID, and SALARY using the UNION operator, knowing that the salary exists only in the, EMPLOYEES table? The code example in the slide matches the EMPLOYEE_ID and the JOB_ID columns in the EMPLOYEES and in the JOB_HISTORY tables. A literal value of 0 is added to the JOB_HISTORY SELECT statement to match the numeric SALARY column in the EMPLOYEES SELECT statement. In the preceding results, each row in the output that corresponds to a record from the JOB_HISTORY table contains a 0 in the SALARY column. Controlling the Order of Rows By default, the output is sorted in ascending order on the first c