What are the main differences between UNION and UNION ALL in SQL?
- How-Tos FAQs
- December 16, 2018
The main differences between UNION and UNION ALL are:
a) UNION combines the two datasets into one and then removes the duplicate data UNION ALL combines the two datasets into one and but do not removes the duplicate data;
b) Since the UNION ALL do not removes duplicate data, its performance is better than that of the UNION.
So, for gaining the performance, we should always use a UNION ALL instead of a UNION, when we are sure that there won’t be any data duplication.
Both UNION and UNION ALL can be used to combine data from a table or without a table.
1. UNION/UNION ALL without table:
SELECT '1' ID UNION ALL
SELECT '1' ID UNION ALL
SELECT '2' ID UNION ALL
SELECT '3' ID UNION ALL
SELECT '4' ID UNION ALL
SELECT '4' ID UNION ALL
SELECT '5' ID
#Output
ID
----
1
1
2
3
4
4
5
SELECT '1' ID UNION
SELECT '1' ID UNION
SELECT '2' ID UNION
SELECT '3' ID UNION
SELECT '4' ID UNION
SELECT '4' ID UNION
SELECT '5' ID
#Output
ID
----
1
2
3
4
52. UNION/UNION ALL with the table:
CREATE TABLE x (col CHAR(1))
CREATE TABLE y (col CHAR(1))
INSERT INTO x
VALUES('a'),
('b'),
('c'),
('d')
INSERT INTO y
VALUES('e'),
('f'),
('c'),
('d')
SELECT col FROM x UNION ALL
SELECT col FROM y
#Output
col
----
a
b
c
d
e
f
c
d
SELECT col FROM x UNION
SELECT col FROM y
#Output
col
----
a
b
c
d
e
fUp Next:
Read How to use PIVOT to convert rows to columns in SQL SERVER DB?
