クエリの処理順序

Oracle SQL 入門

Hadrien Lacroix

Content Developer

なぜ処理順序が重要？

クエリを最適化する
- 余計な結果を防ぐ
- 実行が速くなる

例

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

例

    SELECT BillingCountry, AVG(Total) > 100) AS Average
->  FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

例

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
->  WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

例

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
->  GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

例

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
->  HAVING AVG(Total) > 100
    ORDER BY Average DESC

例

->  SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

例

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
->  ORDER BY Average DESC

何が問題になる？

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

別名は WHERE、GROUP BY、HAVING では使えません
別名は ORDER BY では使えます

何が問題になる？

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

集計値は WHERE では絞り込めません
集計値は HAVING で絞り込めます

何が問題になる？

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100)
ORDER BY Average DESC

単一行は HAVING では絞り込めません
単一行は WHERE で絞り込めます

クエリの実行順序

FROM と JOIN: 取得するデータを決定
WHERE: 行を絞り込む
GROUP BY: 行をグループ化
HAVING: グループを絞り込む
SELECT: 列を選択し関数を適用
DISTINCT: 重複を除去
UNION、UNION ALL、INTERSECT、MINUS: 集合演算子を適用
ORDER BY: 並べ替え

演習に進みましょう！

Oracle SQL 入門