Monday, 11 February 2013

What is the difference between UNION and UNION ALL in SQL Server

SQL UNION and UNION ALL Operators
The UNION operator is used to combine the result-set of two or more SELECT statements.
Please note that each SELECT statement within the UNION must have the same number of columns. UNION command selects only distinct values.

The UNION ALL also works in similar way as  UNION, but  it allows duplicate records.

The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax :

SELECT Column_name(s) FROM Table_I
UNION
SELECT Column_name(s) FROM Table_II

Note: 

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SQL UNION ALL Syntax :

SELECT column_name(s) FROM Table_I
UNION ALL
SELECT column_name(s) FROM Table_II

Note: 

The Column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.
----------------------------------------------------------------------------------------------------

Example :
Let us suppose we have two Employee Tables , called EMP_MAIN and EMP_SUB as follows :

EMP_MAIN :


EMP_SUB :

-------------------------------------------------------------------------------------------------------
SQL UNION 
Now we want to list all the Unique Employee Names from above two tables EMP_MAIN , EMP_SUB using UNION Operator as follows :

SELECT*FROM EMP_MAIN
UNION 
SELECT*FROM EMP_SUB

Result :
Note: 
The UNION command selects only distinct values.
-------------------------------------------------------------------------------------------------------
SQL UNION ALL:
Now we want to list all the Employee Names(Including duplicates) from above two tables EMP_MAIN , EMP_SUB using UNION ALL Operator as follows :

SELECT*FROM EMP_MAIN
UNION ALL
SELECT*FROM EMP_SUB


Result :

Thanks,TAMATAM

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts