UNION and UNION ALL

UNION and UNION ALL are used for joining two or more datasets together. The datasets need to return the same number of columns. The columns names of all datasets can be different, however, the final results will have their columns named after those in the first dataset (you can use aliases to change them).

UNION will return a distinct set of data. If there are any duplicates in any of the datasets only the first instance will be returned, the duplicates will not. UNION ALL will return all rows whether they are duplicates or not.

First we’ll create some sample data:

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

/* Drop the two Sample Tables if they exist */

IFOBJECT_ID('tempdb..#SampleData1')ISNOTNULL

BEGIN

DROPTABLE#SampleData1

END;

IFOBJECT_ID('tempdb..#SampleData2')ISNOTNULL

BEGIN

DROPTABLE#SampleData2

END;

GO

/* Create the two Sample Tables */

CREATETABLE#SampleData1

(

ProductNameVARCHAR(50)NOTNULL,

ProductPriceDECIMAL(4,2)NOTNULL

);

CREATETABLE#SampleData2

(

ProductNameVARCHAR(50)NOTNULL,

ProductPriceDECIMAL(4,2)NOTNULL

);

GO

SETNOCOUNTON;

/* Insert sample data into our two tables */

INSERTINTO#SampleData1VALUES('Product One',9.99);

INSERTINTO#SampleData1VALUES('Product Two',8.99);

INSERTINTO#SampleData1VALUES('Product Three',7.99);

INSERTINTO#SampleData1VALUES('Product Four',6.99);

INSERTINTO#SampleData1VALUES('Product Five',5.99);

INSERTINTO#SampleData1VALUES('Product Six',4.99);

INSERTINTO#SampleData2VALUES('Product One',9.99);

INSERTINTO#SampleData2VALUES('Product Two',8.99);

INSERTINTO#SampleData2VALUES('Product Three',7.99);

INSERTINTO#SampleData2VALUES('Product Four',6.99);

INSERTINTO#SampleData2VALUES('Product Five',10.99);

INSERTINTO#SampleData2VALUES('Product Six',11.99);

GO

When we run the UNION statement, we can see that it returns eight rows, it has ignored the duplicates and included the two rows that had a different price.