union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序(重复部分算一次);
union all:对两个结果集进行并集操作,包括所有重复行,不进行排序(重复部分有几个算几个,即冗余比较多,效率低);
intersect:对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序(交集);
minus:对两个结果集进行差操作,不包括重复行,同时进行默认规则排序;
集合操作有并、交、差3种运算。
排序是对结果集的排序,永远在最后一行
CREATE TABLE dept1(
deptno VARCHAR(20),
dname VARCHAR(20),
LOG VARCHAR(50));
INSERT INTO dept1 VALUES(10,'ACCOUNTING','NEW');
INSERT INTO dept1 VALUES(80,'ABC','BJ');
COMMIT;
CREATE TABLE dept(
deptno VARCHAR(20),
dname VARCHAR(20),
LOG VARCHAR(50));
INSERT INTO dept VALUES(10,'ACCOUNTING','NEW');
INSERT INTO dept VALUES(20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES(30,'SALES','CHICAGO');
INSERT INTO dept VALUES(40,'OPERATIONS','BOSTON');
COMMIT;
SELECT * FROM dept1;
SELECT * FROM dept;
SELECT * FROM dept1
UNION
SELECT * FROM dept;
SELECT * FROM dept1
UNION ALL
SELECT * FROM dept;
SELECT * FROM dept
INTERSECT
SELECT * FROM dept1;
SELECT * FROM dept
MINUS
SELECT * FROM dept1;
SELECT * FROM dept1
MINUS
SELECT * FROM dept;
按字段排序的时候要把字段列出来才可以,否则会报错
集合运算中的几点注意事项:
1.集合运算中的列名不必相同,但要类型匹配且顺序要对应,大类型对上就行,如char对varchar2,date对timestamp都可以,字段数要等同,不等需要补全;
CREATE TABLE a(
id_a INT,
name_a CHAR(10));
CREATE TABLE b(
id_b INT,
name_b CHAR(10),sale NUMBER(10,2));
INSERT INTO a VALUES(1,'sohu');
INSERT INTO a VALUES(2,'sina');
INSERT INTO b VALUES(1,'sohu',1000);
INSERT INTO b VALUES(2,'yahooo',2000);
COMMIT;
SELECT * FROM a;
SELECT * FROM b;
2.显式order by是参照第一个select语句的列元素。所以,order by后的列名只能是第一个select使用的列名,别名、列号(OCP考点)如果是补全的null值需要order by,则需要使用别名。
SELECT id_a,name_a NAME,to_number(NULL) FROM a
UNION
SELECT id_b,name_b name,sale FROM b;
表b没有sale列,两个表要union字段数要一致,所以给个空列,但是b表的sale字段的类型是number,所以给a表的空字段列也转换了一下,不转换也可以。