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:

      t1.id AS entity_id,
      'query1' AS entity_type
      `table1` t1,
      `table2` t2
      t1.id = t2.id
      t3.id AS entity_id,
      'query2' AS entity_type
      `table3` t3,
      `table4` t4
      t3.id = t4.id
) 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!

1 Answer 1


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.

  • 1
    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). Commented Nov 12, 2013 at 1:07
  • @Michael-sqlbot thanks for the heads up. Is there anything I can do to avoid this? Is there any other alternative? Commented Nov 12, 2013 at 9:16
  • 1
    @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. Commented Nov 12, 2013 at 12:30
  • Thanks again for the explanation. Will keep that in mind and test these options. Commented Nov 12, 2013 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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