oracle 查询并集,查询集合操作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集合操作过程中把集合查询结果中的重复记录去除了。