数据库操作手册—组合查询

  • 组合查询 

组合以下两个查询语句

mysql> SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI');
+-------------+--------------+---------------------+
| cust_name   | cust_contact | cust_email          |
+-------------+--------------+---------------------+
| Coyote Inc. | Y Lee        | ylee@coyote.com     |
| Wascals     | Jim Jones    | rabbit@wascally.com |
| E Fudd      | E Fudd       | NULL                |
+-------------+--------------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_name='Wascals';
+-----------+--------------+---------------------+
| cust_name | cust_contact | cust_email          |
+-----------+--------------+---------------------+
| Wascals   | Jim Jones    | rabbit@wascally.com |
+-----------+--------------+---------------------+
1 row in set (0.00 sec)

使用UNION联合语句:

mysql> SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_state IN ('IN','IL','MI') UNION SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name='Wascals';
+-------------+--------------+---------------------+
| cust_name   | cust_contact | cust_email          |
+-------------+--------------+---------------------+
| Coyote Inc. | Y Lee        | ylee@coyote.com     |
| Wascals     | Jim Jones    | rabbit@wascally.com |
| E Fudd      | E Fudd       | NULL                |
+-------------+--------------+---------------------+
3 rows in set (0.00 sec)

mysql>

使用UNION时也需要注意:

  • UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键 字UNION分隔(因此,如果组合四条SELECT语句,将要使用三个UNION 关键字)。
  • UNION 中的每个查询必须包含相同的列、表达式或聚集函数(不过, 各个列不需要以相同的次序列出)。
  • 列数据类型必须兼容:类型不必完全相同,但必须是 DBMS可以隐含 转换的类型(例如,不同的数值类型或不同的日期类型)。 

但是在使用UNION时,重复的行会被取消,如果不想取消,可以使用 UNION ALL 而不是 UNION。

mysql> SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_state IN ('IN','IL','MI') UNION ALL SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name='Wascals';
+-------------+--------------+---------------------+
| cust_name   | cust_contact | cust_email          |
+-------------+--------------+---------------------+
| Coyote Inc. | Y Lee        | ylee@coyote.com     |
| Wascals     | Jim Jones    | rabbit@wascally.com |
| E Fudd      | E Fudd       | NULL                |
| Wascals     | Jim Jones    | rabbit@wascally.com |
+-------------+--------------+---------------------+
4 rows in set (0.00 sec)

对组合查询结果排序:

这条UNION在最后一条SELECT语句后使用了ORDER BY子句。虽然ORDER BY 子句似乎只是最后一条 SELECT 语句的组成部分,但实际上 DBMS将 用它来排序所有 SELECT 语句返回的所有结果。 

mysql> SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_state IN ('IN','IL','MI') UNION ALL SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name='Wascals' ORDER BY cust_name, cust_contact;
+-------------+--------------+---------------------+
| cust_name   | cust_contact | cust_email          |
+-------------+--------------+---------------------+
| Coyote Inc. | Y Lee        | ylee@coyote.com     |
| E Fudd      | E Fudd       | NULL                |
| Wascals     | Jim Jones    | rabbit@wascally.com |
| Wascals     | Jim Jones    | rabbit@wascally.com |
+-------------+--------------+---------------------+
4 rows in set (0.00 sec)

注:

某些 DBMS还支持另外两种 UNION:EXCEPT(有时称为 MINUS)可用 来检索只在第一个表中存在而在第二个表中不存在的行;而 INTERSECT 可用来检索两个表中都存在的行。实际上,这些 UNION 很 少使用,因为相同的结果可利用联结得到。 
原文地址:https://www.cnblogs.com/yeshengCqupt/p/13498824.html