パラメータがあるときだけWHERE句が有効になるようなSQLを書こうとして詰んだ

クエリ1個で済ませたかった

SELECT文のWHEREで絞りたい条件が複数あるときに静的なクエリでできないか試したけど, あんまり良くなさそうだった.

まとめ

ORとかCASEとかを使うと一応動くものは作れる.
けどあんまりパフォーマンスが良くなさそうなのでおとなしく動的SQLを使うほうが良さげ.

# どちらもTEAMとROLEに空文字('')以外の文字列が指定された場合のみWHERE句が有効になる

mysql> SELECT * FROM employee
    -> WHERE (team = @TEAM OR @TEAM = '')
    -> AND (role = @ROLE OR @ROLE = '')
    -> ;

mysql> SELECT * FROM employee
    -> WHERE CASE WHEN @TEAM = '' THEN @TEAM ELSE team END = @TEAM
    -> AND CASE WHEN @ROLE = '' THEN @ROLE ELSE role END = @ROLE
    -> ;

環境

macOS Mojave 10.14
MySQL 5.7.25

やりかた

まずは適当なテーブルを作成する.

mysql> source employee.sql

mysql> DESC employee;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| team  | varchar(10) | YES  |     | NULL    |                |
| role  | varchar(10) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM employee;
+----+----------+------+---------+------+
| id | name     | team | role    | age  |
+----+----------+------+---------+------+
|  1 | Alice    | A    | manager |   30 |
|  2 | Ben      | B    | manager |   50 |
|  3 | Charlie  | A    | member  |   40 |
|  4 | Daniel   | A    | member  |   30 |
|  5 | Emily    | A    | member  |   20 |
|  6 | Florence | A    | member  |   30 |
|  7 | George   | A    | trainee |   20 |
|  8 | Harry    | B    | member  |   40 |
|  9 | Isabel   | B    | member  |   40 |
| 10 | Jack     | B    | trainee |   20 |
| 11 | Katie    | B    | trainee |   20 |
+----+----------+------+---------+------+
11 rows in set (0.00 sec)

このテーブルから「teamがAで, かつroleがmember」のレコードを抽出するクエリは次のように書ける.

mysql> SELECT * FROM employee WHERE team = 'A' AND role = 'member';
+----+----------+------+--------+------+
| id | name     | team | role   | age  |
+----+----------+------+--------+------+
|  3 | Charlie  | A    | member |   40 |
|  4 | Daniel   | A    | member |   30 |
|  5 | Emily    | A    | member |   20 |
|  6 | Florence | A    | member |   30 |
+----+----------+------+--------+------+
4 rows in set (0.00 sec)

ここまではいいんだけど,
外部からクエリを投げるときなんかにteamとroleがそれぞれ指定されたときだけWHEREの条件を増やして, 次のようなクエリを自動で作れないか考えてみる.

# roleだけ指定された場合
mysql> SELECT * FROM employee WHERE role = 'member';

# teamだけ指定された場合
mysql> SELECT * FROM employee WHERE team = 'A';

# 両方指定された場合
mysql> SELECT * FROM employee WHERE team = 'A' AND role = 'member';

# 何も指定されなかった場合
mysql> SELECT * FROM employee;

これをプログラム側で実現するなら次のように動的にクエリを組み立てればできる.
(これは簡単すぎる例, 本当はこんな適当な文字列連結でクエリを組み立てるのはSQLインジェクションとかを考えるとたぶんセキュリティ的によろしくない)

sql_example.go

これでも問題なさそうなんだけど,
プログラム側の処理に頼らずSQLだけでこんな感じのクエリを組み立てたくなった.

できそうなのは2通り.

ORを使う

レコードの要素にNULLがない場合はORで複数の条件を合わせればできそう.

mysql> SELECT * FROM employee
    -> WHERE (team = @TEAM OR @TEAM = '')
    -> AND (role = @ROLE OR @ROLE = '')
    -> ;

WHERE (team = @TEAM OR @TEAM = '')はパラメータ@TEAMの内容によって次のように変わる.

@TEAM = ''の場合
WHERE (team = '' OR '' = '')
-> team = ''となるレコードはないので'' = ''を満たすレコード(即ち全件)を抽出
@TEAM = 'A'の場合
WHERE (team = 'A' OR 'A' = '')
-> 'A' = ''となるレコードはないのでteam = 'A'を満たすレコードのみ抽出

実際に試してみる.

mysql> SET @TEAM=''; SET @ROLE='';
mysql> SELECT * FROM employee WHERE (team = @TEAM OR @TEAM = '') AND (role = @ROLE OR @ROLE = '');
+----+----------+------+---------+------+
| id | name     | team | role    | age  |
+----+----------+------+---------+------+
|  1 | Alice    | A    | manager |   30 |
|  2 | Ben      | B    | manager |   50 |
|  3 | Charlie  | A    | member  |   40 |
|  4 | Daniel   | A    | member  |   30 |
|  5 | Emily    | A    | member  |   20 |
|  6 | Florence | A    | member  |   30 |
|  7 | George   | A    | trainee |   20 |
|  8 | Harry    | B    | member  |   40 |
|  9 | Isabel   | B    | member  |   40 |
| 10 | Jack     | B    | trainee |   20 |
| 11 | Katie    | B    | trainee |   20 |
+----+----------+------+---------+------+
11 rows in set (0.00 sec)
# SELECT * FROM employee; と同じ

mysql> SET @TEAM='A'; SET @ROLE='';
mysql> SELECT * FROM employee WHERE (team = @TEAM OR @TEAM = '') AND (role = @ROLE OR @ROLE = '');
+----+----------+------+---------+------+
| id | name     | team | role    | age  |
+----+----------+------+---------+------+
|  1 | Alice    | A    | manager |   30 |
|  3 | Charlie  | A    | member  |   40 |
|  4 | Daniel   | A    | member  |   30 |
|  5 | Emily    | A    | member  |   20 |
|  6 | Florence | A    | member  |   30 |
|  7 | George   | A    | trainee |   20 |
+----+----------+------+---------+------+
6 rows in set (0.00 sec)
# SELECT * FROM employee WHERE team = 'A'; と同じ

mysql> SET @TEAM=''; SET @ROLE='member';
mysql> SELECT * FROM employee WHERE (team = @TEAM OR @TEAM = '') AND (role = @ROLE OR @ROLE = '');
+----+----------+------+--------+------+
| id | name     | team | role   | age  |
+----+----------+------+--------+------+
|  3 | Charlie  | A    | member |   40 |
|  4 | Daniel   | A    | member |   30 |
|  5 | Emily    | A    | member |   20 |
|  6 | Florence | A    | member |   30 |
|  8 | Harry    | B    | member |   40 |
|  9 | Isabel   | B    | member |   40 |
+----+----------+------+--------+------+
6 rows in set (0.00 sec)
# SELECT * FROM employee WHERE role = 'member'; と同じ

mysql> SET @TEAM='A'; SET @ROLE='member';
mysql> SELECT * FROM employee WHERE (team = @TEAM OR @TEAM = '') AND (role = @ROLE OR @ROLE = '');
+----+----------+------+--------+------+
| id | name     | team | role   | age  |
+----+----------+------+--------+------+
|  3 | Charlie  | A    | member |   40 |
|  4 | Daniel   | A    | member |   30 |
|  5 | Emily    | A    | member |   20 |
|  6 | Florence | A    | member |   30 |
+----+----------+------+--------+------+
4 rows in set (0.00 sec)
# SELECT * FROM employee WHERE team = 'A' AND role = 'member'; と同じ

やったぜ.
と思ったらこのやり方は個別の条件を指定するクエリに比べるとINDEXが使えなくなるので, 性能が良くないらしい…¹
今はまだテーブルが小さいからうまくいってるように見えるだけなんだろうか.
SQLチューニングはちゃんと勉強したことがないのでよくわからん.

CASEでがんばる

ちょっと探してみたらCASEを使う別の方法もあった².

mysql> SELECT * FROM employee
    -> WHERE CASE WHEN @TEAM = '' THEN @TEAM ELSE team END = @TEAM
    -> AND CASE WHEN @ROLE = '' THEN @ROLE ELSE role END = @ROLE
    -> ;

こっちも構文が違うだけで結果は同じになるけど, パフォーマンスも変わらないんだろうか…

mysql> SET @TEAM='A'; SET @ROLE='member';
mysql> SELECT * FROM employee WHERE CASE WHEN @TEAM = '' THEN @TEAM ELSE team END = @TEAM AND CASE WHEN @ROLE = '' THEN @ROLE ELSE role END = @ROLE;
+----+----------+------+--------+------+
| id | name     | team | role   | age  |
+----+----------+------+--------+------+
|  3 | Charlie  | A    | member |   40 |
|  4 | Daniel   | A    | member |   30 |
|  5 | Emily    | A    | member |   20 |
|  6 | Florence | A    | member |   30 |
+----+----------+------+--------+------+
4 rows in set (0.00 sec)
# SELECT * FROM employee WHERE team = 'A' AND role = 'member'; と同じ

mysql> SET @TEAM='A'; SET @ROLE='';
mysql> SELECT * FROM employee WHERE CASE WHEN @TEAM = '' THEN @TEAM ELSE team END = @TEAM AND CASE WHEN @ROLE = '' THEN @ROLE ELSE role END = @ROLE;
+----+----------+------+---------+------+
| id | name     | team | role    | age  |
+----+----------+------+---------+------+
|  1 | Alice    | A    | manager |   30 |
|  3 | Charlie  | A    | member  |   40 |
|  4 | Daniel   | A    | member  |   30 |
|  5 | Emily    | A    | member  |   20 |
|  6 | Florence | A    | member  |   30 |
|  7 | George   | A    | trainee |   20 |
+----+----------+------+---------+------+
6 rows in set (0.00 sec)
# SELECT * FROM employee WHERE team = 'A'; と同じ

mysql> SET @TEAM=''; SET @ROLE='member';
mysql> SELECT * FROM employee WHERE CASE WHEN @TEAM = '' THEN @TEAM ELSE team END = @TEAM AND CASE WHEN @ROLE = '' THEN @ROLE ELSE role END = @ROLE;
+----+----------+------+--------+------+
| id | name     | team | role   | age  |
+----+----------+------+--------+------+
|  3 | Charlie  | A    | member |   40 |
|  4 | Daniel   | A    | member |   30 |
|  5 | Emily    | A    | member |   20 |
|  6 | Florence | A    | member |   30 |
|  8 | Harry    | B    | member |   40 |
|  9 | Isabel   | B    | member |   40 |
+----+----------+------+--------+------+
6 rows in set (0.00 sec)
# SELECT * FROM employee WHERE role = 'member'; と同じ

mysql> SET @TEAM=''; SET @ROLE='';
mysql> SELECT * FROM employee WHERE CASE WHEN @TEAM = '' THEN @TEAM ELSE team END = @TEAM AND CASE WHEN @ROLE = '' THEN @ROLE ELSE role END = @ROLE;
+----+----------+------+---------+------+
| id | name     | team | role    | age  |
+----+----------+------+---------+------+
|  1 | Alice    | A    | manager |   30 |
|  2 | Ben      | B    | manager |   50 |
|  3 | Charlie  | A    | member  |   40 |
|  4 | Daniel   | A    | member  |   30 |
|  5 | Emily    | A    | member  |   20 |
|  6 | Florence | A    | member  |   30 |
|  7 | George   | A    | trainee |   20 |
|  8 | Harry    | B    | member  |   40 |
|  9 | Isabel   | B    | member  |   40 |
| 10 | Jack     | B    | trainee |   20 |
| 11 | Katie    | B    | trainee |   20 |
+----+----------+------+---------+------+
11 rows in set (0.00 sec)
# SELECT * FROM employee; と同じ

おわり

一応動くものは作れたけど, このやり方だとパフォーマンス面を考えると微妙みたい…
参考にした記事にもあるように, やっぱりクエリを変化させたいときは動的に組み立てるほうがいいんだろうか?

目的の用途はそこまでスピードが求められるものではないので一旦はこれで良しとしたいけど, やっぱりちょっとひっかかる…
SQLの勉強は正直あまりやりたくないので多分ORMとか使って動的SQLを組み立てるほうが楽そう.

おまけ

ねこのかわいい肉球