SQL 書き方ドリル

改訂新版 反復学習ソフト付き SQL書き方ドリル (WEB+DB PRESS plusシリーズ)

評判が良さそうなので買ってみた。
第3章の「複数のテーブルを扱う」は勉強になった。
正直言うと、あまり複雑な SQL は書いたことがなかったりして...(^^;)

2、3 気になった点があったのでコメントしておく。

第3章 その2

都道府県別に、それぞれ何人ずつ顧客がいるのか教えてくれ。そうそう、この前は都道府県の名前が出ていなかったぞ。ID だけじゃわからないのでちゃんと表示してくれ。

/* 解答1 (テキストから一部自己流に変えています) */
SELECT
  p.PrefecturalID,
  p.PrefecturalName AS 都道府県名,
  COUNT(*) AS 顧客数
FROM
  Prefecturals AS p
JOIN
  Customers AS c
ON
  p.PrefecturalID = c.PrefecturalID
GROUP BY
  p.PrefecturalID,
  p.PrefecturalName
;

/* 解答2 (テキストから一部自己流に変えています) */
SELECT
  p.PrefecturalID,
  MAX(p.PrefecturalName) AS 都道府県名,
  COUNT(*) AS 顧客数
FROM
  Prefecturals AS p
JOIN
  Customers AS c
ON
  p.PrefecturalID = c.PrefecturalID
GROUP BY
  p.PrefecturalID
;

解答1と解答2はどちらが一般的なんでしょう?
パフォーマンスはそれほど変わらないのかな?

/* 解答3 (自分の解答) */
SELECT
  p.PrefecturalID,
  p.PrefecturalName AS 都道府県名,
  c.Count AS 顧客数
FROM
  Prefecturals AS p
JOIN
  (
    SELECT
      PrefecturalID,
      COUNT(*) AS Count
    FROM
      Customers
    GROUP BY
      PrefecturalID
  ) AS c
ON
  p.PrefecturalID = c.PrefecturalID
;

自分としては、この SQL が一番しっくり来る気がします。
ただ、全件ではなく部分検索の場合、

SELECT
  p.PrefecturalID,
  p.PrefecturalName AS 県名,
  c.Count AS 顧客数
FROM
  Prefecturals AS p
JOIN
  (
    SELECT
      PrefecturalID,
      COUNT(*) AS Count
    FROM
      Customers
    GROUP BY
      PrefecturalID
  ) AS c
ON
  p.PrefecturalID = c.PrefecturalID
WHERE
  p.PrefecturalName LIKE '%県'
;

だと無駄な集計処理が行われちゃいますかね?
DBMS が適当に最適化してくれるとうれしいんですが。

SELECT
  p.PrefecturalID,
  p.PrefecturalName AS 県名,
  c.Count AS 顧客数
FROM
  Prefecturals AS p
JOIN
  (
    SELECT
      PrefecturalID,
      COUNT(*) AS Count
    FROM
      Customers
    WHERE
      PrefecturalID IN (
        SELECT
          PrefecturalID
        FROM
          Prefecturals
        WHERE
          PrefecturalName LIKE '%県'
      )
    GROUP BY
      PrefecturalID
  ) AS c
ON
  p.PrefecturalID = c.PrefecturalID
;

それともこんなふうに書いた方がいいですかね。
例題のように Prefecturals から SELECT するカラムが1個だけならここまでしませんが、複数カラムだったり、さらに別のテーブルと JOIN する場合、どうしようか少し悩んでいます。

第3章 その6

商品ごとの販売数量の平均を上回った日を一覧で出してくれ。商品単位で比較してくれよ。あ、商品名は当然つけてくれよ。

/* 解答1 (テキストから一部自己流に変えています) */
SELECT
  p.ProductName,
  s.SaleDate
FROM
  Products AS p
JOIN
  Sales AS s
ON
  p.ProductID = s.ProductID
WHERE
  s.Quantity > (
    SELECT
      AVG(Quantity)
    FROM
      Sales AS s2
    WHERE
      s.ProductID = s2.ProductID
  )
ORDER BY
  p.ProductID,
  s.SaleDATE DESC
;

これがテキストの解答なんだけど...

/* 解答2 (自分の解答) */
SELECT
  p.ProductName,
  s.SaleDate
FROM
  Products AS p
JOIN
  Sales AS s
ON
  p.ProductID = s.ProductID
JOIN
  (
    SELECT
      ProductID,
      AVG(Quantity) AS AvgQuantity
    FROM
      Sales
    GROUP BY
      ProductID
  ) AS s2
ON
  s.ProductID = s2.ProductID
  AND s.Quantity > s2.AvgQuantity
ORDER BY
  p.ProductID,
  s.SaleDATE DESC
;

こちらの方がパフォーマンスが良くないでしょうか?