Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Which is Better Union of SubQuery?

Author  Topic 

Gerald30
Yak Posting Veteran

62 Posts
Posted - 2012-05-08 : 21:55:46
Hello All.

Can you give me an advice on which is more efficient using a Union or SubQuery.


Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-05-08 : 21:58:44
Depends on scenario. Can you elaborate on your scenario with sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-05-08 : 22:00:34
in general, UNION and SUBQUERY are not the same at all. You just can't compare it.

Unless you have a specific query in mind that one uses UNION or another uses SUBQUERY

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Gerald30
Yak Posting Veteran

62 Posts
Posted - 2012-05-08 : 23:46:34
Uhmmm I think I have a mistake for that question.

May next question is this would type of query advisable or is there a shorter or efficient way for this query.

Please see my query.


 select 
 g.Process as "Process",
 g.SHIFT as "SHIFT",
 g.Model as "MODEL",
 sum(g.Target_Output) as "TARGET OUTPUT",
 sum(g.Output) as "OUTPUT",
(sum(g.Target_Output) -  sum(g.Output)) as "Output Variance"

 from(
 
 select 
               'Cutting' as 'Process', 
              v.cut_shf as 'SHIFT', 
               v.cut_mdl as 'Model', 
               v.cut_pdt as 'PDT',
               z.target as 'Target_Output',  
               sum(v.cut_out_qty) as 'Output'
               from mcgis_web.sum_cut_001 v 
               cross join (select tgt_mdl_nme as 'Mdl', tgt_yld as 'Yield', tgt_qty as 'Target', tgt_shf as 'SHF', tgt_dte as 'TDate', tgt_prs_id as 'ProcessID' from mcgis_test1.c_tgt_001 ) z 
               where z.mdl = v.cut_mdl And z.shf = v.cut_shf
               and z.tdate = v.cut_pdt 
              and z.ProcessID  = 10 
               group by v.cut_shf, v.cut_mdl, v.cut_pdt
               
               union
               
               
               select 
               'Chamfering' as 'Process', 
              v.chm_shf as 'SHIFT', 
               v.chm_mdl as 'Model', 
               v.chm_pdt as 'PDT',
               z.target as 'Target_Output',  
               sum(v.chm_out_qty) as 'Output'
               from mcgis_web.sum_chm_001 v 
               cross join (select tgt_mdl_nme as 'Mdl', tgt_yld as 'Yield', tgt_qty as 'Target', tgt_shf as 'SHF', tgt_dte as 'TDate', tgt_prs_id as 'ProcessID' from mcgis_test1.c_tgt_001 ) z 
               where z.mdl = v.chm_mdl And z.shf = v.chm_shf
               and z.tdate = v.chm_pdt 
              and z.ProcessID  = 16 
               group by v.chm_shf, v.chm_mdl, v.chm_pdt
               
               ) g
               
               group by  g.Shift, g.Model



May problem with this is that i cannot get the chamfering process.

Can you please help me out?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-05-08 : 23:55:04
can you also post the table schema, sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Gerald30
Yak Posting Veteran

62 Posts
Posted - 2012-05-09 : 00:15:29
Its tables would be Sum_CUT_001, SUM_CHM_001 (these two table is the summary table from my data entry.) c_tgt_001 (This the the target table where we enter the Target Output per Process Per day per shift per Model)

Their relation to each other would the process ,Date, Shift and Model.

The result that I`am expecting would be,

----------|-----|-------|-------------|---------|----------|
Process SHIFT MODEL Target Output Output Variance
----------|-----|-------|-------------|---------|----------|
Cuting DAY GSX1 31482 30000 1482
Cuting NIGHT GSX1 10426 1340 9086
Chamfering DAY MSY32 27716 5272 22444
Chamfering NIGHT MSY32 10426 1340 9086


Regarding with this on
"May problem with this is that i cannot get the chamfering process."

I just forgot to include the g.Process in group by.

May question is this query advisable or is there a shorter or efficient way?

Wouldn't this query a burden to our database?

Please teach me I still have a lot of things to learn.

Thank you....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-05-09 : 16:15:31
two things

1. you dont need cross join as what you're doing now is effecitively inner join between tables on required conditions
2. your both selects look similar except for the related column from summary tables. i think what you need is do is to add couple of select with inner joins to relevant summary tables and then use union all to merge the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Gerald30
Yak Posting Veteran

62 Posts
Posted - 2012-05-09 : 20:37:02
Ok Sir I will follow your advice thank you very much.

I have another question. Would it be possible to add Grand Total after each Process?

Example Output..

-----------|-----|-------|-------------|---------|----------|
Process SHIFT MODEL Target Output Output Variance
-----------|-----|-------|-------------|---------|----------|
Cuting DAY GSX1 31482 30000 1482
Cuting NIGHT GSX1 10426 1340 9086
Sub Total 41908 31340 10568
-----------|-----|-------|-------------|---------|----------|
Chamfering DAY MSY32 27716 5272 22444
Chamfering NIGHT MSY32 10426 1340 9086
Sub Total 38142 6612 31530


I have Tried using Roll up but I cant put a Sub Total on the last row for each process.

Thanks again.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-05-09 : 21:18:10
where are you showing data? is your front end application a reporting tool?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Gerald30
Yak Posting Veteran

62 Posts
Posted - 2012-05-10 : 00:20:11
No Sir I just Showing it on a Grid view.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-05-10 : 12:19:21
quote:
Originally posted by Gerald30

Ok Sir I will follow your advice thank you very much.

I have another question. Would it be possible to add Grand Total after each Process?

Example Output..

-----------|-----|-------|-------------|---------|----------|
Process SHIFT MODEL Target Output Output Variance
-----------|-----|-------|-------------|---------|----------|
Cuting DAY GSX1 31482 30000 1482
Cuting NIGHT GSX1 10426 1340 9086
Sub Total 41908 31340 10568
-----------|-----|-------|-------------|---------|----------|
Chamfering DAY MSY32 27716 5272 22444
Chamfering NIGHT MSY32 10426 1340 9086
Sub Total 38142 6612 31530


I have Tried using Roll up but I cant put a Sub Total on the last row for each process.

Thanks again.

why?
what happened when you used rollup/cube?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Gerald30
Yak Posting Veteran

62 Posts
Posted - 2012-05-10 : 21:47:19
When I use Rollup or Cube the record show this.


-----------|-----|-------|-------------|---------|----------|
Process SHIFT MODEL Target Output Output Variance
-----------|-----|-------|-------------|---------|----------|
Cuting DAY GSX1 31482 30000 1482
Cuting NIGHT GSX1 10426 1340 9086
Null 41908 31340 10568
-----------|-----|-------|-------------|---------|----------|
Chamfering DAY MSY32 27716 5272 22444
Chamfering NIGHT MSY32 10426 1340 9086
Null 38142 6612 31530

I want to Replace the Null part with Sub Total.

How can I do that?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-05-10 : 22:02:03
use isnull() or coalesce() function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Gerald30
Yak Posting Veteran

62 Posts
Posted - 2012-05-10 : 22:42:30
Sir can you show me an example on how to use isnull()or coalesce()?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-05-10 : 22:50:29
msdn has example

http://msdn.microsoft.com/en-us/library/ms190349.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Gerald30
Yak Posting Veteran

62 Posts
Posted - 2012-05-11 : 02:35:33
Noted Sir.

Thanks for your Help.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site