Tuesday, 9 July 2013

UNION and UNION ALL in sql server

Hi,
In This article we will What is UNION and UNION ALL? What is difference between both?
and details about both.

1) Union and Union All::-

The purpose of the SQL UNION
and UNION ALL commands are to combine the results of two or more queries
into a single result set consisting of all the rows belonging to all the queries
in the union.
but their is one condition a column count and datatype of all must same when you use union .
The question becomes whether or not to use the ALL syntax.

2)What is difference between Union and Union All?

The difference between 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.

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.

In simple word a union is give you a distinct data from both query,and
union all is give all data with all duplicate records.in case of union all
their is no need to check duplicate records thats why a union all is faster than
union.

3)Check below query for understanding how to use UNION and UNION ALL

A) Create table and fill some data ::

 create table #emp(Emp_name varchar(100),dept varchar(50), emp_sal int)
insert into #emp(Emp_name ,dept , emp_sal)values('adi','IT',123)
insert into #emp(Emp_name ,dept , emp_sal)values('raj','IT',1231)
insert into #emp(Emp_name ,dept , emp_sal)values('nisha','WC',1232)
insert into #emp(Emp_name ,dept , emp_sal)values('shehal','WC',1233)
insert into #emp(Emp_name ,dept , emp_sal)values('kiran','aw',1234)
insert into #emp(Emp_name ,dept , emp_sal)values('shehal','WC',1233)
insert into #emp(Emp_name ,dept , emp_sal)values('kiran','aw',1234)


create table #manager(manager_name varchar(100),dept varchar(50), manager_sal int) insert into #manager(manager_name,dept ,manager_sal )values ('sandip','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('aditya','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('pranav','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('ajit','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('harshal','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('ajit','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('harshal','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('ajit','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('harshal','admin',5000)

B)UNION ::

select * from #emp union select * from #manager

C)UNION ALL ::

select * from #emp union all select * from #manager


Other Popular Posts ::

Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update

No comments:

Post a Comment

8451831077