Monday , December 11 2017
Home / DataBase / SQL SERVER / Union vs Union All in Sql Server

Union vs Union All in Sql Server

Union vs Union All in Sql Server:

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

in short UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

Example :

Output:

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL will selects all values.

in short UNION ALL does not remove duplicates, and it therefore faster than UNION.

Example:

Output:

Difference between Union and Union ALL:

  • Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
  • UNION ALL will perform better than UNION
  • UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

Example:

I have created two tables now i am adding sample data in above two tables using below script

In both tables they have 2 common names(Balu,Murali)

now if i perform union then it will eliminate duplicate values

Output:

now if i perform union all then it will show all rows

output:

Note:

  1. While using this commands all selected columns need to be of the same data type.
  2. A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results when compare to UNION command

Thanks for reading this article if you like this article please share with your Friends.

Print Friendly, PDF & Email

Give us your valuable feedback

comments

About Murali

Hello everyone I am Software Developer having 3+ years of Experience in Microsoft Technologies and Others. My skills :- Asp.net, C#,Ado.net, SQL SERVER, web services, windows Service,Windows applications,HTML,CSS, JavaScript,JQuery,AnjularJs

Check Also

Find all rows count and columns count in single database in sql server

In General for any database it contains lot of tables, if we want to find …

%d bloggers like this: