x

SQL Union Operator

Hi, Here is the example query. Query1:

 Select supp_addr_addid 'billtoaddid'
 from  supp_addr_address(nolock), supp_bu_suplmain (nolock) 
 where supp_addr_supcode = supp_bu_supcode
 and supp_addr_supcode = '12069' 
 and supp_addr_addid =  supp_bu_deforderto 

 billtoaddid
 -----------
 2
 
 (1 row(s) affected)

Query 2:

  Select supp_addr_addid 'billtoaddid'
  from  supp_addr_address(nolock), supp_bu_suplmain (nolock)
  where supp_addr_supcode = '12069' 
  and   supp_addr_supcode = supp_bu_supcode
  and supp_addr_loid = 'MJBLO' 
  and supp_addr_supcode in 
  ( 
  select supp_ou_supcode  
  from supp_ou_suplmain (nolock) 
  where supp_ou_ouinstid = 3
  and supp_ou_supcode =  '12069' 
  ) 

Query 2 output:

 billtoaddid
 -----------
 1
 2
 (2 row(s) affected)


I Need to write union for the query 1 and query 2 but the result set of the query 1 should come first. If i write the union query as below,

 Select supp_addr_addid 'billtoaddid'
 from  supp_addr_address(nolock), supp_bu_suplmain (nolock) 
 where supp_addr_supcode = supp_bu_supcode
 and supp_addr_supcode = '12069' 
 and supp_addr_addid =  supp_bu_deforderto 
 union
 Select supp_addr_addid 'billtoaddid'
 from  supp_addr_address(nolock), supp_bu_suplmain (nolock)
 where supp_addr_supcode = '12069' 
 and   supp_addr_supcode = supp_bu_supcode
 and supp_addr_loid = 'MJBLO' 
 and supp_addr_supcode in 
 ( 
 select supp_ou_supcode  
 from supp_ou_suplmain (nolock) 
 where supp_ou_ouinstid = 3
 and supp_ou_supcode =  '12069' 
 ) 

The output comes as below.

 billtoaddid
 -----------
 1
 2

 (2 row(s) affected)

I need a output of query 1 to come first. Please help any question let me know.

Thanks Satish

more ▼

asked Aug 20, 2012 at 09:08 PM in Default

avatar image

satish.N
10 1 1 1

I got the answer thanks for the help.

Thanks Satish.

Aug 20, 2012 at 09:27 PM satish.N

Satish - can you post the answer you found, it will help others who find your question useful

Aug 21, 2012 at 07:36 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It looks like there are two things preventing you from getting the output you're after.

  • The only way to guarantee the order of a query is with an ORDER BY clause.

  • Using UNION instead of UNION ALL only gives you a distinct list, not ALL the rows.

Currently your final output is 1 2

You're wanting to get

 2 (from Q1)
 1 (from Q2)
 2 (from Q2)

...in that order. To achieve this you're going to need something like

 SELECT blah
        ,1 as sort_order
 FROM #q1
 UNION ALL
 SELECT blah
        ,2 as sort_order
 FROM #q2
 ORDER BY sort_order

If you don't want the sort column coming through in the final output, this might be a good option:

 WITH your_output as (
 SELECT blah
        ,1 as sort_order
 FROM q1
 UNION ALL
 SELECT blah
        ,2 as sort_order
 FROM q2
 )
 SELECT blah
 FROM your_output
 ORDER BY sort_order

In this instance you'll get the right answer using UNION instead of UNION ALL, because the sort_order column we added differentiates between the output from q1 and q2, so why did I bother? Because UNION ALL is much cheaper than UNION. In order to get the distinct list that UNION outputs it has to sort the data to find the duplicates. This extra work comes at a price, especially in long lists.

more ▼

answered Sep 06, 2014 at 10:05 PM

avatar image

GPO
4.9k 41 51 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x33
x28
x22
x10

asked: Aug 20, 2012 at 09:08 PM

Seen: 788 times

Last Updated: Sep 06, 2014 at 10:08 PM

Copyright 2017 Redgate Software. Privacy Policy