forum.venkateswarlu.net
      Register      Login
uniion and union all which one is best(Recently asked interview question)

3 replies to this topic

Harikrishna Sikhakolli #1
Member
108 Points
 Posted on 26 Mar 2012 06:57 AM IST I have two tables.There is no duplicate data in the tables.
I want to combine the data from the two tables which one(union or union all)is best?WHY 
SQL Server     917 views     Reply to this topic
Gaurav Dutt #2
Member
62 Points
 Replied on 26 Mar 2012 07:00 AM IST Hi,
If you don't have any duplicate data in your table then you should use union. Actually, Union all pick all records duplicate also. 
Reply to this topic
Harikrishna Sikhakolli #3
Member
108 Points
 Replied on 26 Mar 2012 12:13 PM IST why union is best.I want reason .Both do the same work(Here there is no duplicate data). 
Reply to this topic
Harikrishna Sikhakolli #4
Member
108 Points
 Replied on 27 Mar 2012 06:40 AM IST Answer is union all
emp1
id name sal
11 jyothi 11000
12 gopi 12000
13 guru 13000
14 harikrishna 14000
emp2
id name sal
18 sowjanya 18000
17 seshu 17000
16 murali 16000
15 hemalatha 15000

select * from emp1
union
select * from emp2

output
id name sal
11 jyothi 11000
12 gopi 12000
13 guru 13000
14 harikrishna 14000
15 hemalatha 15000
16 murali 16000
17 seshu 17000
18 sowjanya 18000

It display the data in sorted order.Here it takes some buffer space to sort the data.
Here sort logical operation is performed.

union alll

select * from emp1
union all
select * from emp2

output


id name sal
11 jyothi 11000
12 gopi 12000
13 guru 13000
14 harikrishna 14000
18 sowjanya 18000
17 seshu 17000
16 murali 16000
15 hemalatha 15000
Here data is not in sort order.there is no logical operation .
 
Reply to this topic