Les15_oracle数据库教程.pptVIP

SET 运算符 目标 通过本章学习，您将可以: 描述 SET 操作符 将多个查询用SET 操作符联接组成一个新的查询 排序 SET 操作符 UNION 操作符 UNION 操作符返回两个查询的结果集的并集 UNION 操作符举例 UNION ALL 操作符 UNION ALL 操作符举例 INTERSECT 操作符 INTERSECT 操作符举例 MINUS 操作符 MINUS 操作符举例 使用 SET 操作符注意事项 在SELECT 列表中的列名和表达式在数量和数据类型上要相对应 括号可以改变执行的顺序 ORDER BY 子句: 只能在语句的最后出现 可以使用第一个查询中的列名, 别名或相对位置 SET 操作符 除 UNION ALL之外，系统会自动将重复的记录删除 系统将第一个查询的列名显示在输出中 除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列 匹配各SELECT 语句举例 匹配各SELECT 语句举例 使用相对位置排序举例 总结 通过本章学习,您已经可以: 使用 UNION 操作符 使用 UNION ALL 操作符 使用 INTERSECT 操作符 使用 MINUS操作符 使用 ORDER BY 对结果集排序 Practice 15 Overview This practice covers using the Oracle9i datetime functions. The MINUS Operator Use the MINUS operator to return rows returned by the first query that are not present in the second query (the first SELECT statement MINUS the second SELECT statement). Guidelines The number of columns and the datatypes of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical. All of the columns in the WHERE clause must be in the SELECT clause for the MINUS operator to work. Instructor Note To illustrate the MINUS operator, run the script demo\15_minus.sql. The MINUS Operator (continued) In the example in the slide, the employee IDs and Job IDs in the JOB_HISTORY table are subtracted from those in the EMPLOYEES table. The results set displays the employees remaining after the subtraction; they are represented by rows that exist in the EMPLOYEES table but do not exist in the JOB_HISTORY table. These are the records of the employees who have not changed their jobs even once. SET Operator Guidelines The expressions in the select lists of the queries must match in number and datatype. Queries that use UNION, UNION ALL, INTERSECT, and MINUS SET operators in their WHERE clause must have the same number and type of columns in their SELECT list. For example: SELECT employee_id, department_id FROM employees