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:
|
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 */ IF OBJECT_ID('tempdb..#SampleData1') IS NOT NULL BEGIN DROP TABLE #SampleData1 END; IF OBJECT_ID('tempdb..#SampleData2') IS NOT NULL BEGIN DROP TABLE #SampleData2 END; GO /* Create the two Sample Tables */ CREATE TABLE #SampleData1 ( ProductName VARCHAR(50) NOT NULL, ProductPrice DECIMAL(4,2) NOT NULL ); CREATE TABLE #SampleData2 ( ProductName VARCHAR(50) NOT NULL, ProductPrice DECIMAL(4,2) NOT NULL ); GO SET NOCOUNT ON; /* Insert sample data into our two tables */ INSERT INTO #SampleData1 VALUES('Product One', 9.99); INSERT INTO #SampleData1 VALUES('Product Two', 8.99); INSERT INTO #SampleData1 VALUES('Product Three', 7.99); INSERT INTO #SampleData1 VALUES('Product Four', 6.99); INSERT INTO #SampleData1 VALUES('Product Five', 5.99); INSERT INTO #SampleData1 VALUES('Product Six', 4.99); INSERT INTO #SampleData2 VALUES('Product One', 9.99); INSERT INTO #SampleData2 VALUES('Product Two', 8.99); INSERT INTO #SampleData2 VALUES('Product Three', 7.99); INSERT INTO #SampleData2 VALUES('Product Four', 6.99); INSERT INTO #SampleData2 VALUES('Product Five', 10.99); INSERT INTO #SampleData2 VALUES('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.
|
1 2 3 4 5 |
SELECT * FROM #SampleData1 UNION SELECT * FROM #SampleData2 |
Results:
|
1 2 3 4 5 6 7 8 9 10 |
ProductName ProductPrice -------------------------------------------------- --------------------------------------- Product Five 5.99 Product Five 10.99 Product Four 6.99 Product One 9.99 Product Six 4.99 Product Six 11.99 Product Three 7.99 Product Two 8.99 |
UNION ALL returns all rows, no matter whether they are duplicates or not.
|
1 2 3 4 5 |
SELECT * FROM #SampleData1 UNION ALL SELECT * FROM #SampleData2 |
Results:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
ProductName ProductPrice -------------------------------------------------- --------------------------------------- Product One 9.99 Product Two 8.99 Product Three 7.99 Product Four 6.99 Product Five 5.99 Product Six 4.99 Product One 9.99 Product Two 8.99 Product Three 7.99 Product Four 6.99 Product Five 10.99 Product Six 11.99 |
Leave a Comment