使用union關(guān)鍵字時(shí)化撕,可以給出多條select 語句倘感,并將它們的結(jié)果合成單個(gè)結(jié)果集研叫。合并時(shí)兩個(gè)表對應(yīng)的列數(shù)和數(shù)據(jù)類型必須相同玫荣,每個(gè)select 語句之間使用union或union all 關(guān)鍵字分隔,union 執(zhí)行的時(shí)候刪除重復(fù)的記錄贾富,所有返回的行都是唯一歉眷,使用union all 關(guān)鍵字的作用是不刪除重復(fù)行也不對結(jié)果進(jìn)行自動排序。
例如:
mysql> select s_id,f_name from fruits where s_id=101 union select s_id,f_name from fruits where f_price<10;
+------+------------+
| s_id | f_name |
+------+------------+
| 101 | apple |
| 101 | blackberry |
| 101 | cherry |
| 103 | apricot |
| 106 | ahfjwj |
| 103 | cococut |
| 102 | grape |
+------+------------+
7 rows in set (0.00 sec)
mysql> select s_id,f_name from fruits where s_id=101 union all select s_id,f_name from fruits where f_price<10;
+------+------------+
| s_id | f_name |
+------+------------+
| 101 | apple |
| 101 | blackberry |
| 101 | cherry |
| 101 | apple |
| 103 | apricot |
| 106 | ahfjwj |
| 101 | cherry |
| 103 | cococut |
| 102 | grape |
+------+------------+
9 rows in set (0.00 sec)
每個(gè)select集是這樣的
mysql> select s_id,f_name from fruits where s_id=101;
+------+------------+
| s_id | f_name |
+------+------------+
| 101 | apple |
| 101 | blackberry |
| 101 | cherry |
+------+------------+
3 rows in set (0.00 sec)
mysql> select s_id,f_name from fruits where f_price<10
-> ;
+------+---------+
| s_id | f_name |
+------+---------+
| 101 | apple |
| 103 | apricot |
| 106 | ahfjwj |
| 101 | cherry |
| 103 | cococut |
| 102 | grape |
+------+---------+
6 rows in set (0.00 sec)
很明顯 union all 沒有去重颤枪,查詢的結(jié)果還有重復(fù)的行,