Maximum value from multiple tables

Consider the following situation: we have T1, T2, & T3 tables in which saved id and price of products. Now we need to find the id of products which have maximum price of all 3 tables. I have this solution:

select id
from T1
where price in(
   select max(price)
   from( 
      select max(price) as price
      from T1

      union

      select max(price) as price
      from T2

      union

      select max(price) as price
      from T3
   ) temp
)   

union 

select id
from T2
where price in(
   select max(price)
   from( 
      select max(price) as price
      from T1

      union

      select max(price) as price
      from T2

      union

      select max(price) as price
      from T3
   ) temp
)   

union

....    same for T3 table.

Is it possiple to optimize this query?

I'm assuming that you mean you want the id of the item which is the most expensive based on the data from all three tables. For each table, you need the id and the price of the item(s) with the maximum price in that table. For one table, that is:

SELECT id, price FROM Tn WHERE price = (SELECT MAX(price) FROM Tn)

So, there's a sub-query:

SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
UNION
SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
UNION
SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)

And, you need to find one of the id values with the maximum price from that sub-query:

SELECT id
  FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
        UNION
        SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
        UNION
        SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
       ) AS M
 ORDER BY price DESC
 LIMIT 1

Note that if there are two equally expensive items listed in any one of the tables, the UNION query will return more than 3 rows. If there are two or more equally expensive items, the query with the LIMIT chooses one, and you can't predict which. If you need to see all the id values of equally expensive items that cost the most, then you need to play a similar game using nested queries. This blows my mind, but should work:

SELECT id, price
  FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
        UNION
        SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
        UNION
        SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
       ) AS M
 WHERE price =
       (SELECT MAX(Price)
          FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
                UNION
                SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
                UNION
                SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
               ) AS M2
       );

This would be easier using a WITH clause:

WITH MaxPrices AS
     (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
      UNION
      SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
      UNION
      SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
     )
SELECT id, price
  FROM M
 WHERE price = (SELECT MAX(Price) FROM M);

Not every DBMS supports the WITH clause like this, and I believe MySQL is one of the DBMS in the category that does not include such support.

StackExchange Database Administrators Q#12292, answer score: 4