重庆思庄Oracle、Redhat认证学习论坛

标题: 并、交、差示例 [打印本页]

作者: 5dm 时间: 2012-11-1 13:49
标题: 并、交、差示例

并

union

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

union all

SQL> select employee_id,job_id from employees
2 union all
3 select employee_id,job_id from job_history;

EMPLOYEE_ID JOB_ID
----------- ----------
        206 AC_ACCOUNT
        205 AC_MGR
        200 AD_ASST
        100 AD_PRES
        101 AD_VP
        102 AD_VP
        109 FI_ACCOUNT
        110 FI_ACCOUNT
        111 FI_ACCOUNT
        112 FI_ACCOUNT
        113 FI_ACCOUNT

EMPLOYEE_ID JOB_ID
----------- ----------
108 FI_MGR
203 HR_REP

交(intersection)

intersect

SQL> select employee_id,job_id from employees
2 intersect
3 select employee_id,job_id from job_history;

EMPLOYEE_ID JOB_ID
----------- ----------
176 SA_REP
200 AD_ASST

差

minus

SQL> select employee_id from employees
2 minus
3 select employee_id from job_history;

EMPLOYEE_ID
-----------
        100
        103
        104
        105
        106
        107
        108
        109
        110
        111
        112

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)