Loading ...

Difference between UNION and UNION ALL in sql server

 /5
0 (0votes)

Union will filter duplicate values where as union all will not filter duplicate values. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values. for understanding more deeply see bellow example .

  select 'Ajit','Gurgaon' union all
  select 'Monu','Guraon' union all
  select 'vinay','Noida' union all
  select 'Ajit','Gurgaon

the above tsql statement (query) provide four records as

Ajit Gurgaon
Monu Guraon
vinay Noida
Ajit Gurgaon

and when we use union in place of union all

  select 'Ajit','Gurgaon' union
  select 'Monu','Guraon' union
  select 'vinay','Noida' union
  select 'Ajit','Gurgaon

then on executing this qurey we get the below output
Ajit Gurgaon
Monu Guraon
vinay Noida
 

Here we see when we use union all then duplicate record come in our output and on using union there are only  three  record come as our output repeated record 'Ajit Guragon' are not shown in output.

 

 

 

Comments (no comments yet)

Top Posts