SET运算符

-- SET操作符：

-- union 联合去重两个表的字段个数和类型都得一样，起别名应该在第一个表上操作，，排序按照第一列从小到大来排的

select employee_id, department_id

from employee01

union --union all

select employee_id, department_id

from employee02;

minus ：取差集

-- 使两表字段个数和类型一致

select employee_id emp_id,dapartment_id,to_char(null)

from employees01

union

select to_number(null),department_id,department_name

from departments;

--查询10,50,20部门的job_id,department_id，且department_id 按10,50,20的顺序来排列

column a_dummy noprint;

select job_id,department_id,1 a_dummy

from employees

where  department_id = 10

union

select job_id,department_id,2

from employees

where department_id = 50

union

select job_id,department_id,3

from employees

where department_id = 20

order by 3 asc;

JOB_ID     DEPARTMENT_ID

---------- -------------

AD_ASST               10

SH_CLERK              50

ST_CLERK              50

ST_MAN                50

MK_MAN                20

MK_REP                20

--查询所有员工的last_name,department_id和department_name

select last_name,department_id,to_char(null)

from employees

union

select to_char(null), department_id,department_name

from departments;

巴特西

SET运算符

最新文章

热门文章