UNION and UNION ALL is use to combine the two or more result set.

On what basis we can combine the two result sets?
1) Selected number of columns must be same in UION or UNION ALL.
2) Selected columns order must be same in all result set.
3) Selected columns data type should be same.

Let consider the below record set of two different classes of math and science. There are some common student available in both classes.

UNION in SQL Server

We can use the UION to find out common record from two more record set and it shows only one record instead of duplicate record.
Let find out the common student and there city available in Math and Science class.

Syntax for UNION

SELECT < column1 , column2, columns 3 > FROM < table1 >
WHERE < condition >
UNION
SELECT < column1 , column2, columns 3 > FROM < table2 >
WHERE < condition >

Example

SELECT StudentName, City FROM tblMathStudent
UNION
SELECT StudentName, City FROM tblScienceStudent

Output

Note : If you observe the above code block two student Yogesh and Virat both common for Math and Science classes. When we UNION both select statement it show once entry for Yogesh and Vikrant instead of 2 entries.

Let observe the other example, just list out the student from both classes who belong to Pune city and don't show duplicate record in final result set.

Example

SELECT StudentName, City FROM tblMathStudent
WHERE City= 'Pune'
UNION
SELECT StudentName, City FROM tblScienceStudent
WHERE City= 'Pune'

Output

In above example we just list the student from Pune and belong from both classes. Yogesh is common for both classes but UION show the one entry instead of two entries.

UNION ALL in SQL Server

UNION ALL is also use to combine two or more result set like but UNION ALL shows all duplicate entries in final result set.

Find out the all student who belong to both classes.

Example

SELECT StudentName, City FROM tblMathStudent
UNION ALL
SELECT StudentName, City FROM tblScienceStudent

Output

Note : If you observe the above output we can see that UNION ALL combine all the record from both result set and it not remove the duplicate entry. So Yogesh and Virat showing two times in final result set.

What is difference between UNION and UNION ALL?

UNION UNION ALL
Functionality Combine two or more result sets. Combine two or more result sets.
Difference UNION remove the duplicate entry UNION ALL shows the duplicate entry
Example
SELECT StudentName, City FROM tblMathStudent
UNION
SELECT StudentName, City FROM tblScienceStudent
SELECT StudentName, City FROM tblMathStudent
UNION ALL
SELECT StudentName, City FROM tblScienceStudent
Output
When to use what Required combine two or more result set without duplicate entry Required combine two or more result set with duplicate entry