MYSQL中union的用法

2026/7/28 16:38:42

1.union查询就是把2条或者多条sql语句的查询结果，合并成一个结果集。

如：sql1: N行，sql2: M行，sql1 union sql2 ---> N+M行

2.union满足什么条件就可以用了?

只要结果集中的列数一致就可以.（如都是2列或者N列）

3.如果Union后的结果有重复(即某2行,或N行,所有的列,值都一样),怎么办?

这种情况是比较常见的,默认会去重.不想去重可以使用union all.

4. 取自于2张表,通过"别名"让2个结果集的列一致。那么,如果取出的结果集,列名字不一样,还能否union.

可以,而且取出的最终列名,以第1条sql为准 .

参考实例代码:

CREATE TABLE num_a ( id VARCHAR( 3 ) NOT NULL, num INT(3 ) UNSIGNED NOT NULL )CHARSET utf8 ENGINE MYISAM; CREATE TABLE num_b ( id VARCHAR( 3 ) NOT NULL, num INT(3 ) UNSIGNED NOT NULL )CHARSET utf8 ENGINE MYISAM; INSERT INTO num_a VALUES ( 'a', 5 ); INSERT INTO num_a VALUES ( 'b', 10 ); INSERT INTO num_a VALUES ( 'c', 15 ); INSERT INTO num_a VALUES ( 'd', 10 ); INSERT INTO num_b VALUES ( 'b', 5 ); INSERT INTO num_b VALUES ( 'c', 15 ); INSERT INTO num_b VALUES ( 'd', 20 ); INSERT INTO num_b VALUES ( 'e', 99 );

1，union会去掉重复的行:

SELECT id,num FROM num_a UNION SELECT id, num FROM num_b

2、UNION ALL不会过滤重复的行

SELECT id,num FROM num_a UNION ALL SELECT id, num FROM num_b

3、order by对union后的结果集排序

SELECT id,num FROM num_a UNION SELECT id, num FROM num_b ORDER BY num DESC

4、把num_a和num_b不同的索引结果保留，相同的索引结果相加然后输出：

SELECT a.id, ( a.num + b.num ) AS num FROM num_a AS a INNER JOIN num_b AS b ON a.id = b.id UNION ALL SELECT * FROM num_a AS a WHERE NOT EXISTS( SELECT * FROM num_b AS b WHERE a.id = b.id ) UNION ALL SELECT * FROM num_b AS b WHERE NOT EXISTS( SELECT * FROM num_a AS a WHERE a.id = b.id ) ORDER BY id ASC