SQL UNION 操作符 #
SQL UNION 操作符合并两个或多个 SELECT 语句的结果。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。它可以从多个表中选择数据，并将结果集组合成一个结果集。使用 UNION 时，每个 SELECT 语句必须具有相同数量的列，且对应列的数据类型必须相似。
SQL UNION 语法 #
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
UNION 操作符默认会去除重复的记录，如果需要保留所有重复记录，可以使用 UNION ALL 操作符。
SQL UNION ALL 语法 #
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
演示数据库
MariaDB [dn]> select * from isps;
+-----+------------+--------------------------------------------------+
| idx | name | intro |
+-----+------------+--------------------------------------------------+
| 1 | West | 西部数码老牌互联网基础服务提供商 |
| 2 | Nameliso | Namesilo是一家美国的域名注册商 |
| 3 | eName | 易名科技为国内知名的域名服务机构 |
| 4 | CloudFlare | Cloudflare 是一个全球性的云平台 |
+-----+------------+--------------------------------------------------+
MariaDB [dn]> select * from dnl;
+-----+--------------------+------------+------------+------------+------------------+
| idx | Name | Created | Expiration | ISP | Note |
+-----+--------------------+------------+------------+------------+------------------+
| 001 | vps.pet | 2024-08-15 | 2025-08-15 | West | NULL |
| 002 | nanazip.cn | 2024-08-04 | 2025-08-04 | West | NULL |
| 003 | hugotheme.cn | 2024-08-01 | 2025-08-01 | West | NULL |
| 004 | wuli.me | 2024-07-27 | 2025-07-27 | West | NULL |
| 005 | gitfaq.cn | 2024-07-24 | 2025-07-24 | West | NULL |
| 006 | ofdma.cn | 2024-07-22 | 2025-07-22 | West | NULL |
| 007 | dotxt.cn | 2024-07-15 | 2025-07-15 | West | NULL |
| 008 | piaqi.cn | 2024-07-11 | 2025-07-11 | West | NULL |
| 009 | p9.pub | 2024-06-23 | 2025-06-23 | West | NULL |
| 010 | dotfile.cn | 2024-05-27 | 2025-05-27 | West | NULL |
| 011 | learnsql.cn | 2022-08-04 | 2025-08-04 | West | NULL |
......
| 048 | ustv.top | 2020-10-13 | 2030-10-13 | West | NULL |
| 049 | aido.cc | 2020-09-22 | 2030-09-22 | West | NULL |
| 050 | kjzx.top | 2020-08-16 | 2030-08-16 | West | NULL |
| 051 | wxtx.top | 2020-08-11 | 2030-08-11 | West | NULL |
| 052 | smdm.top | 2018-09-10 | 2028-09-10 | West | NULL |
| 053 | vpshz.top | 2018-07-18 | 2028-07-18 | West | NULL |
| 054 | kodis.top | 2018-01-25 | 2028-01-25 | West | NULL |
| 055 | imagemagick.top | 2017-10-24 | 2027-10-24 | West | NULL |
| 056 | oppa.top | 2015-12-29 | 2027-12-29 | West | NULL |
| 057 | sjcs.top | 2017-09-05 | 2027-09-05 | West | NULL |
| 058 | swaywm.com | 2022-10-24 | 2025-10-24 | eName | NULL |
| 059 | xalug.com | 2023-06-14 | 2026-06-14 | eName | NULL |
| 060 | rustcmd.com | 2023-04-24 | 2026-04-24 | eName | NULL |
| 061 | nvgao.net | 2023-07-26 | 2026-07-26 | eName | NULL |
| 062 | 0x8.net | 2023-05-28 | 2026-05-28 | eName | NULL |
| 063 | dotbbq.com | 2017-10-28 | 2025-10-28 | eName | NULL |
| 064 | awkgrepsed.org | 2023-06-25 | 2025-06-25 | Namesilo | NULL |
| 065 | bailuyuan.org | 2017-03-23 | 2025-03-23 | Namesilo | NULL |
| 066 | hdnj.org | 2023-03-18 | 2025-03-18 | Namesilo | NULL |
| 067 | huangdineijing.org | 2024-03-16 | 2025-03-16 | Namesilo | NULL |
| 068 | huxian.org | 2023-05-04 | 2025-05-04 | Namesilo | NULL |
| 069 | les.moe | 2015-12-31 | 2025-12-31 | Namesilo | NULL |
| 070 | lishi.app | 2023-05-03 | 2025-05-03 | Namesilo | NULL |
| 071 | ltecn.com | 2014-07-26 | 2025-07-26 | Namesilo | NULL |
| 072 | pornie.in | 2016-03-10 | 2025-03-10 | Namesilo | NULL |
| 073 | pornie.top | 2016-03-10 | 2027-03-10 | Namesilo | NULL |
| 074 | raspi.in | 2014-02-27 | 2026-02-27 | Namesilo | NULL |
| 075 | regex.in | 2020-01-03 | 2026-01-03 | Namesilo | NULL |
| 076 | suopo.net | 2020-06-17 | 2025-06-17 | Namesilo | NULL |
| 077 | tld.moe | 2024-08-14 | 2025-08-14 | Namesilo | NULL |
| 078 | QUPAI.ORG | 2023-04-14 | 2025-04-14 | CloudFlare | NULL |
| 079 | ZJQ.XYZ | 2020-10-24 | 2024-10-25 | CloudFlare | NULL |
| 080 | UNIXETC.COM | 2017-03-30 | 2025-03-30 | CloudFlare | NULL |
| 081 | tld.moe | 2024-08-15 | 2025-08-15 | Regery | Top-Level Domain |
| 082 | les.moe | 2024-08-15 | 2025-08-15 | NULL | NULL |
+-----+--------------------+------------+------------+------------+------------------+
82 rows in set (0.001 sec)
SQL UNION 实例 #
下面的 SQL 语句从 “dnl” 和 “isps” 表中选取所有不同的ISP（只有不同的值）：
MariaDB [dn]> select ISP from dnl union select name from isps order by ISP;
+------------+
| ISP |
+------------+
| NULL |
| CloudFlare |
| eName |
| Nameliso |
| Namesilo |
| Regery |
| West |
+------------+
7 rows in set (0.001 sec)
注释：UNION 不能用于列出两个表中所有的ISP。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！
SQL UNION ALL 实例 #
下面的 SQL 语句使用 UNION ALL 从 “dnl” 和 “isps” 表中选取所有的ISP（包含重复值）：
MariaDB [dn]> select ISP from dnl union all select name from isps order by ISP;
+------------+
| ISP |
+------------+
| NULL |
| CloudFlare |
| CloudFlare |
| CloudFlare |
| CloudFlare |
| eName |
| eName |
| eName |
| eName |
| eName |
| eName |
| eName |
| Nameliso |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Namesilo |
| Regery |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
| West |
+------------+
86 rows in set (0.001 sec)
带有 WHERE 的 SQL UNION ALL #
下面的 SQL 语句使用 UNION ALL 从 “dnl” 和 “isps” 表中选取所有的eName的数据（包含重复值）：
MariaDB [dn]> select ISP from dnl where ISP = 'eName' union all select name from isps where name = 'eName' order by ISP;
+-------+
| ISP |
+-------+
| eName |
| eName |
| eName |
| eName |
| eName |
| eName |
| eName |
+-------+
7 rows in set (0.001 sec)