SET运算符 SQL技术方案.pptxVIP

SET 运算符讲师：佟刚新浪微博：尚硅谷-佟刚目标通过本章学习，您将可以:描述 SET 操作符将多个查询用 SET 操作符联接组成一个新的查询 排序ABINTERSECT SET 操作符ABABUNION/UNION ALLABMINUSUNION 操作符ABUNION 操作符返回两个查询的结果集的并集UNION 操作符举例SELECT employee_id, job_idFROM employeesUNIONSELECT employee_id, job_idFROM job_history;……ABINTERSECT 操作符INTERSECT 操作符返回两个结果集的交集INTERSECT 操作符举例SELECT employee_id, job_idFROM employeesINTERSECTSELECT employee_id, job_idFROM job_history;ABMINUS 操作符MINUS 操作符返回两个结果集的补集 MINUS 操作符举例SELECT employee_id,job_idFROM employeesMINUSSELECT employee_id,job_idFROM job_history;…使用 SET 操作符注意事项在SELECT 列表中的列名和表达式在数量和数据类型上要相对应括号可以改变执行的顺序ORDER BY 子句:只能在语句的最后出现可以使用第一个查询中的列名或别名 SET 操作符除 UNION ALL 之外，系统会自动将重复的记录删除系统将第一个查询的列名显示在输出中除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列 匹配各SELECT 语句举例SELECT department_id, TO_NUMBER(null) location, hire_dateFROM employeesUNIONSELECT department_id, location_id, TO_DATE(null)FROM departments;…SELECT employee_id, job_id,salaryFROM employeesUNIONSELECT employee_id, job_id,0FROM job_history;匹配各SELECT 语句举例…总结通过本章学习,您已经可以:使用 UNION 操作符使用 UNION ALL 操作符使用 INTERSECT 操作符使用 MINUS操作符使用 ORDER BY 对结果集排序Lesson AimIn this lesson, you learn how to write queries by using SET operators.The SET OperatorsThe SET operators combine the results of two or more component queries into one result. Queries containing SET operators are called compound queries.All SET operators have equal precedence. If a SQL statement contains multiple SET operators, the Oracle server evaluates them from left (top) to right (bottom) if no parentheses explicitly specify another order. You should use parentheses to specify the order of evaluation explicitly in queries that use the INTERSECT operator with other SET operators.Note: In the slide, the light color (gray) in the diagram represents the query result.Instructor NoteThe INTERSECT and MINUS operators are not ANSI SQL-99 compliant. They are Oracle-specific.The UNION OperatorThe UNION operator returns all rows selected by either query. Use the UNION operator to return all r