并、交、差示例

5dm

并

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