All Type Coding

Search Here

Union and union all in sql server

UNION 
The UNION operator selects only distinct values by default. The UNION operator is used to combine the result-set of two or more SELECT statements.
Each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.The column names in the result-set of a UNION are usually equal to the column names in the first SELECT statement in the UNION.
Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

For Example: let's see how to use union operator between two or more table
let'a say we have two table Unionall1 and Unionall 
Unionall1 






Unionall Table








Select * from Unionall1
union 
Select * from Unionall

The output will be like below:













UNION ALL
The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query (even if the row exists in more than one of the SELECT statements).

Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types.

Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Let's see how to use union all 
Select * from Unionall1
union all
Select * from Unionall


The output will be like below:












Differences between UNION and UNION ALL Operators?

1-UNION removes duplicate rows, whereas UNION ALL doesn’t.

2-UNION have to perform distinct sort to remove duplicates, which makes it less faster than UNION ALL.
3-UNION is a little bit slower than UNION ALL because When use UNION, to remove the duplicate rows, sql server has to to do a distinct sort, which is time consuming. For this reason, UNION ALL is much faster than UNION.  
4-ORDER BY clause should be used only on the last SELECT statement in the UNION query.


Note: For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be same.
Note: If you want to see the cost of DISTINCT SORT, you can turn on the estimated query execution plan using CTRL + L.

Differences between JOIN and UNION
UNION combines the result set of two or more select queries into a single result set which includes all the rows from all the queries in the UNION, where as JOIN retrieves data from two or more tables based on logical relationships between the tables.

In short, UNION combines rows from 2 or more tables, where JOIN combines columns from 2 or more tables.

No comments :

Post a Comment