将多个查询用 SET 操作符连接组成一个新的查询
UNION/UNION ALL(交集)
INTERSECT(并集)
MINUS (差集)
--UNION/UNION ALL
select employee_id,department_id from employees01 union select employee_id,department_id from employees02;
select employee_id,department_id from employees01 union all select employee_id,department_id from employees02;
--起别名(union前面语句起好别名,后面可以不用起)
select employee_id emp_id,department_id dept_id from employees01 union select employee_id,department_id from employees02;
--排序
select employee_id emp_id,department_id dept_id from employees01 union select employee_id,department_id from employees02 order by emp_id;
select employee_id emp_id,department_id dept_id from employees01 union select employee_id,department_id from employees02 order by emp_id desc;
--INTERSECT
select employee_id emp_id,department_id dept_id from employees01 intersect select employee_id,department_id from employees02 order by emp_id desc;
--MINUS
select employee_id emp_id,department_id dept_id from employees01 minus select employee_id,department_id from employees02 order by emp_id desc;
注意:
在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
括号可以改变执行的顺序
ORDER BY 子句:
只能在语句的最后出现
可以使用第一个查询中的列名, 别名或相对位置
匹配各SELECT 语句
select employee_id,department_id,to_char(null) from employees01 union select to_number(null),department_id,department_name from departments;
使用相对位置排序
column a_dummy noprint;
select 'study at' as "My Dream",2 a_dummy from dual union select 'I want to',1 from dual union select 'atguigu.com',3 from dual order by 2 asc;