本ページで学習するのは、SQLで抽出条件を記述するときに不可欠な「述語」と呼ばれる道具である。実は、これまでも、この述語の仲間を扱っている。例えば、=、<、>、<>などの比較演算子は、正確には比較述語の一種である。
述語とは、関数の一種である、ただし、特別な条件を満たす関数となる。その条件とは、「戻り値が真理値になること」である、普通の関数は、戻り値が数値だったり文字列だったり日付だったりいろいろですが、述語の戻り値はすべて真理値（TRUE／FALSE／UNKNOWN）である。ここが述語と関数の大きな違いである。
具体的には、次の述語を扱っていく。
- LIKE
- BETWEEN
- IS NULL、IS NOT NULL
- IN
- EXISTS
LIKE述語－文字列の部分一致検索
これまでは、文字列を条件に検索するケースでは「=」を使ってきた。この「=」は、文字列が完全に一致する場合しか真（TRUE）にならない。一方、LIKE述語はもう少し曖昧で、文字列の部分一致検索を行うときに使用する。
部分一致には、大きく分けて、前方一致、中間一致、後方一致の3種類がある。
まず、検索用のテーブルを作成する。
CREATE TABLE SampleLike (strcol VARCHAR(6) NOT NULL, PRIMARY KEY (strcol));
START TRANSACTION;
INSERT INTO SampleLike (strcol) VALUES('abcddd');
INSERT INTO SampleLike (strcol) VALUES('dddabc');
INSERT INTO SampleLike (strcol) VALUES('abdddc');
INSERT INTO SampleLike (strcol) VALUES('abcdd');
INSERT INTO SampleLike (strcol) VALUES('ddabc');
INSERT INTO SampleLike (strcol) VALUES('abddc');
COMMIT;
SELECT * FROM SampleLike;
結果
+--------+ | strcol | +--------+ | abcdd | | abcddd | | abddc | | abdddc | | ddabc | | dddabc | +--------+
このテーブルから文字列「ddd」を含むレコードを選択するとする。このとき、前方一致、中間一致、後方一致では、それぞれ次のような結果の違いが生じる。
前方一致：「dddabc」が選択される
前方一致とは、検索条件となる文字列が、検索対象の文字列の最初に位置しているレコードだけが選択される検索の仕方である。
中間一致「abcddd」「abdddc」「dddabc」が選択される
中間一致は、検索条件となる文字列が検索対象の文字列の「どこか」に含まれていればレコードが選択される検索の仕方である。最初でも、最後でも、真ん中でも構わない。
後方一致：「abcddd」が選択される
後方一致は、前方一致の反対。つまり検索条件となる文字列が、文字列の最後尾にあるレコードだけが選択対象となる検索の仕方である。
このように、文字列の中に含まれる規則に基づいて検索することを「パターンマッチング」と呼ぶ。
前方一致を行う
SELECT * FROM SampleLike WHERE strcol LIKE 'ddd%';
結果
+--------+ | strcol | +--------+ | dddabc | +--------+
「%」は「0文字以上の任意の文字列」を意味する特殊な記号で、この場合だと「dddで始まるすべての文字列」を意味している。
中間一致検索
SELECT * FROM SampleLike WHERE strcol LIKE '%ddd%';
結果
+--------+ | strcol | +--------+ | abcddd | | abdddc | | dddabc | +--------+
後方一致検索
SELECT * FROM SampleLike WHERE strcol LIKE '%ddd';
結果
+--------+ | strcol | +--------+ | abcddd | +--------+
なお、「%」の代わりに「_」を使うこともできるが、これは%と違い、「任意の1文字」を意味する。
strcolが「abc+任意の2文字」で構成されるレコードを選択するには、以下のようになる。
SELECT * FROM SampleLike WHERE strcol LIKE 'abc__';
結果
+--------+ | strcol | +--------+ | abcdd | +--------+
「abc」で始まる文字列としては、「abcddd」もそうである、しかし、こちらは「ddd」が3文字なため、「__」という2文字分を指定する条件に合致しない。そのため結果にも含まれない、ということになる。したがって反対に、以下のように書けば、今度は「abcddd」だけを選択することになる。
SELECT * FROM SampleLike WHERE strcol LIKE 'abc___';
結果
+--------+ | strcol | +--------+ | abcddd | +--------+
BETWEEN述語－範囲検索
BETWEENは範囲検索を行う。この述語が他の述語や関数と異なる点は、引数を3つ使うことである。例えば、chojinテーブルから身長（height）が200から210までの超人を選択する場合、以下のようになる。
SELECT name, height FROM chojin WHERE height BETWEEN 200 AND 210;
結果
+-----------------------------+--------+ | name | height | +-----------------------------+--------+ | ウォーズマン | 210 | | ラーメンマン | 209 | | ブラックホール | 201 | | ミスター・カーメン | 200 | | アトランティス | 200 | | スニゲーター | 210 | | アシュラマン | 203 | | マーリンマン | 202 | | グリムリパー | 206 | | ネメシス | 205 | | アビスマン | 202 | | ジャスティスマン | 209 | | サイコマン | 206 | +-----------------------------+--------+
BETWEENの特徴は、200と210という両端の値も含むということ。もし両端を結果に含みたくない場合、<と
を使って書く必要がある。
SELECT name, height FROM chojin WHERE height > 200 AND height < 210;
結果
+--------------------------+--------+ | name | height | +--------------------------+--------+ | ラーメンマン | 209 | | ブラックホール | 201 | | アシュラマン | 203 | | マーリンマン | 202 | | グリムリパー | 206 | | ネメシス | 205 | | アビスマン | 202 | | ジャスティスマン | 209 | | サイコマン | 206 | +--------------------------+--------+
IS NULL、IS NOT NULL－NULLか非NULLかの判定
ある列がNULLの行を選択するためには、「=」を使うことはできない。特別なIS NULLという述語を使う必要がある。
超人属性（attribute）がNULLの超人を検索
SELECT name, attribute FROM chojin WHERE attribute IS NULL;
結果
+--------------------------+-----------+ | name | attribute | +--------------------------+-----------+ | スペシャルマン | NULL | | カナディアンマン | NULL | +--------------------------+-----------+
これとは反対にNULL以外の行を選択したければIS NOT NULLを使う。
超人属性（attribute）がNULL以外の超人を検索
SELECT name, attribute FROM chojin WHERE attribute IS NOT NULL;
結果
+--------------------------------+-----------------------+ | name | attribute | +--------------------------------+-----------------------+ | キン肉マン | アイドル超人 | | テリーマン | アイドル超人 | | ロビンマスク | アイドル超人 | | ウォーズマン | アイドル超人 | | ラーメンマン | 残虐超人 | 以下省略
IN述語－ORの便利な省略形
今度は、超人強度が1000、2000、3000の超人を選択することを考えてみる。
SELECT name, power FROM chojin WHERE power = 1000 OR power = 2000 OR power = 3000;
結果
+--------------------------+-------+ | name | power | +--------------------------+-------+ | バッファローマン | 1000 | | アシュラマン | 1000 | | クラッシュマン | 3000 | | グリムリパー | 1000 | | シングマン | 3000 | | カラスマン | 2000 | | サイコマン | 1000 | +--------------------------+-------+
これはこれで正解だが、値が増えると読みにくくなる。このようなとき、IN述語を使った形式で書き換えるとすっきりまとめられる。
SELECT name, power FROM chojin WHERE power IN (1000, 2000, 3000);
結果
+--------------------------+-------+ | name | power | +--------------------------+-------+ | バッファローマン | 1000 | | アシュラマン | 1000 | | クラッシュマン | 3000 | | グリムリパー | 1000 | | シングマン | 3000 | | カラスマン | 2000 | | サイコマン | 1000 | +--------------------------+-------+
反対に、「超人強度が1000、2000、3000以外」の超人を選択したいなら、否定形NOT INを使う。
SELECT name, power FROM chojin WHERE power NOT IN (1000, 2000, 3000);
結果
+--------------------------------+-------+ | name | power | +--------------------------------+-------+ | キン肉マン | 95 | | テリーマン | 95 | | ロビンマスク | 96 | | ウォーズマン | 100 | | ラーメンマン | 97 | | ブロッケンＪｒ | 90 | | ウルフマン | 80 | | ステカセキング | 250 | 以下省略
IN述語の引数にサブクエリを使用する
INとサブクエリ
IN述語には、他の述語にはない使い方がある。それは引数にサブクエリを指定するという使い方である。サブクエリは、SQL内部で生成されたテーブルのことであるから、「INはテーブルを引数に指定できる」という言い方をしても良い。
具体的な使い方を見るために、ここで1つ、新しいテーブルを追加する。どの超人が、どのイベントに参加したか、EVENTテーブルを作る。
CREATE TABLE event (event_id CHAR(4) NOT NULL, event_mei VARCHAR(200) NOT NULL, chojin_id CHAR(4) NOT NULL, PRIMARY KEY (event_id, chojin_id));
このCREATE TABLE文で特徴的なところは、主キーを2列指定しているところである。この理由は、テーブルに含まれるある1行を、重複なく特定するためには、イベントID（event_id）や超人ID（chojin_id）という1列だけでは不十分で、イベントと超人の組み合わせが必要になるからである。
では、eventテーブルにデータを挿入するINSERT文を作る。
START TRANSACTION;
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000A','第20回超人オリンピック編','0001');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000A','第20回超人オリンピック編','0002');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000A','第20回超人オリンピック編','0003');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000A','第20回超人オリンピック編','0005');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000B','超人オリンピック ザ・ビッグファイト編','0001');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000B','超人オリンピック ザ・ビッグファイト編','0004');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000B','超人オリンピック ザ・ビッグファイト編','0005');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000B','超人オリンピック ザ・ビッグファイト編','0006');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000B','超人オリンピック ザ・ビッグファイト編','0007');
COMMIT;
実際にテーブルを表示させてみる。
SELECT * FROM event;
結果
+----------+---------------------------------------------------------+-----------+ | event_id | event_mei | chojin_id | +----------+---------------------------------------------------------+-----------+ | 000A | 第20回超人オリンピック編 | 0001 | | 000A | 第20回超人オリンピック編 | 0002 | | 000A | 第20回超人オリンピック編 | 0003 | | 000A | 第20回超人オリンピック編 | 0005 | | 000B | 超人オリンピック ザ・ビッグファイト編 | 0001 | | 000B | 超人オリンピック ザ・ビッグファイト編 | 0004 | | 000B | 超人オリンピック ザ・ビッグファイト編 | 0005 | | 000B | 超人オリンピック ザ・ビッグファイト編 | 0006 | | 000B | 超人オリンピック ザ・ビッグファイト編 | 0007 | +----------+---------------------------------------------------------+-----------+
では、「第20回超人オリンピック編（000A）に参加した超人（chojin_id）の超人強度（power）」を求める。
この答えを出すには2つのステップを踏む必要がある。
- eventテーブルから、event_idが000Aが持っている超人ID（chojin_id）を選択する
- chojinテーブルから、1.で選択した超人（chojin_id）のみ超人強度（power）を選択する
SQLでも同様である。まず、1.のステップは次のように書ける。
SELECT chojin_id FROM event WHERE event_id = '000A';
結果
+-----------+ | chojin_id | +-----------+ | 0001 | | 0002 | | 0003 | | 0005 | +-----------+
あとは、このSELECT文そのものを2.の条件として使えば良い。
SELECT name, power FROM chojin WHERE id IN (SELECT chojin_id FROM event WHERE event_id = '000A');
結果
+--------------------+-------+ | name | power | +--------------------+-------+ | キン肉マン | 95 | | テリーマン | 95 | | ロビンマスク | 96 | | ラーメンマン | 97 | +--------------------+-------+
では、「超人オリンピック ザ・ビッグファイト編（000B）に参加しなかった正義超人」を検索する。
SELECT name, power FROM chojin WHERE category ='正義超人' AND id NOT IN (SELECT chojin_id FROM event WHERE event_id = '000B');
結果
+--------------------------+-------+ | name | power | +--------------------------+-------+ | テリーマン | 95 | | ロビンマスク | 96 | | スペシャルマン | 0 | | カナディアンマン | 0 | +--------------------------+-------+
EXISTS述語
本ページの最後で学習するのは、EXISTSという述語である。これを最後に持ってきたことには、3つの理由がある。
- EXISTSはこれまで学んだ述語とは使い方が異なる
- 構文を直感的に理解することが難しい
- 実はEXISTSを使わなくてもIN（およびNOT IN）によって、ほぼ代用できる
1.と2.の理由は、ある意味でセットになっているが、EXISTSは慣れないうちは使い方の難しい述語である。特に否定形のNOT EXISTSを使うSQL文は、熟練したDBエンジニアでも意味を即座に把握できないこともしばしばである。また、結局の所3.の理由で述べたように、INで代用できてしまうケースが多いため、「覚えたけどあまり利用しない」という人が多い述語である。
ただし、EXISTS述語は、使いこなせるようになると非常に大きな力を発揮する。そのゆえ、いずれSQLの中級入門を果たすときはマスターしていただきたい道具であるため、本ページでは基本的な使い方に絞って紹介する。
EXISTS述語の使い方
EXISTS述語の役割を一言でいうと、「“ある条件に合致するレコードの存在有無”を調べること」である。そういうレコードが存在すれば真（TRUE）、存在しなければ偽（FALSE）を返す。EXISTS（存在する）という述語の主語は、「レコード」である。
例として、「INとサブクエリ」で求めた「第20回超人オリンピック編（000A）に参加した超人（chojin_id）の超人強度（power）」を、EXISTSを使って求めてみる。
SELECT name, power FROM chojin AS C WHERE EXISTS ( SELECT * FROM event AS E WHERE E.event_id = '000A' AND E.chojin_id = C.id );
結果
+--------------------+-------+ | name | power | +--------------------+-------+ | キン肉マン | 95 | | テリーマン | 95 | | ロビンマスク | 96 | | ラーメンマン | 97 | +--------------------+-------+
EXISTSの引数
これまで学んだ述語は、だいたい「列LIKE文字列」や「列BETWEEN 値1 AND 値2」のように、2つ以上の引数を指定した。しかし、EXISTSの左側には何もない。これは妙な形であるが、その理由は、EXISTSが引数を1つしか取らない述語だからである。EXISTSは、右に引数を1つだけ書く。そしてその引数は、常にサブクエリである。この場合、
( SELECT * FROM event AS E WHERE E.event_id = '000A' AND E.chojin_id = C.id )
というサブクエリが唯一の引数である。正確には、「E.chojin_id = C.id」という条件でchojinテーブルとeventテーブルを結合しているため、相関サブクエリが引数である。EXISTSは、常に相関サブクエリを引数にとる。
サブクエリの中の「SELECT *」
先ほどのサブクエリの中で「SELECT *」としている点に違和感を感じるかもしれないが、EXISTSはレコードの存在有無しか見ないため、どんな列が返されるかを一切気にしない。EXISTSは、サブクエ内のWHERE句で指定されている条件「イベントID（event_id）が'000A'で、超人ID（chojin_id）が超人（chojin）テーブルとイベント（event）テーブルとで一致する」レコードが存在するかどうかだけ調べて、そのレコードが存在した場合にのみ、真（TRUE）を返す。
したがって、以下のように書き方をしても、結果は変わらない。
SELECT name, power FROM chojin AS C WHERE EXISTS (SELECT 1 FROM event AS E WHERE E.event_id = '000A' AND E.chojin_id = C.id);
結果
+--------------------+-------+ | name | power | +--------------------+-------+ | キン肉マン | 95 | | テリーマン | 95 | | ロビンマスク | 96 | | ラーメンマン | 97 | +--------------------+-------+
EXISTSのサブクエリで「SELECT *」と書くのは、SQLの一種の習慣だと思っていただきたい。
NOT INをNOT EXISTSで書き換える
INをNOT EXISTSで書き換えられるように、NOT IN をNOT EXISTSで書き換えることも可能。「超人オリンピック ザ・ビッグファイト編（000B）に参加した超人（chojin_id）以外の正義超人の超人強度（power）」を求めるSELECT文を、NOT EXISTSを使って書いてみる。
SELECT name, power FROM chojin AS C WHERE category = '正義超人' AND NOT EXISTS (SELECT * FROM event AS E WHERE E.event_id = '000B' AND E.chojin_id = C.id);
結果
+--------------------------+-------+ | name | power | +--------------------------+-------+ | テリーマン | 95 | | ロビンマスク | 96 | | スペシャルマン | 0 | | カナディアンマン | 0 | +--------------------------+-------+
NOT EXISTSは、EXISTSとは逆に、サブクエリ内部で指定した条件のレコードが「存在しない」場合に真（TRUE）を返す。
さて、INとEXISTSのSELECT文を見比べると、INのほうが分かりやすいと感じる人も多いのではないだろうか。最初は無理にEXISTSを使う必要はない。EXISTSには、INにない便利さがあるし、厳密にこの2つは同値ではないので、最終的にはどちらの述語もマスターしてほしいが、そうしたことは中級編に内容になってくる。
