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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to combine the results from two queries into one result set and order them altogether.

This is the working, raw SQL code I have:

    SELECT AS entity_id,
      'query1' AS entity_type
      `table1` t1,
      `table2` t2
    WHERE =
    SELECT AS entity_id,
      'query2' AS entity_type
      `table3` t3,
      `table4` t4
    WHERE =
) AS results ORDER BY results.entity_id

The query above works perfectly, but I need to convert it into a specific syntax for a CMS database API, which does not support such queries.

I am looking for something without the SELECT * FROM(subquery) as such queries can be converted easily.

Is there any command I can use to UNION ALL and order records from both sub-queries?

Thank you!

share|improve this question
up vote 1 down vote accepted

I would consider taking the union queries and make a view using them. You can then treat the union as if it was a table.

share|improve this answer
Thank you! Very nice idea - I'm testing it now. – Aram Boyajyan Nov 11 '13 at 18:53
Works perfectly. Thanks again. – Aram Boyajyan Nov 11 '13 at 19:46
Almost perfectly. The potential pitfall here is that this often scales poorly since UNION causes views to be rendered with the TEMPTABLE algorithm which means the result of the SELECT inside the view is fetched into an implicit temporary table, and then any WHERE clause applied to the outer SELECT from the view is applied to the temporary table, removing the ability to use indexes to satisfy the WHERE clause used against the view (only WHERE clauses that are part of the view definition itself can still use indexes). – Michael - sqlbot Nov 12 '13 at 1:07
@Michael-sqlbot thanks for the heads up. Is there anything I can do to avoid this? Is there any other alternative? – Aram Boyajyan Nov 12 '13 at 9:16
@Ivanhoe123 potentially, but it will depend on the nature of the restrictions in your CMS interface; if the view, itself, returns a small result and you don't need an outer WHERE then the view is no worse than the original query, which most likely will create a temp table anyway, since table indexes can't be used for sorting the result of UNION. – Michael - sqlbot Nov 12 '13 at 12:30

Your Answer


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

Not the answer you're looking for? Browse other questions tagged or ask your own question.