はじめに
OracleのSQLのアンチパターンとして、メンテナンス性や可読性の悪いSQLと、修正したSQLを問題集形式で紹介します。
対象読者
- SQLのアンチパターンの問題集を使って理解を深めたい方
必要な環境
本稿で扱うSQLは、Oracle 11.2.0.1.0で動作確認しました。他のDBでも応用が可能です。
1. 1対多の集計を行うSQLのアンチパターン1
よく見かける1対多の集計を行うSQLを考えます。サンプルデータと修正対象のSQLと出力結果は下記です。
ShopT
| ShopID | ShopName |
| 1 | ABCD |
| 2 | EFGH |
| 3 | IJKL |
AmountT
| ShopID | Val |
| 1 | 100 |
| 3 | 300 |
| 3 | 900 |
ShopIDごとの、Valの合計を出力します。
修正対象のSQL
select a.ShopID,a.ShopName,sum(nvl(b.Val),0) as sumVal
from ShopT a Left Join AmountT b
on a.ShopID = b.ShopID
group by a.ShopID,a.ShopName
order by a.ShopID;
出力結果
| ShopID | ShopName | sumVal |
| 1 | ABCD | 100 |
| 2 | EFGH | 0 |
| 3 | IJKL | 1200 |
上記のSQLでは、AmountTテーブルを使って求める列はsumValという1列のみであることに注目して、シンプルな記述で同じ結果を取得できるSQLに書き直して下さい。
ヒント
Left Joinによる外部結合を行ってから、group byでグループ化しているため、結構複雑なSQLになってます。相関サブクエリを使って、少しシンプルにしてみます。
修正後のSQLと解説
修正後のSQL
select ShopID,ShopName, (select nvl(sum(b.Val),0) from AmountT b where b.ShopID = a.ShopID) as sumVal from ShopT a order by ShopID;
修正後のSQLでは、相関サブクエリを使うことによって、全体のfrom句にはShopTのみを記述すればよくなり、さらにgroup byによるグループ化も不要になりました。全体のfrom句にテーブル指定が1つしかないSQLは、たいていの場合において、可読性の高いSQLになります。
Oracleのselect文の評価順序は、下記ですので、相関サブクエリを使って、（評価順序の1番目の）全体のfrom句に記述するテーブルの数を減らし、（評価順序の9番目の）select句に参照先のテーブルを記述するのは、select文の処理を脳内でイメージしやすくして、SQLの可読性を上げることになります。
select文の評価順序
1. from句 2. where句 (結合条件) 3. start with句 4. connect by句 5. where句 (行のフィルタ条件) 6. group by句 7. having句 8. model句 9. select句 10. union、minus、intersectなどの集合演算 11. order by句
