サブクエリを使わない書き方

Question

商品(item)

id	name	cost
1	りんご	100
2	みかん	70
3	バナナ	200
4	メロン	1000

仕入れ(purchase)

id	item_id	count
1	1	10
2	4	1
3	2	6
4	1	5
5	3	8

売上(sales)

id	item_id	count
1	2	3
2	1	4
3	1	6
4	3	6

というデータベースがあるとして
仕入れた商品から売上で減った分の現在の在庫の合計価格を求めたいのですが
これをサブクエリなしでTEMPORARY TABLEを使って書く方法が知りたいです。

自分で考えて書いたところこんな感じになってしまったのですが
もっとスマートな書き方があるのではないかと思い質問させていただきました。

create temporary table tmp1(
 select purchase.item_id, sum(purchase.count) * item.cost costs
 from purchase, item
 where item.id = purchase.item_id
 group by purchase.item_id
);

create temporary table tmp2(
 select sales.item_id, sum(sales.count) * item.cost costs
 from sales
, item
 where item.id = sales.item_id
 group by sales.item_id
);

select sum(tmp1.costs) - sum(tmp2.costs)
 from tmp1
 left join tmp2
 on tmp1.item_id = tmp2.item_id;

Answer 1

一時テーブルを使用するのは速度面での対応ですか？
そうであるなら、インデックスさえ適切ならサブクエリーでも問題ないと思います。
逆に一時テーブルでもインデックス使用しないと意味が無かったりしますので。

select item.*
    , cost * (coalesce(item_purchase.cnt, 0) - coalesce(item_sales.cnt, 0))
from item left join (
      select item_id, sum(`count`) as cnt from purchase group by item_id
    ) as item_purchase
    on item.id = item_purchase.item_id
    left join (
      select item_id, sum(`count`) as cnt from sales group by item_id
    ) as item_sales
    on item.id = item_sales.item_id

または

select *
    , cost * (
         coalesce((select sum(`count`) from purchase where item_id=item.id), 0) 
       - coalesce((select sum(`count`) from sales where item_id=item.id), 0)
      )
from item

Comments

[hasepu0]

回答ありがとうございます。
まだSQLは学習し始めたばかりで、速度面を気にする段階でもないのですが
とある規約でサブクエリが禁止されていまして、どう書くのが適切なのかと思い質問いたしました。

ただ、coalesceという関数も知らなかったので、こういう書き方もできるのかととても参考になります。

[sazi]

1番目のSQLではインラインビューですけど、サブクエリーの対象なら、その部分を一時テーブルとして下さい。
規約については目的があるはずですから、確認した方がいいですよ。
一般的にMYSQLではサブクエリーが遅いとされています。
なので、それが理由ではないかと思います。

[hasepu0]

納得いたしました。詳しく教えていただいてありがとうございました。