SQL query simplification

Question

select model from (
    select price, model from pc where price = (select max(price) from pc)
    union
    select price, model from laptop where price = (select max(price) from laptop)
    union
    select price, model from printer where price = (select max(price) from printer)
) t1 where price = (select max(price) from (
    select price, model from pc where price = (select max(price) from pc)
    union
    select price, model from laptop where price = (select max(price) from laptop)
    union
    select price, model from printer where price = (select max(price) from printer)
) t2 )

I'm very new to SQL so my question is very simple, but I would like to sort out one point. Am I right that this query can not be simplified to something like this?

select model from (
    select price, model from pc where price = (select max(price) from pc)
    union
    select price, model from laptop where price = (select max(price) from laptop)
    union
    select price, model from printer where price = (select max(price) from printer)
) t1 where price = (select max(price) from t1)

And if it can not be, is it a bad thing that we run two same subqueries?

Only by watching queries it is hard to understand table structure and what are you trying to implement.? — Somnath Muluk, Jul 06 '12 at 13:40
We have pc, laptop and printer tables with two columns in each: model and price. The goal is to find the most expensive models from all the tables. MySQL. — ivkremer, Jul 06 '12 at 13:46
@Kremchik - Change your structure. One table, with three columns `(type, model, price)` where type contains the `id`s for `{PC, Laptop, Printer}`. Then, if you add, for example, `monitor` or other types, you don't need to add new tables and update all your queries. — MatBailie, Jul 06 '12 at 13:49
@Dems, I can restructure my schema but the question is can we give a query result a name and select from it? What is the point of `t2` in the first query? — ivkremer, Jul 06 '12 at 14:01
@Kremchik - Many RDBMS now implement Common Table Expressions. These allow you to define a sub-query once and re-use it several times in the same outer-query. MySQL, however, does not have that capability. In terms of `Why do I need the T2 alias?`, it's simply because *all* data-sets must always be named. Technically you have `SELECT MAX(t2.price)` in there, but you just didn't type it. — MatBailie, Jul 06 '12 at 14:05
@Dems, thank you very much. I suppose the last two lines of your comment answer my question. — ivkremer, Jul 06 '12 at 14:10

score 1 · Accepted Answer · answered Jul 06 '12 at 13:59

I still say to go with one table, which is best practice design. (Not duplicating identical tables unnecessarily.)

CREATE TABLE unified_table (
  product_type,
  price,
  model
)

Doing so enables this query...

SELECT
  *
FROM
  unified_table
WHERE
  price = (SELECT MAX(price) FROM unified_table)

But, if you can't, or won't, trust the optimiser to deal with the consequences of the UNIONs...

SELECT
  *
FROM
(
  SELECT * FROM pc
  UNION ALL
  SELECT * FROM laptop
  UNION ALL
  SELECT * FROM printer
) t1
WHERE
  price = (SELECT MAX(price) FROM (SELECT price FROM pc
                                   UNION ALL
                                   SELECT price FROM laptop
                                   UNION ALL
                                   SELECT price FROM printer
                                  ) t2
          )

The optimiser will understand how to optimise this so as to remove redundant searches.

EDIT:

As a compromise, you can make a unified view, and query that...

CREATE VIEW unified_table AS
  SELECT 'pc'      AS type, * FROM pc
  UNION ALL
  SELECT 'laptop'  AS type, * FROM laptop
  UNION ALL
  SELECT 'printer' AS type, * FROM printer

Ok, so I suppose it is not the problem of duplicating the queries in the last sample of code because of the optimizer? — ivkremer, Jul 06 '12 at 14:03
@Kremchik - `T1` and `T2` will be created and processed separately. But you *don't* need to do the three MAX()es in `T2`, the optimiser already knows how to do MAX() of three UNIONed tables. And you don't need to pre-filter each table in `T1`, the optimiser only needs to search each table once with the result of MAX() from `T2`. — MatBailie, Jul 06 '12 at 14:07

Matzi · Answer 2 · 2012-07-06T14:02:16.953

0

Try something like this:

select model, price
from (
    select price, model from pc order by price desc limit 1
    union
    select price, model from laptop order by price desc limit 1
    union
    select price, model from printer order by price desc limit 1
) t1 
order by price desc
limit 1

However I would suggest you to review your database structure, this seems like you created multiple table for the same stuff (items) based on type. You could keep all this in one table, differenciated by only a content of a type column.

Without limit:

select t1.model, t1.price
from 
(select max(price) p
 from
    select max(price) p from pc
    union
    select max(price) p from laptop
    union
    select max(price) p from printer
) max_price
JOIN (
    select price, model from pc
    union
    select price, model from laptop
    union
    select price, model from printer
) t1 ON price >= max_price.p

edited Jul 06 '12 at 14:02

answered Jul 06 '12 at 13:46

Matzi

13,770
4
33
50

Thank you! But I don't think I can use `limit 1` statement because there can exist two or more models with the same price which is the highest price. – ivkremer Jul 06 '12 at 13:50
@Kremchik: does this really matter? – Matzi Jul 06 '12 at 13:56
Yes, because there can be: `pc: {100, $1000}` and `printer: {105, $2000}` and `laptop: {110, $2000}`. The query result should be 105 and 110 both. – ivkremer Jul 06 '12 at 14:06
I know that it can happen, the question is that it really matters? Anyway, I provided the limitless solution too. And as Dems said, better to change your tables. – Matzi Jul 06 '12 at 14:09
Yes, I've got the idea. Thank you, Matzi! – ivkremer Jul 06 '12 at 14:51

Gonzalo.- · Answer 3 · 2012-07-06T14:11:57.210

0

select * from (
    select price, model from pc where price = (select max(price) from pc)
    union
    select price, model from laptop where price = (select max(price) from laptop)
    union
    select price, model from printer where price = (select max(price) from printer)
) order by Price desc limit 1

Since you got 3 values to compare, from different tables, with no relation, you have to do a Union, and then compare them

This is a way, and you don't need to calculate the price again.

edited Jul 06 '12 at 14:11

answered Jul 06 '12 at 13:48

Gonzalo.-

12,512
5
50
82

We can not use `limit` because there can be two or more models with the same price which is the highest price. But anyhow my question is: can we name query results anyhow and select from it? – ivkremer Jul 06 '12 at 13:58

SQL query simplification

3 Answers3