17.SET操作符
1.set operators操作符
--表达式必须在数量上匹配
--后一个语句中列的数据类型必须与前一个语句中的对应列数据类型一致
--括号可改变优先级
--order by语句可以出现在语句最后
--除union all外，重复行都会被取消
--第一个查询中的列名会出现在结果中
--除union all外，结果默认是升序排序1.union/union all
hr@ORCLPDB01 2023-02-25 16:35:37> select employee_id,job_id
2 from employees
3 union
4 select employee_id,job_id
5 from job_history;
EMPLOYEE_ID JOB_ID
----------- ----------
199 SH_CLERK
200 AC_ACCOUNT
200 AD_ASST
201 MK_MAN
201 MK_REP
hr@ORCLPDB01 2023-02-25 16:35:37> select employee_id,job_id
2 from employees
3 union all
4 select employee_id,job_id
5 from job_history;
EMPLOYEE_ID JOB_ID
----------- ----------
199 SH_CLERK
200 AD_ASST
200 AD_ASST
200 AC_ACCOUNT
201 MK_MAN注意：
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNIONUnion All：对两个结果集进行并集操作，包括重复行，不进行排序；
如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。
2.intersect
hr@ORCLPDB01 2023-02-25 17:45:50> r
1 select employee_id,job_id
2 from employees
3 intersect
4 select employee_id,job_id
5* from job_history order by 1
EMPLOYEE_ID JOB_ID
----------- ----------
176 SA_REP
200 AD_ASST
Elapsed: 00:00:00.013.minus
hr@ORCLPDB01 2023-02-25 17:48:29> r
1 select employee_id,job_id
2 from employees
3 minus
4 select employee_id,job_id
5* from job_history order by 1
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
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
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
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
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
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
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
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
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
201 MK_MAN
202 MK_REP
203 HR_REP
204 PR_REP
205 AC_MGR
206 AC_ACCOUNT
105 rows selected.使用union进行列匹配，永字符串进行列匹配
hr@ORCLPDB01 2023-02-25 17:55:08> r
1 select location_id,department_name "Deaprtment", to_char(null) "Warehouse location"
2 from departments
3 union
4 select location_id,to_char(null) "Department"
5 ,state_province
6* from locations
LOCATION_ID Deaprtment Warehouse location
----------- ------------------------------ -------------------------
1000
1100
1200 Tokyo Prefecture
1300
1400 IT
1400 Texas
1500 Shipping
1500 California
1600 New Jersey
1700 Accounting
1700 Administration
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
1700 NOC
1700 Operations
1700 Payroll
1700 Purchasing
1700 Recruiting
1700 Retail Sales
1700 Shareholder Services
1700 Treasury
1700 Washington
1800 Marketing
1800 Ontario
1900 Yukon
2000
2100 Maharashtra
2200 New South Wales
2300
2400 Human Resources
2400
2500 Sales
2500 Oxford
2600 Manchester
2700 Public Relations
2700 Bavaria
2800 Sao Paulo
2900 Geneve
3000 BE
3100 Utrecht
3200 Distrito Federal,使用union匹配数字0
hr@ORCLPDB01 2023-02-25 17:55:09> select employee_id,job_id,salary
2 from employees
3 union
4 select employee_id,job_id,0
5 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
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
