最初に
今回はSQLのアンチパターンの話で、From句のSelect文(副問い合わせ)はSQLが読みにくくなるので避けた方がいいよ、という話です。
初級者がやりがちで、レビューで指摘することも多いのですが、あまり認知されていない気がするので、記事にしてみます。
なお、各SQLはSQL Serverのサンプルデータベース「AdventureWorks」で作成しています。動かしてみたい方は、そちらで試してみてください。以下のような結果が返ってきます。
AdventureWorksのテーブル設計等は以下で公開されています。(英語ですが…)
ダメな例
とりあえず、ダメな例を以下に示します。
全体のFrom句に、再度Select文が出てきます。
おそらくSQLの作者は、最初にヘッダの部分を作って、次に明細の部分を作って、最後にくっつけたのでしょう。まあ、気持ちは分かるのです。
特に初級者の間は、長いSQL等を一度に書くことができず、部分的なSQLを組んで、それをFrom句に置いてくっつける、という書き方をやりがちです。結果、以下のようなSQLになってしまいます。
select
Head.CustomerID,
Head.CustomerName,
Head.SalesOrderID,
Head.OrderDate,
Head.ShipDate,
Head.Status,
Head.StatusName, -- ★A
Detail.SalesOrderDetailID,
Detail.ProductID,
Detail.ProductName,
Detail.OrderQty
from
-- ヘッダを作る
(select
H.CustomerID,
concat(P.FirstName,' ',P.LastName) as CustomerName,
H.SalesOrderID,
H.OrderDate,
H.ShipDate,
H.Status,
-- Status列の値から状態名を作成
case H.Status -- ★C
when 1 then 'In process'
when 2 then 'Approved'
when 3 then 'Backordered'
when 4 then 'Rejected'
when 5 then 'Shipped'
when 6 then 'Cancelled'
end as StatusName
from
Sales.SalesOrderHeader H --★D
-- Customerを結合してPersonIDを取得
inner join Sales.Customer C on
C.CustomerID = H.CustomerID
-- PerrsonIDでPersonを引き当て(顧客氏名を取得)
inner join Person.Person P on
P.BusinessEntityID = C.PersonID
where
'2012-04-01' <= H.OrderDate and H.OrderDate < '2012-04-02'
) as Head, --★B
-- 明細を作る
(select
D.SalesOrderID,
D.SalesOrderDetailID,
D.ProductID,
P.Name as ProductName,
D.OrderQty
from
Sales.SalesOrderDetail D
-- Productを結合して製品名を取得
inner join Production.Product P on
P.ProductID = D.ProductID
) as Detail
where
Detail.SalesOrderID = Head.SalesOrderID
order by
Head.CustomerID, Head.OrderDate, Head.ShipDate, Head.SalesOrderID, Detail.SalesOrderDetailID
ダメな所 その1
例えばこのSQLで「StatusName」の詳細を調べようとすると、どうなるか。
まず以下でHeadの部分から持ってきているのね、となって
Head.StatusName, -- ★A
次に以下で、このHead部分のselect句を見ればいいのね、となって、
) as Head, --★B
次に以下を見つけて、テーブル「H」のStatus列からcaseで生成しているのね、となって
case H.Status -- ★C
最後に以下へたどり着いて、「H」は「SalesOrderHeader」だから、全部まとめるとSalesOrderHeaderテーブルのStatus列の値からcaseで作っているのね、となるわけです。
Sales.SalesOrderHeader H --★D
実際にSQL文から追ってみてほしいのですが、目が上に下に行って、非常に追いにくいのが分かると思います。
ダメな所 その2
例えばこのSQLの出力に、テーブル「SalesOrderHeader」の列「OnlineOrderFlag」を追加するとします。この時、例えばですが「★A」の前と「★C」の前の二か所に当該列の追加が必要です。
列を持ってくる処理、すなわちSelect句が二か所で書かれているため、1列の追加で二か所の変更が必要になってしまうのです。
このSQLはDRY(Don't Repeat Yourself)の原則に反しています。
改善例
改善は簡単で、From句のSelect文(副問い合わせ)をやめて、単純なSQLにまとめます。
select
H.CustomerID,
concat(Psn.FirstName,' ',Psn.LastName) as CustomerName,
H.SalesOrderID,
H.OrderDate,
H.ShipDate,
H.Status,
case H.Status -- ★A
when 1 then 'In process'
when 2 then 'Approved'
when 3 then 'Backordered'
when 4 then 'Rejected'
when 5 then 'Shipped'
when 6 then 'Cancelled'
end as StatusName,
D.SalesOrderID,
D.SalesOrderDetailID,
D.ProductID,
Prd.Name as ProductName,
D.OrderQty
from
Sales.SalesOrderHeader H --★B
inner join Sales.SalesOrderDetail D on
D.SalesOrderID = H.SalesOrderID
inner join Sales.Customer C on
C.CustomerID = H.CustomerID
inner join Person.Person Psn on
Psn.BusinessEntityID = C.PersonID
inner join Production.Product Prd on
Prd.ProductID = D.ProductID
where
'2012-04-01' <= H.OrderDate and H.OrderDate < '2012-04-02'
order by
H.CustomerID, H.OrderDate, H.ShipDate, H.SalesOrderID, D.SalesOrderDetailID
改善 その1
このSQLなら「StatusName」の詳細を調べるときも、
まず以下で「H」から持ってきたStausからCaseで作っているのね、となって
case H.Status -- ★A
次に以下で「H」は「SalesOrderHeader」なのね、となって終わりです。
Sales.SalesOrderHeader H --★B
実際にSQL文から追ってみると、ダメな例より、はるかに追いやすいのがわかると思います。
改善 その2
列「OnlineOrderFlag」を追加する場合も、例えば「★A」の前に一か所追加すればOKです。
Select句が一か所にまとまっており、DRYになっているからです。
最後に
ということで、From句のSelect文(副問い合わせ)は避けた方がよいよ、という話でした。
SQL初級者は、部分的にSQLを作成して最後にくっつける、という書き方になる傾向があり、結果、From句にSelect文が並ぶことになりやすいです。しかし、これはどうしてもSQLが汚くなります。
全体を一気に書くことは、なかなか難しいかもしれませんが、副問い合わせを使わないシンプルなSQLを心がけていくと、少しだけ幸せが増えるかと思います。