Objectives
After completing this lesson,you should be able to do the following:
- Describe set operators.
- Use a set operator to combine multiple queries into a single query.
- Control the order of rows returned.
Lesson Agenda
- Set Operators:Types and guidelines
- Tables used in this lesson
- UNION and UNION ALL operator
- INTERSECT operator
- MINUS operator
- Matching the SELEC T statements
- Using the ORDER BY clause in set operators
Set Operators
UNION ALL 不考虑重复元素
UNION 考虑重复元素，会去掉重复的元素
INTERSECT 求交集元素信息
MINUS 两个集合相减
Set Operator Guidelines
- The expressions in the SELECT lists must match in number.
- The data type of each column in the second query must match the data type of its correspongding column in the first query
- Parentheses can be used to alter the sequence of execution
- ORDER BY clause appear only at the very end of the statement.
The Oracle Server and Set Operator
- Duplicate rows are automatically eliminated execpt in UNIION ALL.
- Column names from the first query appear in the result.
- The output is sorted in ascending order by default except in UNION ALL.
/*
1、按照查询的第一列进行排序.除了UNION ALL。
2、另外，如果第一列是CHAR(10),第二列是CHAR(20)，那么最终的结果将是按照CHAR(20)进行输出.计算原则:按照最长的算
3、集合操作，是不忽略NULL VALUE的.
*/
Tables Used in This Lesson
- The tables used in this lesson are:
- EMPLOYEES:Provides details regarding all current employees
- JOB_HISTORY:Records the details of the start date and end date of the former job,and the job identification number and department when an employee switches jobs
UNION Operator
Using the UNION Operator
- Display the current and previous job details of all employees.
- Display each employee only once.
View Code
SQL> SELECT employee_id,job_id FROM employees 2 UNION 3 SELECT employee_id,job_id FROM job_history; EMPLOYEE_ID JOB_ID ----------- ---------- 100 AD_PRES 101 AC_ACCOUNT 101 AC_MGR 101 AD_VP 102 AD_VP 102 IT_PROG 103 IT_PROG 104 IT_PROG 105 IT_PROG 106 IT_PROG 107 IT_PROG EMPLOYEE_ID JOB_ID ----------- ---------- 108 FI_MGR 109 FI_ACCOUNT 110 FI_ACCOUNT 111 FI_ACCOUNT 112 FI_ACCOUNT 113 FI_ACCOUNT 114 PU_MAN 114 ST_CLERK 115 PU_CLERK 116 PU_CLERK 117 PU_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 118 PU_CLERK 119 PU_CLERK 120 ST_MAN 121 ST_MAN 122 ST_CLERK 122 ST_MAN 123 ST_MAN 124 ST_MAN 125 ST_CLERK 126 ST_CLERK 127 ST_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 128 ST_CLERK 129 ST_CLERK 130 ST_CLERK 131 ST_CLERK 132 ST_CLERK 133 ST_CLERK 134 ST_CLERK 135 ST_CLERK 136 ST_CLERK 137 ST_CLERK 138 ST_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 139 ST_CLERK 140 ST_CLERK 141 ST_CLERK 142 ST_CLERK 143 ST_CLERK 144 ST_CLERK 145 SA_MAN 146 SA_MAN 147 SA_MAN 148 SA_MAN 149 SA_MAN EMPLOYEE_ID JOB_ID ----------- ---------- 150 SA_REP 151 SA_REP 152 SA_REP 153 SA_REP 154 SA_REP 155 SA_REP 156 SA_REP 157 SA_REP 158 SA_REP 159 SA_REP 160 SA_REP EMPLOYEE_ID JOB_ID ----------- ---------- 161 SA_REP 162 SA_REP 163 SA_REP 164 SA_REP 165 SA_REP 166 SA_REP 167 SA_REP 168 SA_REP 169 SA_REP 170 SA_REP 171 SA_REP EMPLOYEE_ID JOB_ID ----------- ---------- 172 SA_REP 173 SA_REP 174 SA_REP 175 SA_REP 176 SA_MAN 176 SA_REP 177 SA_REP 178 SA_REP 179 SA_REP 180 SH_CLERK 181 SH_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 182 SH_CLERK 183 SH_CLERK 184 SH_CLERK 185 SH_CLERK 186 SH_CLERK 187 SH_CLERK 188 SH_CLERK 189 SH_CLERK 190 SH_CLERK 191 SH_CLERK 192 SH_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 193 SH_CLERK 194 SH_CLERK 195 SH_CLERK 196 SH_CLERK 197 SH_CLERK 198 SH_CLERK 199 SH_CLERK 200 AC_ACCOUNT 200 AD_ASST 201 MK_MAN 201 MK_REP EMPLOYEE_ID JOB_ID ----------- ---------- 202 MK_REP 203 HR_REP 204 PR_REP 205 AC_MGR 206 AC_ACCOUNT 115 rows selected.
UNION ALL Operator
Using the UNION ALL Operator
- Display the current and previous departments of all employees.
View Code
SQL> SELECT employee_id,job_id FROM employees 2 UNION ALL 3 SELECT employee_id,job_id FROM job_history 4 ORDER BY employee_id; EMPLOYEE_ID JOB_ID ----------- ---------- 100 AD_PRES 101 AD_VP 101 AC_ACCOUNT 101 AC_MGR 102 AD_VP 102 IT_PROG 103 IT_PROG 104 IT_PROG 105 IT_PROG 106 IT_PROG 107 IT_PROG EMPLOYEE_ID JOB_ID ----------- ---------- 108 FI_MGR 109 FI_ACCOUNT 110 FI_ACCOUNT 111 FI_ACCOUNT 112 FI_ACCOUNT 113 FI_ACCOUNT 114 PU_MAN 114 ST_CLERK 115 PU_CLERK 116 PU_CLERK 117 PU_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 118 PU_CLERK 119 PU_CLERK 120 ST_MAN 121 ST_MAN 122 ST_MAN 122 ST_CLERK 123 ST_MAN 124 ST_MAN 125 ST_CLERK 126 ST_CLERK 127 ST_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 128 ST_CLERK 129 ST_CLERK 130 ST_CLERK 131 ST_CLERK 132 ST_CLERK 133 ST_CLERK 134 ST_CLERK 135 ST_CLERK 136 ST_CLERK 137 ST_CLERK 138 ST_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 139 ST_CLERK 140 ST_CLERK 141 ST_CLERK 142 ST_CLERK 143 ST_CLERK 144 ST_CLERK 145 SA_MAN 146 SA_MAN 147 SA_MAN 148 SA_MAN 149 SA_MAN EMPLOYEE_ID JOB_ID ----------- ---------- 150 SA_REP 151 SA_REP 152 SA_REP 153 SA_REP 154 SA_REP 155 SA_REP 156 SA_REP 157 SA_REP 158 SA_REP 159 SA_REP 160 SA_REP EMPLOYEE_ID JOB_ID ----------- ---------- 161 SA_REP 162 SA_REP 163 SA_REP 164 SA_REP 165 SA_REP 166 SA_REP 167 SA_REP 168 SA_REP 169 SA_REP 170 SA_REP 171 SA_REP EMPLOYEE_ID JOB_ID ----------- ---------- 172 SA_REP 173 SA_REP 174 SA_REP 175 SA_REP 176 SA_REP 176 SA_MAN 176 SA_REP 177 SA_REP 178 SA_REP 179 SA_REP 180 SH_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 181 SH_CLERK 182 SH_CLERK 183 SH_CLERK 184 SH_CLERK 185 SH_CLERK 186 SH_CLERK 187 SH_CLERK 188 SH_CLERK 189 SH_CLERK 190 SH_CLERK 191 SH_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 192 SH_CLERK 193 SH_CLERK 194 SH_CLERK 195 SH_CLERK 196 SH_CLERK 197 SH_CLERK 198 SH_CLERK 199 SH_CLERK 200 AD_ASST 200 AD_ASST 200 AC_ACCOUNT EMPLOYEE_ID JOB_ID ----------- ---------- 201 MK_MAN 201 MK_REP 202 MK_REP 203 HR_REP 204 PR_REP 205 AC_MGR 206 AC_ACCOUNT 117 rows selected.
INTERSECT Operator
Using the INTERSECT Operator
- Display the employee IDs and IDs of those employees who currently have a job tilte that is the same as their previous one(that is,the changed jobs but have gone back to doing the same job they did previously).
View Code
SQL> SELECT employee_id,job_id 2 FROM employees 3 INTERSECT 4 SELECT employee_id,job_id 5 FROM job_history; EMPLOYEE_ID JOB_ID ----------- ---------- 176 SA_REP 200 AD_ASST
/*
查询出来的交集，说明employees、job_history表中都存在记录.
*/
MINUS Operator
Using the MINUS Operator
- Display the employee IDs of those employees who have not changed their jobs even once.
View Code
SQL> SELECT employee_id,job_id FROM employees 2 MINUS 3 SELECT employee_id,job_id FROM job_history; EMPLOYEE_ID JOB_ID ----------- ---------- 100 AD_PRES 101 AD_VP 102 AD_VP 103 IT_PROG 104 IT_PROG 105 IT_PROG 106 IT_PROG 107 IT_PROG 108 FI_MGR 109 FI_ACCOUNT 110 FI_ACCOUNT EMPLOYEE_ID JOB_ID ----------- ---------- 111 FI_ACCOUNT 112 FI_ACCOUNT 113 FI_ACCOUNT 114 PU_MAN 115 PU_CLERK 116 PU_CLERK 117 PU_CLERK 118 PU_CLERK 119 PU_CLERK 120 ST_MAN 121 ST_MAN EMPLOYEE_ID JOB_ID ----------- ---------- 122 ST_MAN 123 ST_MAN 124 ST_MAN 125 ST_CLERK 126 ST_CLERK 127 ST_CLERK 128 ST_CLERK 129 ST_CLERK 130 ST_CLERK 131 ST_CLERK 132 ST_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 133 ST_CLERK 134 ST_CLERK 135 ST_CLERK 136 ST_CLERK 137 ST_CLERK 138 ST_CLERK 139 ST_CLERK 140 ST_CLERK 141 ST_CLERK 142 ST_CLERK 143 ST_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 144 ST_CLERK 145 SA_MAN 146 SA_MAN 147 SA_MAN 148 SA_MAN 149 SA_MAN 150 SA_REP 151 SA_REP 152 SA_REP 153 SA_REP 154 SA_REP EMPLOYEE_ID JOB_ID ----------- ---------- 155 SA_REP 156 SA_REP 157 SA_REP 158 SA_REP 159 SA_REP 160 SA_REP 161 SA_REP 162 SA_REP 163 SA_REP 164 SA_REP 165 SA_REP EMPLOYEE_ID JOB_ID ----------- ---------- 166 SA_REP 167 SA_REP 168 SA_REP 169 SA_REP 170 SA_REP 171 SA_REP 172 SA_REP 173 SA_REP 174 SA_REP 175 SA_REP 177 SA_REP EMPLOYEE_ID JOB_ID ----------- ---------- 178 SA_REP 179 SA_REP 180 SH_CLERK 181 SH_CLERK 182 SH_CLERK 183 SH_CLERK 184 SH_CLERK 185 SH_CLERK 186 SH_CLERK 187 SH_CLERK 188 SH_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 189 SH_CLERK 190 SH_CLERK 191 SH_CLERK 192 SH_CLERK 193 SH_CLERK 194 SH_CLERK 195 SH_CLERK 196 SH_CLERK 197 SH_CLERK 198 SH_CLERK 199 SH_CLERK EMPLOYEE_ID JOB_ID ----------- ---------- 201 MK_MAN 202 MK_REP 203 HR_REP 204 PR_REP 205 AC_MGR 206 AC_ACCOUNT 105 rows selected.
Matching the SELECT Statements
- Using the UNION operator,display the location ID,department name,and the state where it is located.
- You must match the data type(using the TO_CHAR function or any other conversion functions) when columns do not exist in one ore the other table.
View Code
SQL> SELECT location_id,department_name "Department",TO_CHAR(NULL) "Warehouse location" FROM departments 2 UNION 3 SELECT location_id,TO_CHAR(NULL) "Department",state_province FROM locations; LOCATION_ID Department Warehouse location ----------- ------------------------------ ------------------------- 1000 1100 1200 Tokyo Prefecture 1300 1400 IT 1400 Texas 1500 Shipping 1500 California 1600 New Jersey 1700 Accounting 1700 Administration LOCATION_ID Department Warehouse location ----------- ------------------------------ ------------------------- 1700 Benefits 1700 Construction 1700 Contracting 1700 Control And Credit 1700 Corporate Tax 1700 Executive 1700 Finance 1700 Government Sales 1700 IT Helpdesk 1700 IT Support 1700 Manufacturing LOCATION_ID Department Warehouse location ----------- ------------------------------ ------------------------- 1700 NOC 1700 Operations 1700 Payroll 1700 Purchasing 1700 Recruiting 1700 Retail Sales 1700 Shareholder Services 1700 Treasury 1700 Washington 1800 Marketing 1800 Ontario LOCATION_ID Department Warehouse location ----------- ------------------------------ ------------------------- 1900 Yukon 2000 2100 Maharashtra 2200 New South Wales 2300 2400 Human Resources 2400 2500 Sales 2500 Oxford 2600 Manchester 2700 Public Relations LOCATION_ID Department Warehouse location ----------- ------------------------------ ------------------------- 2700 Bavaria 2800 Sao Paulo 2900 Geneve 3000 BE 3100 Utrecht 3200 Distrito Federal, 50 rows selected.
Matching the SELECT Statement:Example
- Using the UNION operator,display the employee_id,job_id,and salary of all employees.
View Code
SQL> SELECT employee_id,job_id,salary FROM employees 2 UNION 3 SELECT employee_id,job_id,0 FROM job_history; EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 100 AD_PRES 24000 101 AC_ACCOUNT 0 101 AC_MGR 0 101 AD_VP 17000 102 AD_VP 17000 102 IT_PROG 0 103 IT_PROG 9000 104 IT_PROG 6000 105 IT_PROG 4800 106 IT_PROG 4800 107 IT_PROG 4200 EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 108 FI_MGR 12008 109 FI_ACCOUNT 9000 110 FI_ACCOUNT 8200 111 FI_ACCOUNT 7700 112 FI_ACCOUNT 7800 113 FI_ACCOUNT 6900 114 PU_MAN 11000 114 ST_CLERK 0 115 PU_CLERK 3100 116 PU_CLERK 2900 117 PU_CLERK 2800 EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 118 PU_CLERK 2600 119 PU_CLERK 2500 120 ST_MAN 8000 121 ST_MAN 8200 122 ST_CLERK 0 122 ST_MAN 7900 123 ST_MAN 6500 124 ST_MAN 5800 125 ST_CLERK 3200 126 ST_CLERK 2700 127 ST_CLERK 2400 EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 128 ST_CLERK 2200 129 ST_CLERK 3300 130 ST_CLERK 2800 131 ST_CLERK 2500 132 ST_CLERK 2100 133 ST_CLERK 3300 134 ST_CLERK 2900 135 ST_CLERK 2400 136 ST_CLERK 2200 137 ST_CLERK 3600 138 ST_CLERK 3200 EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 139 ST_CLERK 2700 140 ST_CLERK 2500 141 ST_CLERK 3500 142 ST_CLERK 3100 143 ST_CLERK 2600 144 ST_CLERK 2500 145 SA_MAN 14000 146 SA_MAN 13500 147 SA_MAN 12000 148 SA_MAN 11000 149 SA_MAN 10500 EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 150 SA_REP 10000 151 SA_REP 9500 152 SA_REP 9000 153 SA_REP 8000 154 SA_REP 7500 155 SA_REP 7000 156 SA_REP 10000 157 SA_REP 9500 158 SA_REP 9000 159 SA_REP 8000 160 SA_REP 7500 EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 161 SA_REP 7000 162 SA_REP 10500 163 SA_REP 9500 164 SA_REP 7200 165 SA_REP 6800 166 SA_REP 6400 167 SA_REP 6200 168 SA_REP 11500 169 SA_REP 10000 170 SA_REP 9600 171 SA_REP 7400 EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 172 SA_REP 7300 173 SA_REP 6100 174 SA_REP 11000 175 SA_REP 8800 176 SA_MAN 0 176 SA_REP 0 176 SA_REP 8600 177 SA_REP 8400 178 SA_REP 7000 179 SA_REP 6200 180 SH_CLERK 3200 EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 181 SH_CLERK 3100 182 SH_CLERK 2500 183 SH_CLERK 2800 184 SH_CLERK 4200 185 SH_CLERK 4100 186 SH_CLERK 3400 187 SH_CLERK 3000 188 SH_CLERK 3800 189 SH_CLERK 3600 190 SH_CLERK 2900 191 SH_CLERK 2500 EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 192 SH_CLERK 4000 193 SH_CLERK 3900 194 SH_CLERK 3200 195 SH_CLERK 2800 196 SH_CLERK 3100 197 SH_CLERK 3000 198 SH_CLERK 2600 199 SH_CLERK 2600 200 AC_ACCOUNT 0 200 AD_ASST 0 200 AD_ASST 4400 EMPLOYEE_ID JOB_ID SALARY ----------- ---------- ---------- 201 MK_MAN 13000 201 MK_REP 0 202 MK_REP 6000 203 HR_REP 6500 204 PR_REP 10000 205 AC_MGR 12008 206 AC_ACCOUNT 8300 117 rows selected.
Using the ORDER BY Clause in Set Operations
- The ORDER BY clause can appear only once at the end of the compound query.
- Component queries cannot have individual ORDER BY clauses.
- ORDER BY clause recognizes only the columns of the first SELECT query.
- By default,the first column of the first SELECT query is used to sort the output in an ascending order.
set_opt_sort_01
SELECT location_id,department_name "Dep",TO_CHAR(NULL) "Location" FROM departments ORDER BY location_id UNION SELECT location_id,TO_CHAR(NULL) "Dep",state_province "Location" FROM locations ORDER BY state_province; #execute results: #SQL> @/RealData/oracle/scripts/set_opt_sort_error.sql #UNION #* #ERROR at line 2: #ORA-00933: SQL command not properly ended ~
set_opt_sort_02
SELECT location_id,department_name "Dep",TO_CHAR(NULL) "Location" FROM departments UNION SELECT location_id,TO_CHAR(NULL) "Dep",state_province "Location" FROM locations ORDER BY state_province; #executes results #SQL> @/RealData/oracle/scripts/set_opt_sort_right.sql #SELECT location_id,TO_CHAR(NULL) "Dep",state_province "Location" FROM locations ORDER BY state_province * #ERROR at line 3: #ORA-00904: "STATE_PROVINCE": invalid identifier #说明ORDER BY 的字段必须是以第一个查询的字段进行选择，而不能选择其他的查询List.
set_opt_sort_03
SELECT location_id,department_name "Dep",TO_CHAR(NULL) "Location" FROM departments UNION SELECT location_id,TO_CHAR(NULL) "Dep",state_province "Location" FROM locations ORDER BY 2;
Summary
In this lesson,you should have learned how to use:
- UNION to return all distinct rows
- UNION ALL return all rows,including duplicates.
- INTERSECT to return all rows that are shared by both queries
- MINUS to return all distinct rows that are selected by the first query,bu not by the second.
- ORDER BY only at the very end of the statement.