Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

Sql Server - Union and Union All

Union and Union All, both are used to select data from one or more than one tables but still they have some differences. For Example, Union is used to select distinct data from the tables but Union All allows duplicate rows to be selected from the tables.


Suppose we have a table called tbl_Manager and the structure of this table is given below:-


CREATE TABLE tbl_Manager(Managerid int identity(1,1) not null,Managername nvarchar(255),Departmentname nvarchar(255))


Now Suppose we insert the data into the table with the help of the query given below:-


Insert into tbl_Manager(Managername,Departmentname)
select 'Vivek Johari', 'Technology' union all
select 'Atul','Testing' union all
select 'Vivek Johari', 'Mobile development' union all
select 'Atul','HR' union all
select 'Virender Singh', 'Web development' union all
select 'Virender Singh', 'R&D development' union all
select 'Jagdish','Quality Assurance' union all
select 'Jagdish','Mobile development' union all
select 'Atul','Technology'




Now Suppose, we want to select the “Managername” from the table who work in the department “Technology” and “Mobile development”.


First, we try to get the result with the help of Union.In this case the query will be given below:-


select Managername from tbl_Manager where departmentname='Technology'
union
select Managername from tbl_Manager where departmentname='Mobile development'


Result :-







Now if we use the Union All instead of union to selet the managername from the table who work in the department “Technology” and “Mobile development”, we get the following result:-

select Managername from tbl_Manager where departmentname='Technology'
union all
select Managername from tbl_Manager where departmentname='Mobile development'



Result:-








By analyzing the two results, one can easily find out that the Union all allowed dublication of the data and Union do not allows the duplicate data to appear.


The second difference between Union and Union All can be given on the basic of their uses. Both Union and Union All  can be used to insert multiple rows in the table in a single query. You can see the use of the Union All in the insert statement, which we have used to insert the data into the table tbl_Manager.But in case where we required to insert multiple rows containing duplicate values , we can’t use Union for this purpose. Also the use of Union will increase the cost of execution plan for the insert query as compared to Union all..


Also since Union does not allowed the duplication of the data, therefore it has to first select the whole data and then use the Distinct function to eliminate the duplicate data.It will increase the cost of the execution plan for the query since it have to use the two functions whereas Union All allow the duplication of the data so it only needs the select statement to show the data. So it will cost little as compare to the Union.


Summary
Both Union and Union All are used to select the data from the tables but we should use Union All unless it is required to fetch only the distinct data since the use of Union will increase the cost of the execution of the query.







Comments

Posted by gjfjjfrfhf on 28 October 2012

welcome to our (w e b i s t e) below,FREE SHIPPING :  

Air j r d an(1-24)shoes $33

Nike  Sh ox (R4,NZ,OZ)  $35

Air  Max (90,TN,2009)    $33

T-shirts  (Polo ,ED Hardy,Lac o s t e)  $16

Jean (True Religion,ED Hardy,C o o g i)  $33

FREE  SHIPPING

http://.www.newflybuy.com/

Leave a Comment

Please register or log in to leave a comment.