0

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 * FROM (
    SELECT
      t1.id AS entity_id,
      t2.title,
      'query1' AS entity_type
    FROM
      `table1` t1,
      `table2` t2
    WHERE
      t1.id = t2.id
  UNION ALL
    SELECT
      t3.id AS entity_id,
      t4.title,
      'query2' AS entity_type
    FROM
      `table3` t3,
      `table4` t4
    WHERE
      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

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). – 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
  • 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. – Michael - sqlbot Nov 12 '13 at 12:30
  • Thanks again for the explanation. Will keep that in mind and test these options. – Aram Boyajyan Nov 12 '13 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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