--UNION 操作符返回两个查询的结果集的并集
select *
from app_jf
where jfindcode = 'KEY2014000063'
union
select *
from app_jf
where jfindcode in ('KEY2014000064', 'KEY2014000063');
--查询结果是:
--KEY2014000063
--KEY2014000064
--UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。
select *
from app_jf
where jfindcode = 'KEY2014000063'
union all
select *
from app_jf
where jfindcode in ('KEY2014000064', 'KEY2014000063');
--查询结果是:
--KEY2014000063
--KEY2014000063
--KEY2014000064
--INTERSECT 操作符返回两个结果集的交集
select *
from app_jf
where jfindcode = 'KEY2014000063'
intersect
select *
from app_jf
where jfindcode in ('KEY2014000064', 'KEY2014000063');
--查询结果是:
--KEY2014000063
--MINUS操作符:返回两个结果集的差集
select *
from app_jf
where jfindcode in ('KEY2014000064', 'KEY2014000063')
minus
select *
from app_jf
where jfindcode = 'KEY2014000063';
--查询结果是:
--KEY2014000064
--EXISTS
SELECT employee_id, last_name, job_id, department_id
FROM employees
outer WHERE EXISTS
(SELECT 'X' FROM employees WHERE manager_id = outer.employee_id);
--NOT EXISTS
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS
(SELECT 'X' FROM employees WHERE department_id = d.department_id);
转载于:https://my.oschina.net/Cheney521/blog/395845