(九)集合运算.ppt

Lesson Aim In this lesson, you learn about more advanced features of the SELECT statement. You can write subqueries in the WHERE clause of another SQL statement to obtain values based on an unknown conditional value. This lesson covers single-row subqueries and multiple-row subqueries. 集合运算 目标 通过本章学习，您将可以: 描述集合操作符 使用集合运算完成在一条查询语句中的负责查询 控制返回行的顺序 集合运算符 UNION/UNION ALL 并集 A B A B A B INTERSECT 交集 A B MINUS 差集 并集 A B UNION运算符返回两个集合去掉重复元素后的所有记录。 使用Union运算符 显示员工当前和之前的工作情况，每次记录显示一次。 SELECT employee_id, job_id FROM employees UNION SELECT employee_id, job_id FROM job_history; … … Union All A B UNION ALL 返回两个集合的所有记录，包括重复的 使用Union All 显示所有员工当前和之前的部门信息 SELECT employee_id, job_id, department_id FROM employees UNION ALL SELECT employee_id, job_id, department_id FROM job_history ORDER BY employee_id; … … 交集 A B INTERSECT 运算符返回同时属于两个集合的记录 使用INTERSECT运算符 显示薪水同时位于级别1（700~1300）和级别2（1201~1400）的员工信息。 select ename,sal from emp where sal between 700 and 1300 INTERSECT select ename,sal from emp where sal between 1201 and 1400; 差集 A B MINUS返回属于第一个集合，但不属于第二个集合的记录。 Lesson Aim In this lesson, you learn about more advanced features of the SELECT statement. You can write subqueries in the WHERE clause of another SQL statement to obtain values based on an unknown conditional value. This lesson covers single-row subqueries and multiple-row subqueries.