none
Alternative to Union in the query

    Question

  • Hi Team,

    What is the alternative to union?

    Here is a basic query but there are lot of fields in the actual query and lot of joins. The issue using Union is performance is very bad.

    Select t1.ID,t1.Value,t2.Name From Table1 t1
    Join Table2 t2 on t1.ID = t2.ID
    
    Union
    
    Select t1.ID,t1.Value,t3.Name From Table1 t1
    Join Table3 t3 on t1.ID = t3.ID
    Thanks,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Friday, September 13, 2013 4:46 AM

Answers

  • This may be one of the option, try and see the eprfomance:

    You may create a temp table and insert each results separately into the temp table. May be you can use DISTINCT from the temp table.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, September 13, 2013 4:48 AM

All replies

  • This may be one of the option, try and see the eprfomance:

    You may create a temp table and insert each results separately into the temp table. May be you can use DISTINCT from the temp table.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, September 13, 2013 4:48 AM
  • In addition to Latheesh notes, It's better to use UNION ALL. But for your case that you want to retrieve data from two table in each SELECT statements, it's better to consider using temporary table.

    Another way is to change you database design!!!

    Yes, may be it's the best solution if it possible.


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd
    Saeid Hasani's home page

    Friday, September 13, 2013 4:59 AM