SQL- UNION and UNION ALL

Q What is the difference between UNION and UNION ALL ?
     UNION: It combines the results of two or more SELECT queries and it will        return only distinct records. It also displays result set in sorted order.

     Consider EmpInfo as first table.

EmpInfo Table

      Consider EmpDetails as second table.

EmpDetails

      UNION is applied on the above two tables.We see that only distinct               records are displayed out of two tables.

     

Union Illustration

     UNION ALL: It also combines the results of two or more SELECT                     queries but it will not eliminate duplicate records.       

Union All Illustraion

Key points to remember before applying UNION and UNION ALL:

•  Data types of columns should be the same in each SELECT queries.
•  Order of the columns should be the same in each SELECT queries.
•  Number of columns used should be same in each SELECT queries.
•  ORDER BY clause should always be used in last SELECT statement if you want to order the result set.

Now , let me talk about performance of UNION and UNION ALL.

    UNION is slower than UNION ALL because UNION does the additional operation that is DISTINCT SORT to remove duplicates. This can be easily shown by the estimated execution plan.Press Ctrl + L in SQL Server Management Studio to get the estimated execution plan. You can see below estimated execution plan below for proper understanding.

      In the below estimated execution plan, you can see, concatenation and Distinct Sort is being performed  in UNION.

     But if you see below the estimated execution plan of UNION ALL, only concatenation operation is being performed thus making it faster than UNION.