技術とか戦略とか

SIerで証券レガシーシステムを8年いじってからSESに転職した普通の文系SEによる技術ブログ。

SQL:where 1 = 1の使い所

SQL文の条件指定で"where 1 = 1"を指定した場合、この条件は必ず真になり、全てのカラムが条件を満たすようになります。
これはSQLインジェクションで用いられることで有名ですが、プログラムでSQL文を生成する時に"where 1 = 1"を利用すると便利なことがあります。
 
今回は、以下のユーザーマスタテーブルを例に挙げて説明します。
 
create table user_master
(
user_id CHAR(4) NOT NULL,
user_name VARCHAR(10) NOT NULL,
password VARCHAR(10) NOT NULL,
PRIMARY KEY (user_id)
);
 
insert into user_master values("0001","hoge","password");
insert into user_master values("0002","fuga","password");
insert into user_master values("0003","piyo","piyopiyo");
 
----
 
全件抽出したい場合、以下のSQL文1やSQL文2のように書くことができます。
プログラムの共通部品でSQL文を生成・発行する場合、その部品で必ずwhere句をパラメータで指定する仕様になっていることがあります。
その部品で全件抽出したい場合、パラメータで"1 = 1"を指定すると、部品を直すことなく全件抽出が可能になります。
 
SQL文1】
select * from user_master;
 
SQL文2】
select * from user_master where 1 = 1;
 
【結果】
+---------+-----------+----------+
| user_id | user_name | password |
+---------+-----------+----------+
| 0001 | hoge | password |
| 0002 | fuga | password |
| 0003 | piyo | piyopiyo |
+---------+-----------+----------+
3 rows in set (0.00 sec)
 
----
 
次に、ある時期以降にパスワードの文字列に"password"を設定したユーザーを探す要件があるとします。
(user_id と password に条件が指定されることになりますが、条件は指定されないこともあるとします)
この場合も、以下のように2通りに書き方があります。
 
プログラムでSQL文を生成する場合、SQL文1よりもSQL文2の方が楽に生成できます。
SQL文1では、最初の条件していかどうかで"where"をつけるか"and"をつけるか判断する必要がありますが、SQL文2ではこの判断が不要になる(必ず"and"をつければ良い)ためです。
このように、"where 1 = 1"を固定とすることで、可変部分で"where"をつけるか"and"をつけるかの判断が不要になります。
 
SQL文1】
select * from user_master
where user_id >= "0002"
and password = "password";
 
SQL文2】
select * from user_master
where 1 = 1
and user_id >= "0002"
and password = "password";
 
【結果】
+---------+-----------+----------+
| user_id | user_name | password |
+---------+-----------+----------+
| 0002 | fuga | password |
+---------+-----------+----------+
1 row in set (0.00 sec)
 
SQL文1生成イメージ】
SQL文の文字列に"select * from user_master "を記述。
if (ユーザーIDの下限が指定されている) {
 if (最初の条件指定) {
  SQL文の文字列に"where "を追記。
 } else {
  SQL文の文字列に"and "を追記。
 }
 SQL文の文字列に"user_id >= 指定された下限"を追記。
}
if (パスワードが指定されている) {
 if (最初の条件指定) {
  SQL文の文字列に"where "を追記。
 } else {
  SQL文の文字列に"and "を追記。
 }
 SQL文の文字列に"password = 指定されたパスワード"を追記。
}
 
SQL文2生成イメージ】
SQL文の文字列に"select * from user_master where 1 = 1 "を記述。
if (ユーザーIDの下限が指定されている) {
 SQL文の文字列に"and user_id >= 指定された下限"を追記。
}
if (パスワードが指定されている) {
 SQL文の文字列に"and password = 指定されたパスワード"を追記。
}