MySQL 中的 UNION 语句
作者:mmseoamin日期:2023-12-25

文章目录

    • 一、数据准备
    • 一、UNION 和 UNION ALL
    • 二、UNION 的执行顺序(UNION 和其他语句一同出现)
    • 三、MySQL 使用 UNION(ALL) + ORDER 导致排序失效
    • 四、UNION 报错语法

      一、数据准备

      -- 创建表
      CREATE TABLE test_user (
       ID int(11) NOT NULL AUTO_INCREMENT,
       USER_ID int(11) DEFAULT NULL COMMENT '用户账号',
       USER_NAME varchar(255) DEFAULT NULL COMMENT '用户名',
       AGE int(5) DEFAULT NULL COMMENT '年龄',
       COMMENT varchar(255) DEFAULT NULL COMMENT '简介',
       PRIMARY KEY (ID)
      ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
       
      -- 数据插入语句
      INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('1', '111', '开心菜鸟', '18', '今天很开心');
      INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('2', '222', '悲伤菜鸟', '21', '今天很悲伤');
      INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('3', '333', '认真菜鸟', '30', '今天很认真');
      INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('4', '444', '高兴菜鸟', '18', '今天很高兴');
      INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('5', '555', '严肃菜鸟', '21', '今天很严肃');
      
      SELECT * FROM test_user u;
      

      在这里插入图片描述

      一、UNION 和 UNION ALL

      UNION

      连接数据集关键字,可以将两个查询结果集拼接为一个,会过滤掉相同的记录

      UNION ALL

      连接数据集关键字,可以将两个查询结果集拼接为一个,不会过滤掉相同的记录

      -- 使用UNION
      SELECT * FROM test_user u
      UNION
      SELECT * FROM test_user u;
      

      在这里插入图片描述

      使用 UNION ,可以看到查询结果只有 5 条数据。

      -- 使用UNION ALL
      SELECT * FROM test_user u
      UNION ALL
      SELECT * FROM test_user u;
      

      在这里插入图片描述

      使用 UNION ALL,可以看到查询结果有 10 条数据。

      二、UNION 的执行顺序(UNION 和其他语句一同出现)

      from—>on—>join—>where—>group by—>having+(聚合函数)—>select—>distinct—>UNION—>order by—>limit

      UNION 的执行顺序在 ORDER BY 之前

      请记住这个执行顺序,便可以知道 UNION 和其他语句一同出现的结果。

      1. UNION 和 WHERE 语句
      -- 1、第二个子句中的 where 语句不能同时作用于两个select语句
      -- 5 + 1,共计 6 条
      SELECT *, 'table1' FROM test_user u 
      UNION ALL
      SELECT *, 'table2' FROM test_user u WHERE AGE = 30;
      

      UNION 在 where 之后,所以第二个表的WHERE先筛选后进行数据集拼接;

      在这里插入图片描述

      如果想要把 where 作用所有结果集,可以通过再嵌套一个 select 。

      -- 1、第二个子句中的 where 语句不能同时作用于两个select语句
      -- 1 + 1,共计 2 条
      -- 写法 1
      SELECT * FROM 
      (
      	SELECT *, 'table1' FROM test_user u 
      	UNION ALL
      	SELECT *, 'table2' FROM test_user u
      ) a 
      WHERE AGE = 30;
      -- 或者使用写法 2
      SELECT *, 'table1' FROM test_user u WHERE AGE = 30
      UNION ALL
      SELECT *, 'table2' FROM test_user u WHERE AGE = 30
      ;
      

      在这里插入图片描述

      1. UNION 和 GROUP 语句
      -- 2、第二个子句中的 group by 语句不能同时作用于两个select语句
      -- 5 + 3,共计 8 条
      SELECT *, 'table1' FROM test_user u 
      UNION ALL
      SELECT *, 'table2' FROM test_user u GROUP BY AGE;
      

      UNION 在 GROUP BY 之后,所以 table2 的 GROUP BY 先分组后进行数据集拼接;

      在这里插入图片描述

      2. UNION 和 HAVING 语句

      -- 3、第二个子句中的 HAVING 语句不能同时作用于两个 select 语句
      -- 5 + 1,共计 6 条
      SELECT *, 'table1' FROM test_user u 
      UNION ALL
      SELECT *, 'table2' FROM test_user u HAVING AGE = 30 ;
      

      UNION 在 HAVING 之后,所以 table2 的 HAVING 先过滤后进行数据集拼接;

      在这里插入图片描述

      3. UNION 和 ORDER BY 语句

      -- 4、第二个子句中的 order by 语句可以同时作用于两个select语句 
      -- 查询结果整体按照 age 进行了排序
      SELECT *, 'table1' FROM test_user u 
      UNION ALL
      SELECT *, 'table2' FROM test_user u ORDER BY AGE;
      

      因为当 UNION(ALL)语句和 ORDER BY语句同时出现,UNION(ALL)语句先执行。

      在这里插入图片描述

      4. UNION 和 LIMIT 语句

      -- 只有1条数据,因为LIMIT在UNION之后执行
      SELECT *, 'table1' FROM test_user u 
      UNION ALL
      SELECT *, 'table2' FROM test_user u limit 0,1;
      

      只有1条数据,因为 LIMIT 在 UNION 之后执行。

      在这里插入图片描述

      1. UNION 、 ORDER BY 和 LIMIT 语句
      -- 5、第二个子句中的 order by ,LIMIT 语句同时作用于两个 select 语句 *********
      -- 只有1条数据,age=30 UNION--->ORDER BY--->LIMIT
      SELECT *, 'table1' FROM test_user u 
      UNION ALL
      SELECT *, 'table2' FROM test_user u order by age desc limit 0,1;
      

      先拼接数据集,在按照 age 排序,最后使用 LIMIT 。

      在这里插入图片描述

      三、MySQL 使用 UNION(ALL) + ORDER 导致排序失效

      通过以下两种方式解决:

      • 添加 LIMIT 字段
      • 额外增加排序字段
        1. SQL 1 如下
        SELECT * FROM test_user u ORDER BY AGE;
        

        在这里插入图片描述

        2. SQL 2 如下

        SELECT * FROM test_user u ORDER BY AGE DESC;
        

        在这里插入图片描述

        3. 查询结果集

        (SELECT *, 'table1' FROM test_user u ORDER BY AGE) 
        UNION ALL 
        (SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC);
        

        可以看到此时 ORDER BY 语句失效了。

        在这里插入图片描述

        原因:UNION(ALL) + 会使 ORDER 失效

        1. 解决办法(1): 添加 LIMIT
        -- 都加上 LIMIT
        ( SELECT *, 'table1' FROM test_user u ORDER BY AGE limit 10) 
        UNION ALL 
        ( SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC limit 10) 
        

        在这里插入图片描述

        最好的解决方案就是先查询后排序,避免上述情况发生。

        1. 解决办法(2) :添加额外的排序字段
        select * from 
        (
        	( SELECT *, 'table1' AS name, row_number() over(ORDER BY AGE ) AS rn FROM test_user u ) 
        	UNION ALL 
        	( SELECT *, 'table2' AS name, row_number() over(ORDER BY AGE DESC) AS rn FROM test_user u ) 
        ) a
        order by name, rn;
        

        额外需要两个字段,通过 row_number() over(order by column)进行表内排序,再通过 name 字段进行表排序。

        在这里插入图片描述

        四、UNION 报错语法

        1. ORDER BY 语法报错

        -- 语法错误
        SELECT *, 'table1' FROM test_user u ORDER BY AGE
        UNION ALL
        SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC;
        

        第一个 SELECT 语句也使用了 ORDER BY ,导致报错。

        在这里插入图片描述

        解决方案:第一个 SELECT 语句加上括号。

        -- 语法正确
        (SELECT *, 'table1' FROM test_user u ORDER BY AGE)
        UNION ALL
        SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC;
        

        加上括号后,虽然不再报错,但是第一个 SELECT 语句的排序失效。

        在这里插入图片描述

        那要是上下两个都加上括号呢?

        -- 语法正确
        (SELECT *, 'table1' FROM test_user u ORDER BY AGE)
        UNION ALL
        (SELECT *, 'table2' FROM test_user u ORDER BY AGE DESC);
        

        语法不报错,但是两个排序都失效了。

        原因大家也清楚,前面第三小节已经讲过了,UNION 在 ORDER BY 语句之前。

        在这里插入图片描述

        2. LIMIT 语法报错

        同样对于 LIMIT 语句也是一样的。

        -- 语法错误
        SELECT *, 'table1' FROM test_user u limit 0,1
        UNION ALL
        SELECT *, 'table2' FROM test_user u limit 0,1;
        

        在这里插入图片描述

        解决方案:同样也是第一个 SELECT 语句加上括号。

        -- 语法正确,1 条记录
        (SELECT *, 'table1' FROM test_user u limit 0,1)
        UNION ALL
        SELECT *, 'table2' FROM test_user u limit 0,1;
        

        此时语法正确,但只返回一行记录。

        在这里插入图片描述

        如果想要返回两条记录,就给第二个 SELECT 语句也加上括号。

        -- 语法正确,2 条记录
        (SELECT *, 'table1' FROM test_user u limit 0,1)
        UNION ALL
        (SELECT *, 'table2' FROM test_user u limit 0,1);
        

        在这里插入图片描述

        总结: UNION 后面执行的 ORDER BY,LIMIT 语句注意使用时要加括号,否则报错。