12.19 高级SQL查询_continued 连接查询结果

1、使用UNION连接结果
UNION用来将不同的SELECT结果连接成一个，每个语句必须有相同个数的列。
新建一张表old_customer与customer一样：
=====
mysql> describe customer;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| first_name | varchar(30) | YES  |     | NULL    |       |
| surname    | varchar(40) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> describe old_customer;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| first_name | varchar(30) | YES  |     | NULL    |       |
| surname    | varchar(40) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
========

得到新旧表中的用户列表：
===========
mysql> select *from customer ;
+------+------------+-------------+
| id   | first_name | surname     |
+------+------------+-------------+
|    1 | Yvonne     | Clegg       |
|    2 | Johnny     | Chaka-Chaka |
|    3 | Winston    | Powers      |
|    4 | Patricia   | Mankunku    |
+------+------------+-------------+
========
mysql> select * from old_customer;
+------+------------+---------+
| id   | first_name | surname |
+------+------------+---------+
| 5432 | Thulani    | Salie   |
| 2342 | Shahiem    | Papo    |
+------+------------+---------+
=========
mysql> select first_name,surname from customer union select first_name,surname f
rom old_customer;
+------------+-------------+
| first_name | surname     |
+------------+-------------+
| Yvonne     | Clegg       |
| Johnny     | Chaka-Chaka |
| Winston    | Powers      |
| Patricia   | Mankunku    |
| Thulani    | Salie       |
| Shahiem    | Papo        |
+------------+-------------+
==========================

union是不是只是mysql中这样用呢？ 还是所有数据库系统中都可以这样用呢？

下面的查询语句也是ok的：
============
mysql> select * from customer union select * from old_customer order by id;
+------+------------+-------------+
| id   | first_name | surname     |
+------+------------+-------------+
|    1 | Yvonne     | Clegg       |
|    2 | Johnny     | Chaka-Chaka |
|    3 | Winston    | Powers      |
|    4 | Patricia   | Mankunku    |
| 2342 | Shahiem    | Papo        |
| 5432 | Thulani    | Salie       |
+------+------------+-------------+
===========
此时order by是对整个union排序，如果需要只对某一个表排序，需要加括号。
默认情况下，union 不返回重复值，可以通过ALL重载该特性
=========
mysql> select id from customer union all select code from sale
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+
===========
对于UNION只要每个select返回的字段个数和数据类型相同就可以将毫不相关的字段组织在一起：
==========
mysql> select id,surname from customer union all select value,sales_rep from sa
es;
+------+-------------+
| id   | surname     |
+------+-------------+
|    1 | Clegg       |
|    2 | Chaka-Chaka |
|    3 | Powers      |
|    4 | Mankunku    |
| 2000 | 1           |
|  250 | 4           |
|  500 | 2           |
|  450 | 1           |
| 3800 | 3           |
|  500 | 1           |
|  720 | 2           |
+------+-------------+
===============

文章来自: 本站原创
标签: union