union, difference, intersection of sets, bags

SQL> select * from t1;

 C1    C2
----------
  1     2
  3     4
  1     2
  1     2

SQL> select * from t2;

 C1    C2
----------
  1     2
  3     4
  3     4

t appears n times in T1 and m times T2

1. bag union T1 ∪ T2  = ( Oracle union all )
- tuple t appears n + m times

2. bag intersection T1 ∩ T2 ( Oracle not support ?)
- t appears min(n,m) times

3. bag difference T1 - T2 ( 교환법칙 성립 안됨  T1 - T2  not equal T2 - T1 ) ( Oracle not support ?)
- t appears max(0,n-m) times

4. set union T1 ∪ T2  ( Oracle union )
- tuple t appears n + m times and Duplicate Elimination  

5. set difference T1 -T2  ( Oracle minus )
 - t appears max(0,n-m) times and key counting and Elimination  

6. set intersection T1 ∩ T2 ( Oracle intersect )
- t appears min(n,m) times and Duplicate Elimination


Oracle 수행결과

SQL> select * from t1 union all select * from t2;

 C1    C2
---------- ----------
  1     2
  3     4
  1     2
  1     2
  1     2
  3     4
  3     4

SQL> select * from t1 union select * from t2;

 C1    C2
---------- ----------
  1     2
  3     4

SQL> select * from t1 intersect select * from t2;

 C1    C2
---------- ----------
  1     2
  3     4

select * from t2 intersect select * from t1;

 C1    C2
---------- ----------
  1     2
  3     4

select * from t2 minus select * from t1;

no rows selected

select * from t1 minus select * from t2;

no rows selected