查詢集合操作union與union all

查詢的集合操作：其中包括4種：union,union all,intersect與minus.
其中union是並集去重，union all是並集不去重，intersect是交集，
minus表示差集。

---建立測試表：

suxing@PROD>create table students(

  2  stu_id number(4),

  3  stu_name varchar2(10),

  4  credit number(2));

Table created.

 

suxing@PROD>create table courses(

  2  cou_id number(4),

  3  cou_name varchar2(10),

  4  credit number(2));

Table created.

 

---插入測試資料：

--往表students中插入測試資料：

suxing@PROD>insert into students values(1121,'susu',3);

1 row created.

suxing@PROD>insert into students values(1122,'sufi',3);

1 row created.

suxing@PROD>insert into students values(1131,'sike',2);

1 row created.
suxing@PROD>insert into students values(1131,'sike',3);

suxing@PROD>commit;

Commit complete.

 

--檢視students中的記錄：

suxing@PROD>select * from students;

    STU_ID STU_NAME       CREDIT

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

      1121 susu                3

      1122 sufi                3

      1131 sike                2

      1131 sike                2

#共有4條記錄：

 

--往表courses中插入4條測試記錄：

suxing@PROD>insert into courses values(2212,'china',4);

1 row created.

suxing@PROD>insert into courses values(2213,'english',3);

1 row created.

suxing@PROD>insert into courses values(2214,'computer',5);

1 row created.

suxing@PROD>insert into courses values(2215,'C language',4);

1 row created.

suxing@PROD>commit;

Commit complete.

 

--檢視錶courses中的記錄：

suxing@PROD>select * from courses;

    COU_ID COU_NAME       CREDIT

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

      2212 china               4

      2213 english             3

      2214 computer            5

      2215 C language          4

#共返回4條記錄。

--往表courses中插入4條測試記錄：

suxing@PROD>insert into courses values(2212,'china',4);

1 row created.

suxing@PROD>insert into courses values(2213,'english',3);

1 row created.

suxing@PROD>insert into courses values(2214,'computer',5);

1 row created.

suxing@PROD>insert into courses values(2215,'C language',4);

1 row created.

suxing@PROD>commit;

Commit complete.

 

--檢視錶courses中的記錄：

suxing@PROD>select * from courses;

    COU_ID COU_NAME       CREDIT

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

      2212 china               4

      2213 english             3

      2214 computer            5

      2215 C language          4

#共返回4條記錄。

---進行union all集合查詢操作（並集不去重）：

suxing@PROD>select * from students

  2  union all

  3  select * from courses;

    STU_ID STU_NAME       CREDIT

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

      1121 susu                3

      1122 sufi                3

      1131 sike                2

      1131 sike                2

      2212 china               4

      2213 english             3

      2214 computer            5

      2215 C language          4

8 rows selected.

---再次進行union集合查詢（並集去重）：

suxing@PROD>select * from students

  2  union

  3  select * from courses;

    STU_ID STU_NAME       CREDIT

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

      1121 susu                3

      1122 sufi                3

      1131 sike                2

      2212 china               4

      2213 english             3

      2214 computer            5

      2215 C language          4

7 rows selected.

#返回共7條記錄。

#返回共7條記錄，比以上的union all集合查詢返回的結果多了一條記錄。

因為進行union集合操作過程中把集合查詢結果中的重複記錄去除了。