What is the difference among UNION and UNION ALL?
UNION: This command is utilized to choose the tuples which have related data from at least two tables.
It's like the JOIN command. However, when we are utilizing the UNION command, the choice segments should be of a similar information type. It eliminates all the copy records from the eventual outcome.
Syntax
Select Column1, Column2, Column3 from Table A
UNION
Select Column1, Column2, Column3 from Table B
Example: There are two tables named Student and Teacher
TABLE 1: Student
StudentID
StudName
StudentAge
1
Bhumi
16
2
Manas
17
3
Mallik
18
4
Suraj
15
5
Raj
15.5
TABLE 2: Teacher
TeacherID
TeachName
TeacherAge
1
Betty
28
2
Mallik
29
3
James
45
4
Suraj
30
Query:
SELECT StudName FROM Student
UNION
SELECT TeachName FROM Teacher
NO
StudName
1
Bhumi
2
Betty
3
James
4
Manas
5
Mallik
6
Raj
7
Suraj
Here, you can see the duplicate tuples are removed.
UNION ALL: This command is the same as the UNION command. It just concatenates the records. Unlike UNION, UNION ALL pulls all the values from all the tables i.e. it doesn’t eliminate duplicate records.
Syntax
Select Column1, Column2, Column3 from Table A
UNION
Select Column1, Column2, Column3 from Table B
Example: Consider the above two tables Student and Teacher.
Query:
Select Column1, Column2, Column3 from Table A
UNION ALL
Select Column1, Column2, Column3 from Table B
NO
StudName
1
Bhumi
2
Manas
3
Mallik
4
Suraj
5
Raj
6
Betty
7
Mallik
8
James
9
Suraj
You can observe here, the result is a combination of both the tables.
UNION vs UNION ALL
The only difference between Union and Union All is that Union All will not remove duplicate rows or records, instead, it just selects all the rows from all the tables which meet the conditions of your specific query and combines them into the result table.
UNION doesn’t work with a column that has Text Data Type. Whereas, UNION ALL works with all data type columns.