Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have one big query which is based on 12 smaller queries that I have put together using UNION.

A relative simple example of my query:

(SELECT
    'type1' AS 'type',
     fieldOne AS someField,
     fieldTwo,
     fieldThree
     FROM tableOne
          JOIN users ON
               users.id = tableOne.id
     WHERE 
          fieldFour = 'someValue'
)
UNION
(SELECT
     'type2' AS 'type',
     fieldOne AS someField,
     fieldTwo,
     NULL AS fieldThree
     FROM tableTwo
          JOIN users ON
               users.id = tableTwo.id
     WHERE 
          fieldFour = 'someValue'
)
UNION ...

The query gets big since each of the 12 smaller queries need to have the same amount of fields, so I set many of the queries to NULL. This results in many reused lines throughout the queries. I must have the data in different tables in order to keep a good structure on things.

I think this can be made in a much more efficient way that is both smaller and takes less time to execute though I can't find any.

share|improve this question
    
If you need a column from one table, but that column cannot be populated from other table, you have to set it NULL and there is no way around it. To go around multiple unions (since you need to query all those 12 tables anyway, but all queries are different) is to split them into 12 INSERT statements into a variable or a temporary table. And then query that variable or table. –  Stoleg May 29 '13 at 20:49
    
You could try creating a view over the union of table1, table2 etc, then joining it with users. –  mustaccio May 29 '13 at 20:55
    
A better database model? if you're pulling in Like information why the seperation in the first place? could a field have been added similar to the table name to denote difference and "poof" all the unions are gone.. –  xQbert May 30 '13 at 2:08
    
@xQbert "Like information"? –  Oskar May 30 '13 at 7:00
    
Use UNION ALL instead of UNION and optimize its subquery separately. –  ypercube Aug 3 '13 at 18:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.