SQL> SELECT EMP_ID, SALARY, PAY_RATE
2 FROM EMPLOYEE_PAY_TBL
3 WHERE SALARY IS NOT NULL
4 OR PAY_RATE IS NOT NULL;
EMP_ID SALARY PAY_RATE
--------- ---------- --------
311549902 40000.00
442346889 14.75
213764555 30000.00
313782439 20000.00
220984332 11.00
443679012 15.00
6 rows selected
--使用UNION
SQL> SELECT EMP_ID, SALARY
2 FROM EMPLOYEE_PAY_TBL
3 WHERE SALARY IS NOT NULL
4 UNION
5 SELECT EMP_ID, PAY_RATE
6 FROM EMPLOYEE_PAY_TBL
7 WHERE PAY_RATE IS NOT NULL;
EMP_ID SALARY
--------- ----------
213764555 30000
220984332 11
311549902 40000
313782439 20000
442346889 14.75
443679012 15
6 rows selected
--UNION 操作符可以组合两个或多个SELECT语句的结构，不包含重复的记录。
SQL> SELECT EMP_ID FROM EMPLOYEE_TBL UNION SELECT EMP_ID FROM EMPLOYEE_PAY_TBL;
EMP_ID
---------
213764555
220984332
311549902
313782439
442346889
443679012
6 rows selected
--EMP_ID在两个表里都存在，但在结果里只出现一次。
--UNION ALL可以组合两个SLEECT 语句的结果，并且包含重复的记录。
SQL> CREATE TABLE PRODUCTS_TMP AS SELECT * FROM PRODUCTS_TBL;
Table created
SQL> SELECT PROD_DESC FROM PRODUCTS_TBL
2 UNION
3 SELECT PROD_DESC FROM PRODUCTS_TMP;
PROD_DESC
----------------------------------------
ASSORTED COSTUMES
ASSORTED MASKS
CANDY CORN
FALSE PARAFFIN TEETH
KEY CHAIN
LIGHTED LANTERNS
OAK BOOKSHELF
PLASTIC PUMPKIN 18 INCH
PLASTIC SPIDERS
PUMPKIN CANDY
WITCH COSTUME
11 rows selected
--下面这个SQL语句返回全部雇员的ID，并且包含重复的记录。
SQL> SELECT EMP_ID
2 FROM EMPLOYEE_TBL
3 UNION ALL
4 SELECT EMP_ID
5 FROM EMPLOYEE_PAY_TBL;
EMP_ID
---------
213764555
220984332
311549902
313782439
442346889
443679012
213764555
220984332
311549902
313782439
442346889
443679012
12 rows selected
SQL> SELECT PROD_DESC
2 FROM PRODUCTS_TBL
3 UNION ALL
4 SELECT PROD_DESC
5 FROM PRODUCTS_TMP;
PROD_DESC
----------------------------------------
KEY CHAIN
OAK BOOKSHELF
WITCH COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
LIGHTED LANTERNS
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF
WITCH COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
LIGHTED LANTERNS
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
22 rows selected
--UNION ALL会返回重复的记录，这个查询返回22条记录(11+11).
--INTERSECT组合两个查询的结果，并且返回两个查询语句中一样的记录。
SQL> SELECT PROD_DESC
2 FROM PRODUCTS_TBL
3 INTERSECT
4 SELECT PROD_DESC
5 FROM PRODUCTS_TMP;
PROD_DESC
----------------------------------------
ASSORTED COSTUMES
ASSORTED MASKS
CANDY CORN
FALSE PARAFFIN TEETH
KEY CHAIN
LIGHTED LANTERNS
OAK BOOKSHELF
PLASTIC PUMPKIN 18 INCH
PLASTIC SPIDERS
PUMPKIN CANDY
WITCH COSTUME
11 rows selected
--只返回11条记录，因为连个查询之间只有11条记录是一样的。
SQL> SELECT PROD_DESC
2 FROM PRODUCTS_TBL
3 MINUS
4 SELECT PROD_DESC
5 FROM PRODUCTS_TMP;
PROD_DESC
----------------------------------------
PLASTIC PUMPKIN 18 INCH
PLASTIC SPIDERS
PUMPKIN CANDY
--组合查询里使用ORDER BY
--ORDER BY子句可以用于组合查询，但只能用于对全部查询结果的排序，只能有一个ORDER BY子句，用数字或别名来引用字段。
SQL> SELECT EMP_ID
2 FROM EMPLOYEE_TBL
3 UNION
4 SELECT EMP_ID
5 FROM EMPLOYEE_PAY_TBL
6 ORDER BY 1;
EMP_ID
---------
213764555
220984332
311549902
313782439
442346889
443679012
6 rows selected
--组合查询里使用GROUP BY
SQL> SELECT 'CUSTOMERS' TYPE, COUNT(*)
2 FROM CUSTOMER_TBL
3 UNION
4 SELECT 'EMPLOYEES' TYPE, COUNT(*)
5 FROM EMPLOYEE_TBL
6 UNION
7 SELECT 'PRODUCTS' TYPE, COUNT(*)
8 FROM PRODUCTS_TBL
9 GROUP BY 1;
TYPE COUNT(*)
--------- ----------
CUSTOMERS 15
EMPLOYEES 6
PRODUCTS 11
--加上ORDER BY子句
SQL> SELECT 'CUSTOMERS' TYPE, COUNT(*)
2 FROM CUSTOMER_TBL
3 UNION
4 SELECT 'EMPLOYEES' TYPE, COUNT(*)
5 FROM EMPLOYEE_TBL
6 UNION
7 SELECT 'PRODUCTS' TYPE, COUNT(*)
8 FROM PRODUCTS_TBL
9 GROUP BY 1
10 ORDER BY 2;
TYPE COUNT(*)
--------- ----------
EMPLOYEES 6
PRODUCTS 11
CUSTOMERS 15
