SQL UNION All usage detailed

UNION ALL the purpose of this instruction is to merge the results of two SQL statements together. The union all and the Union differ in that the Union all lists each qualifying data, regardless of whether the data value is duplicated or not.

The syntax for union ALL is as follows:

[SQL statement 1]
UNION ALL
[SQL Statement 2]

We use the same example as the previous page to show the difference between union all and union. Also assume that we have the following two forms,

1> CREATE TABLE employee (
2>     id           int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (a),
7>     region      char (1))
8> go
1>
2> INSERT into employee (ID, name,    salary, start_date, city,     & nbsp region)
3>               values (1,   ' Jason ', 40420,  ' 02/01/94 ', ' New York ', ' W '
4> go

(1 rows affected)
1> INSERT into employee (IDs, 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 (IDs, 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 (IDs, 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 (IDs, 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 (IDs, 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 (IDs, 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 (IDs, 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 (IDs, name, salary, Start_date, city, region)
2> VALUES (9, ' Mary ', 60020, ' 06/09/02 ', ' Toronto ', ' W ')
3> Go

(1 rows affected)
1>
2> SELECT * 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>