MySQL_05union合并查询结果集

MySQL_05union合并查询结果集

1.union的作用及用法

union关键字用于合并两个或多个 SELECT 语句的查询结果集。

用法:

select ...
union
select ...

案例:查询工作岗位是CLERK和MANAGER的员工,要求显示员工姓名和工作。

1.使用or或者in来实现:

#mysql> select ename,job from emp where job in(‘CLERK‘,‘MANAGER‘);
mysql> select ename,job from emp where job = ‘CLERK‘ or job = ‘MANAGER‘;
+--------+---------+
| ename  | job     |
+--------+---------+
| SMITH  | CLERK   |
| JONES  | MANAGER |
| BLAKE  | MANAGER |
| CLARK  | MANAGER |
| ADAMS  | CLERK   |
| JAMES  | CLERK   |
| MILLER | CLERK   |
+--------+---------+
7 rows in set (0.00 sec)

2.使用union实现:

mysql> select ename,job from emp where job = ‘CLERK‘
    -> union
    -> select ename,job from emp where job = ‘MANAGER‘;
+--------+---------+
| ename  | job     |
+--------+---------+
| SMITH  | CLERK   |
| ADAMS  | CLERK   |
| JAMES  | CLERK   |
| MILLER | CLERK   |
| JONES  | MANAGER |
| BLAKE  | MANAGER |
| CLARK  | MANAGER |
+--------+---------+
7 rows in set (0.03 sec)

2.union的效率高

对于表连接来说,每连接一次新表都满足笛卡尔积,匹配的次数成倍的翻。

而union可以减少匹配的次数,还可以完成两个结果集的拼接。

假设现在有a、b、c表各有10条记录

需要从三个表中查询数据

如果只是使用表连接:a表 连接 b表 连接 c表

  • 总匹配次数:10 * 10 * 10 = 1000次

如果使用union:a表连接b表 拼接 a表连接c表

  • a连接b的匹配次数:10 * 10 = 100次
  • a连接c的匹配次数:10 * 10 = 100次
  • 总匹配次数:100 + 100 =200次
  • union把乘法变成加法

3.union使用时的注意事项

  • 3.1每条SELECT语句必须拥有相同数量的列

    • mysql> select ename,job from emp where job = ‘clerk‘
          -> union
          -> select ename from emp where job = ‘manager‘;
      ERROR 1222 (21000): The used SELECT statements have a different number of columns
      
  • 3.2每条SELECT语句的列要求拥有相同的数据类型

    • #虽然在mysql中可以查询出来不同数据类型的,但是oracle要求严格,会报错。
      mysql> select ename,job from emp where job = ‘clerk‘
          -> union
          -> select ename,sal from emp where job = ‘clerk‘;
      +--------+-------+
      | ename  | job   |
      +--------+-------+
      | SMITH  | CLERK |
      | ADAMS  | CLERK |
      | JAMES  | CLERK |
      | MILLER | CLERK |
      | SMITH  | 800   |
      | ADAMS  | 1100  |
      | JAMES  | 950   |
      | MILLER | 1300  |
      +--------+-------+
      8 rows in set (0.01 sec)
      
  • 3.3每条SELECT语句中的列的顺序要求相同

    • #在mysql中可以查询出来,但是在oracle中会报错
      mysql> select ename,job from emp where job = ‘clerk‘
          -> union
          -> select job,ename from emp where job = ‘manager‘;
      +---------+-------+
      | ename   | job   |
      +---------+-------+
      | SMITH   | CLERK |
      | ADAMS   | CLERK |
      | JAMES   | CLERK |
      | MILLER  | CLERK |
      | MANAGER | JONES |
      | MANAGER | BLAKE |
      | MANAGER | CLARK |
      +---------+-------+
      7 rows in set (0.01 sec)
      

MySQL_05union合并查询结果集

上一篇:Mysql优化(1) 存储引擎、数据类型、字符集


下一篇:Oracle-索引分裂研究