SQL UNION子句用于合并两个或多个SELECT语句的输出,而不返回任何重复的行。
UNION 子句的基本语法如下:
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
请考虑以下两个表。
表1 -客户表(CUSTOMERS)如下。
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Learnfk | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
表2 -订单表(ORDERS)如下。
+-----+---------------------+-------------+--------+ |OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2019-10-08 00:00:00 | 3 | 3000 | | 100 | 2019-10-08 00:00:00 | 3 | 1500 | | 101 | 2019-11-20 00:00:00 | 2 | 1560 | | 103 | 2018-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+
现在,让无涯教程按如下所示将这两个表连接到无涯教程的SELECT语句中:
SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID UNION SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
这将产生以下输出-
+------+----------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +------+----------+--------+---------------------+ | 1 | Ramesh | NULL | NULL | | 2 | Khilan | 1560 | 2019-11-20 00:00:00 | | 3 | kaushik | 3000 | 2019-10-08 00:00:00 | | 3 | kaushik | 1500 | 2019-10-08 00:00:00 | | 4 | Chaitali | 2060 | 2018-05-20 00:00:00 | | 5 | Hardik | NULL | NULL | | 6 | Komal | NULL | NULL | | 7 | Learnfk | NULL | NULL | +------+----------+--------+---------------------+
UNION ALL运算符用于合并两个SELECT语句的输出,包括重复的行,适于UNION子句的相同规则将适用于UNION ALL运算符。
UNION ALL 的基本语法如下。
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
考虑以下两个表,
表1 -客户表(CUSTOMERS)如下。
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Learnfk | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
表2 -订单表(ORDERS)如下。
+-----+---------------------+-------------+--------+ |OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2019-10-08 00:00:00 | 3 | 3000 | | 100 | 2019-10-08 00:00:00 | 3 | 1500 | | 101 | 2019-11-20 00:00:00 | 2 | 1560 | | 103 | 2018-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+
现在,让无涯教程按如下所示将这两个表连接到无涯教程的SELECT语句中:
SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID UNION ALL SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
这将产生以下输出-
+------+----------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +------+----------+--------+---------------------+ | 1 | Ramesh | NULL | NULL | | 2 | Khilan | 1560 | 2019-11-20 00:00:00 | | 3 | kaushik | 3000 | 2019-10-08 00:00:00 | | 3 | kaushik | 1500 | 2019-10-08 00:00:00 | | 4 | Chaitali | 2060 | 2018-05-20 00:00:00 | | 5 | Hardik | NULL | NULL | | 6 | Komal | NULL | NULL | | 7 | Learnfk | NULL | NULL | | 3 | kaushik | 3000 | 2019-10-08 00:00:00 | | 3 | kaushik | 1500 | 2019-10-08 00:00:00 | | 2 | Khilan | 1560 | 2019-11-20 00:00:00 | | 4 | Chaitali | 2060 | 2018-05-20 00:00:00 | +------+----------+--------+---------------------+
还有两个其他子句,它们类似于UNION子句。
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)