Microsoft SQL Server UNION Union and union all
Example
Union operation combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union and will ignore any duplicates that exist. Union all also does the same thing but include even the duplicate values. The concept of union operation will be clear from the example below. Few things to consider while using union are:
1.The number and the order of the columns must be the same in all queries.
2.The data types must be compatible.
Example:
We have three tables : Marksheet1, Marksheet2 and Marksheet3. Marksheet3 is the duplicate table of Marksheet2 which contains same values as that of Marksheet2.
Table1: Marksheet1
Table2: Marksheet2
Table3: Marksheet3
Union on tables Marksheet1 and Marksheet2
SELECT SubjectCode, SubjectName, MarksObtained
FROM Marksheet1
UNION
SELECT CourseCode, CourseName, MarksObtained
FROM Marksheet2
Note: The output for union of the three tables will also be same as union on Marksheet1 and Marksheet2 because union operation does not take duplicate values.
SELECT SubjectCode, SubjectName, MarksObtained
FROM Marksheet1
UNION
SELECT CourseCode, CourseName, MarksObtained
FROM Marksheet2
UNION
SELECT SubjectCode, SubjectName, MarksObtained
FROM Marksheet3
OUTPUT
Union All
SELECT SubjectCode, SubjectName, MarksObtained
FROM Marksheet1
UNION ALL
SELECT CourseCode, CourseName, MarksObtained
FROM Marksheet2
UNION ALL
SELECT SubjectCode, SubjectName, MarksObtained
FROM Marksheet3
OUTPUT
You will notice here that the duplicate values from Marksheet3 are also displayed using union all.
