sql UNION ALL用法详解

union all 这个指令的目的也是要将两个 sql 语句的结果合并在一起。 union all 和 union 不同之处在于 union all 会将每一笔符合条件的资料都列出来，无论资料值有无重复。

union all 的语法如下：

[sql 语句 1]
union all
[sql 语句 2]

我们用和上一页同样的例子来显示出 union all 和 union 的不同。同样假设我们有以下两个表格，

1> create table employee(
2>     id          int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (10),
7>     region      char (1))
8> go
1>
2> insert into employee (id, name,    salary, start_date, city,       region)
3>               values (1,  'jason', 40420,  '02/01/94', 'new york', 'w')
4> go

(1 rows affected)
1> insert into employee (id, name,    salary, start_date, city,       region)
2>               values (2,  'robert',14420,  '01/02/95', 'vancouver','n')
3> go

(1 rows affected)
1> insert into employee (id, name,    salary, start_date, city,       region)
2>               values (3,  'celia', 24020,  '12/03/96', 'toronto',  'w')
3> go

(1 rows affected)
1> insert into employee (id, name,    salary, start_date, city,       region)
2>               values (4,  'linda', 40620,  '11/04/97', 'new york', 'n')
3> go

(1 rows affected)
1> insert into employee (id, name,    salary, start_date, city,       region)
2>               values (5,  'david', 80026,  '10/05/98', 'vancouver','w')
3> go

(1 rows affected)
1> insert into employee (id, name,    salary, start_date, city,       region)
2>               values (6,  'james', 70060,  '09/06/99', 'toronto',  'n')
3> go

(1 rows affected)
1> insert into employee (id, name,    salary, start_date, city,       region)
2>               values (7,  'alison',90620,  '08/07/00', 'new york', 'w')
3> go

(1 rows affected)
1> insert into employee (id, name,    salary, start_date, city,       region)
2>               values (8,  'chris', 26020,  '07/08/01', 'vancouver','n')
3> go

(1 rows affected)
1> insert into employee (id, name,    salary, start_date, city,       region)
2>               values (9,  'mary',  60020,  '06/09/02', 'toronto',  'w')
3> go

(1 rows affected)
1>
2> * from employee
3> go
id          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 jason            40420 1994-02-01 00:00:00.000 new york   w
          2 robert           14420 1995-01-02 00:00:00.000 vancouver  n
          3 celia            24020 1996-12-03 00:00:00.000 toronto    w
          4 linda            40620 1997-11-04 00:00:00.000 new york   n
          5 david            80026 1998-10-05 00:00:00.000 vancouver  w
          6 james            70060 1999-09-06 00:00:00.000 toronto    n
          7 alison           90620 2000-08-07 00:00:00.000 new york   w
          8 chris            26020 2001-07-08 00:00:00.000 vancouver  n
          9 mary             60020 2002-06-09 00:00:00.000 toronto    w

(9 rows affected)
1>
2>
3> select id, name from employee where name like 'c%'
4> union all
5> select id, name from employee where name like 'a%'
6> go
id          name
----------- ----------
          3 celia
          8 chris
          7 alison

(3 rows affected)
1>
2> drop table employee
3> go
1>