『SQLアンチパターン』から学ぶ！効果的なSQLクエリの記述方法を解説

SQLアンチパターンとは？

まずは、「アンチパターン」という言葉について解説します。

アンチパターンとは簡単に説明すると、間違った典型例を集めたテンプレート集のことです。

ソフトウェア開発などにおいて「こうあるべき」という典型例があるように、「こういったものは失敗につながる」ということを教えてくれます。

アンチパターンによって、未然に失敗を回避できるメリットがあるわけです。

本題の「SQLアンチパターン」は、その名の通りSQLにおけるアンチパターンのことです。

このアンチパターンは書籍にもなっており、SQLを扱う人は読んでおいて損はないでしょう。

この記事では、本書から学べるSQLアンチパターンについて解説します。

SQLアンチパターンの目次

本書の目次は次の通りです。

この記事では、本書の中から「III部　クエリのアンチパターン」の部分について詳しく見ていきます。

クエリのアンチパターン

フィア・オブ・ジ・アンノウン（恐怖のunknown）

NULLを扱う列に対して、「NULLを一般値として使っていないか、もしくは一般値をNULLとして使っていないか」というアンチパターンです。

サンプルを用いて説明します。

A.NULLを一般値として使うサンプル

例えば、従業員の情報を格納した次のようなテーブルがあったとします。

employee

id	name	hire_date	rank
1	Suzuki	2006-04-01	A
2	Tanaka	2010-10-15	B
3	Kimura	2020-04-01	NULL

このテーブルでは「rank」として「S、A、B、C」のいずれかの値が入ることを想定しています。

ただし、まだrankが決まっていない従業員の情報は入力しておらず「NULL」となっています。

このテーブルに対して、ランクがついていない人（ランクが入力されていない人）の情報を取得するために、次のようなSQLクエリを実行したとしましょう。

SELECT
  id, name
FROM
  employee
WHERE
 rank NOT IN ('S', 'A', 'B', 'C');

本書では、上記のSQLクエリをアンチパターンとしています。

B.一般値をNULLとして使うサンプル

また、サンプルAのような場合を想定して、NULLの代わりに別の値「-1」や「unknown」などを入力することもアンチパターンとしています。

employee

id	name	hire_date	rank
1	Suzuki	2006-04-01	A
2	Tanaka	2010-10-15	B
3	Kimura	2020-04-01	-1

この例をアンチパターンとしているのは、将来的に別の値が他の意味をなす値になる場合に、値の移行作業が発生する可能性が生まれるからです。

では、このようなアンチパターンを回避するにはどうすれば良いのでしょうか。

本書では、次の3つの回避策が挙げられています。

1. IS NULLで検索する
2. 列にNOT NULL制約を宣言する
3. 動的なデフォルトを設定する

まず、NULLの入っている列に検索をかける場合は「IS NULL」で検索するようにします。

これでNULLを設定しているデータを検索可能です。

 

次に、そもそも列の値にNULLが入らないように設定するパターンです。

NULLが入っていない保証ができれば、サンプルAのパターンでの検索もヒットするでしょう。

 

最後に、動的なデフォルトを設定する方法です。

この方法の具体策として「COALESCE関数」が挙げられています。

COALESCE関数とは、NULLの代替値を取得する関数のことです。

詳しくは、以下の記事で解説していますので参考にしてみてください！

【関連記事】
▶︎SQL coalesce 値がNULLの場合の代替値を返す 簡易版のNVLやNULLIFが使えるDBもあり

アンビギュアスグループ（曖昧なグループ）

「曖昧なグループ」というタイトルの通り、ここではグループ化（GROUP BY句）についてのアンチパターンが解説されています。

具体的には、GROUP BY句を使う際に非グループ化列を参照すると、エラーが生じることです。

GROUP BY句を使っていて、同様のエラーが発生したことのある人もいるのではないでしょうか。

先ほど同様に、サンプルを用いて解説します。

user_score

id	name	class	score
1	Suzuki	A	60
2	Tanaka	B	71
3	Kimura	B	83

上記のテーブルに対して、次のようなSQLクエリを実行したいとしましょう。

SELECT
  id, name, min(score)
FROM
  user_score
GROUP BY
 class;

このSQLクエリを実行した結果得られるデータとして、次のような結果が返ってくるかと思います。

id	name	score
1	Suzuki	60
2	Tanaka	71

ですが、実際にはエラーとなり結果は返ってきません。

 

このようなアンチパターンの解決策として、本書では次の6つを紹介しています。

1. 関数従属性のある列のみにクエリを実行
2. 相関サブクエリを使用
3. 導出テーブルを使用
4. JOINを使用
5. 他列に対しても集約関数を使用
6. グループごとにすべての値を連結

さまざまな解決策があるので、テーブルや状況に応じて使い分けられるようになると良いでしょう。

なお、JOIN関数については以下の記事で解説しています。

【関連記事】
▶︎SQLで複数テーブルを扱うにはJOINで結合 内部結合と外部結合の使い分け

ランダムセレクション

テーブル内からデータをランダムに取得する場合のアンチパターンです。

例えば、次のようなSQLクエリの実行を考えます。

SELECT
  *
FROM
 bugs
ORDER BY
  RAND() LIMIT 1;

RAND関数を用いて、1つデータを取得する実行文になっているのがわかりますね。

RAND関数は、インデックスが効かないためデータをフルスキャンします。

そのため、テーブルのデータ数が多い場合に性能が悪化する可能性が高いです。

 

このアンチパターンの回避策として、本書では次の5つを紹介しています。

1. 1と最大値の間のランダムなキー値を選択
2. 欠番の穴の後にあるキー値を選択
3. すべてのキー値リストを受け取り、ランダムに1つを選択
4. オフセットを用いてランダムに行を選択
5. ベンダー依存の解決策

ベンダー依存の解決策として、SQL Server 2005の「TABLESAMPE句」が挙げられていました。

その他、各データベース固有の解決策があれば、そちらに準拠するのが良いでしょう。

プアマンズ・サーチエンジン（貧者のサーチエンジン）

文字列検索の際に、次のような「LIKE述語」をはじめとしたパターンマッチ述語を使うことはないでしょうか。

SELECT
  *
FROM
 bugs
WHERE
 description
LINE
 '%crash%';

このような曖昧な検索方法は、テーブルのデータ量が増えた場合にその性能が落ちる可能性があります。

上記アンチパターンの回避策としては、「全文検索エンジン（フルテキストインデックス）」を用いることが紹介されています。

各データベースごとの機能例が紹介されているので、詳しく理解したい人は本書をチェックしてみると良いでしょう。

スパゲッティクエリ

「スパゲッティクエリ」や「スパゲッティコード」という言葉をご存知でしょうか。

その意味は、プログラムのソースコード（クエリ）が、コードを制作したエンジニア以外にとって解読困難であることを表す俗語ということです。

スパゲッティクエリは、次のようなデメリットがあるため、アンチパターンとして紹介されています。

• 制作者以外の人が理解できない
• バグを生みやすい
• メンテナンスしづらい

 

【アンチパターン例】

SELECT p.product_id,
  COUNT(f.bug_id) AS count_fixed,
  COUNT(o.bug_id) AS count_open
FROM BugsProducts p
INNER JOIN Bugs f ON p.bug_id = f.bug_id AND f.status = 'FIXED'
INNER JOIN BugsProducts p2 USING (product_id)
INNER JOIN Bugs o ON p2.bug_id = o.bug_id AND o.status = 'OPEN'
WHERE p.product_id = 1
GROUP BY p.product_id;

 

このアンチパターンの解決策の基本は「分割」です。

複雑な式を組み合わせて実行クエリを少なくするのではなく、簡単なクエリを複数回行うことを推奨しています。

1. ワンステップずつ
2. UNIONを用いる
3. CASE式とSUM関数を組み合わせる
4. 上司の問題を解決する
5. SQLを用いたSQLの自動的な記述

 

【関連記事】
▶︎【SQL】複雑な条件にも対応できるCASE式について解説。

【関連記事】
▶︎SQL sumのサンプルコード集 基本の小計出力から、NULL対策まで

インプリシットカラム（暗黙の列）

テーブル内のデータを取得する際に、ワイルドカード「*」を用いる人が多いのではないでしょうか。

カラム名をすべて記述するよりもタイプ数が減り、便利な物だと言えるかもしれません。

しかし、ワイルドカードでの取得は本来不要なカラムの情報まで取得するため、大量のデータを扱う場合などはパフォーマンスに影響を及ぼすこともあるでしょう。

インプリシットカラムの解決策は、非常に簡単かつ明確で「列名を明示的に指定する」です。

必要な情報だけを取得するように意識すると、パフォーマンスの向上につながることでしょう。

まとめ

書籍『SQLアンチパターン』からクエリに関するアンチパターン6つについて解説しました。

事前にアンチパターンを理解することで、余計な手間や予期せぬエラーの回避につながります。

特に大量のデータを扱うDB・テーブルでは。本書の内容が有効活用できる機会が多くあると思います。

SQL初学者はもちろん、より効果的なSQL手法を知りたい人にもおすすめです。

ぜひ本書やこの記事を参考に、SQLアンチパターンを理解してください！