手続き型的に学ぶEXISTS述語の基本

3月頃から、SQL ゼロからはじめるデータベース操作を読んでいたのですが、
1箇所大ハマリした所があったので、その復習も兼ねて。
で、参考になる人もいらっしゃるかもしれないのでブログに書きます。

プログラミングをやっている手続き型に慣れた人にとって、
SQLのEXISTSって馴染みづらくないですかね？
少なくともウチはそうなんですけど。
なので今回は、EXISTSの挙動をイメージ出来る様になる為に、
「おそらく内部挙動はこうなっているのではないか」と予測したプログラムを書いてみました。
合っているかどうかは分からないですけどね。

では、本題に入りますが、
まず、以下の様なデータが入っている２つのテーブルを作成しました。
参考までに、ウチのDB環境はMySQL5.5.16です。

商品一覧テーブル

商品名

テレビ

パソコン

携帯電話

会員テーブル

会員ID	購入商品名
1	テレビ
1	パソコン
1	携帯電話
1	洗濯機
2	テレビ
2	パソコン
2	携帯電話
3	パソコン
3	テレビ
3	冷蔵庫
3	電子レンジ
3	DVDレコーダー
4	電子レンジ
5	テレビ

まずは、簡単なEXISTSから書くことにします。
まずは、以下のSQLについて。

-------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL文

select
 *
from
 会員テーブル A
where
 exists
  (select
   *
  from
   商品一覧テーブル B
  where
   A.購入商品名 = B.商品名
  );

結果(赤くなっている行が表示される行です)

会員ID	購入商品名
1	テレビ
1	パソコン
1	携帯電話
1	洗濯機
2	テレビ
2	パソコン
2	携帯電話
3	パソコン
3	テレビ
3	冷蔵庫
3	電子レンジ
3	DVDレコーダー
4	電子レンジ
5	テレビ

プログラム

<?php

$arr1 = array(
          array(1,'テレビ'),
	  array(1,'パソコン'),
	  array(1,'携帯電話'),
	  array(1,'洗濯機'),
	  array(2,'テレビ'),
	  array(2,'パソコン'),
	  array(2,'携帯電話'),
	  array(3,'パソコン'),
	  array(3,'テレビ'),
	  array(3,'冷蔵庫'),
	  array(3,'電子レンジ'),
	  array(3,'DVDレコーダー'),
	  array(4,'電子レンジ'),
	  array(5,'テレビ')
	);

$arr2 = array('テレビ','パソコン','携帯電話');

$view_arr = array();

// 会員テーブルの行を1行ずつ見ていく
for ($i = 0; $i < count($arr1); $i++) {
  // 会員テーブルの商品名が、商品一覧テーブルに含まれているか
  if (true === in_array($arr1[$i][1], $arr2)) {
    // 含まれて「いる」場合は、表示対象行となる
    $view_arr[] = $arr1[$i];
  }
}

for ($i = 0; $i < count($view_arr); $i++) {
  for ($j = 0; $j < count($view_arr[$i]); $j++) {
    echo $view_arr[$i][$j] . ',';
  }
  echo "\r\n";
}

-------------------------------------------------------------------------------------------------------------------------------------------------------------

さて、上記のSQLを紐解いてみますと。
これは、「会員テーブルの購入商品名が、商品一覧テーブルに存在するかどうかを調べ、
存在する行のみを表示する」というSQLです。
「A.購入商品名 = B.商品名」の所で、商品が存在するかをチェックしています。
で、上記の場合だと、サブクエリのSQL(EXISTSの「内」側のSQL)でヒットする行が赤文字になっている行で、
その行をそのまま表示しています。
つまりEXISTSの基本は、「サブクエリでヒットした行をそのまま表示する」ということになります。
勿論、サブクエリじゃない方のSQL(EXISTSの「外」側のSQL)にwhere句とかがあれば、
更に表示する行は絞りこまれますけど。

次は、上記の逆で「NOT」を使ってみます。

-------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL文

select
 *
from
 会員テーブル A
where
 not exists
  (select
   *
  from
   商品一覧テーブル B
  where
   A.購入商品名 = B.商品名
  );

結果(赤くなっている行が表示される行です)

会員ID	購入商品名
1	テレビ
1	パソコン
1	携帯電話
1	洗濯機
2	テレビ
2	パソコン
2	携帯電話
3	パソコン
3	テレビ
3	冷蔵庫
3	電子レンジ
3	DVDレコーダー
4	電子レンジ
5	テレビ

プログラム

<?php

$arr1 = array(
	  array(1,'テレビ'),
	  array(1,'パソコン'),
	  array(1,'携帯電話'),
	  array(1,'洗濯機'),
	  array(2,'テレビ'),
	  array(2,'パソコン'),
	  array(2,'携帯電話'),
	  array(3,'パソコン'),
	  array(3,'テレビ'),
	  array(3,'冷蔵庫'),
	  array(3,'電子レンジ'),
	  array(3,'DVDレコーダー'),
	  array(4,'電子レンジ'),
	  array(5,'テレビ')
	);

$arr2 = array('テレビ','パソコン','携帯電話');

$view_arr = array();

// 会員テーブルの行を1行ずつ見ていく
for ($i = 0; $i < count($arr1); $i++) {
  // 会員テーブルの商品名が、商品一覧テーブルに含まれていないか
  if (false === in_array($arr1[$i][1], $arr2)) {
    // 含まれて「いない」場合は、表示対象行となる
    $view_arr[] = $arr1[$i];
  }
}

for ($i = 0; $i < count($view_arr); $i++) {
  for ($j = 0; $j < count($view_arr[$i]); $j++) {
    echo $view_arr[$i][$j] . ',';
  }
  echo "\r\n";
}

-------------------------------------------------------------------------------------------------------------------------------------------------------------

「NOT」を付けなかった時とは、全く逆の結果になったって当たり前ですけど。
じゃあ、SQLを紐解いてみると。
サブクエリを実行した時点での結果は、上記２つで差異はないのですが、
「NOT」の場合は、サブクエリのSQLでヒット「しない」行を表示します。
つまりNOT EXISTSの基本は、「サブクエリでヒット"しない"行をそのまま表示する」ということになります。
こうやって書くと至って単純なんですけどねぇ。

じゃあ次は、EXISTSを入れ子にしてみます。
サブクエリを示す括弧に色を着けたのにも注意です。

-------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL文

select
 *
from
 会員テーブル A1
where
 exists
  (select
   *
  from
   商品一覧テーブル B
  where
   exists
    (select
     *
    from
     会員テーブル A2
    where
     A1.会員ID = A2.会員ID and
     B.商品名 = A2.購入商品名
    )
  );

結果(赤くなっている行が表示される行です)

会員ID	購入商品名
1	テレビ
1	パソコン
1	携帯電話
1	洗濯機
2	テレビ
2	パソコン
2	携帯電話
3	パソコン
3	テレビ
3	冷蔵庫
3	電子レンジ
3	DVDレコーダー
4	電子レンジ
5	テレビ

プログラム

<?php

$arr1 = array(
	  array(1,'テレビ'),
	  array(1,'パソコン'),
	  array(1,'携帯電話'),
	  array(1,'洗濯機'),
	  array(2,'テレビ'),
	  array(2,'パソコン'),
	  array(2,'携帯電話'),
	  array(3,'パソコン'),
	  array(3,'テレビ'),
	  array(3,'冷蔵庫'),
	  array(3,'電子レンジ'),
	  array(3,'DVDレコーダー'),
	  array(4,'電子レンジ'),
	  array(5,'テレビ')
	);

$arr2 = array('テレビ','パソコン','携帯電話');

$view_arr = array();

// 会員テーブルAの行を1行ずつ見ていく
for ($i = 0; $i < count($arr1); $i++) {
  // 商品一覧テーブルの行を1行ずつ見ていく
  for ($j = 0; $j < count($arr2); $j++) {	
    // 会員テーブルBの行を1行ずつ見ていく
    for ($k = 0; $k < count($arr1); $k++) {
      // 会員テーブルの会員IDが同じものの中で商品を比較したいので
      // 会員テーブルの会員IDが同じかどうかチェックする
      if ($arr1[$i][0] === $arr1[$k][0]) {
	// 同じ会員IDの中で、商品名が一致するか
	if (true === in_array($arr2[$j], $arr1[$k])) {
	  // 含まれて「いる」なら表示対象行とし、会員テーブルAの参照行を次の行にする
	  /* 同じ会員IDの中で、商品名が1行でも一致していたら、
	  その会員IDの全ての行が表示対象行となる */
	  $view_arr[] = $arr1[$i];
	  $k = count($arr1);
	  $j = count($arr2);
	}
      }
    }
  }
}

for ($i = 0; $i < count($view_arr); $i++) {
  for ($j = 0; $j < count($view_arr[$i]); $j++) {
    echo $view_arr[$i][$j] . ',';
  }
  echo "\r\n";
}

-------------------------------------------------------------------------------------------------------------------------------------------------------------

これは、「会員ID毎に、購入商品が商品一覧に存在するかどうかを調べ、
存在する場合は、その会員IDと同じ会員IDの行を全て表示する」SQLです。
逆に言えば、同じ会員ID内で一行でも商品一覧に存在する購入商品があれば、
存在する行の会員IDと同じ会員IDの行も表示されるって事ですね。
と、ここまでは良いんですが、ただプログラムの方が微妙な感じが（苦笑）
何となくですけど、こういう内部挙動では無い気がするんですよね。

と、段々分からなくなってきたんですが、
最後に一応、NOT EXISTSの入れ子を掲載しておきます。
上記の書籍でハマったのはコレでした。

-------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL文

select
 *
from
 会員テーブル A1
where
 not exists
  (select
   *
  from
   商品一覧テーブル B
  where
   not exists
    (select
     *
    from
     会員テーブル A2
    where
     A1.会員ID = A2.会員ID and
     B.商品名 = A2.購入商品名
    )
  );

結果(赤くなっている行が表示される行です)

会員ID	購入商品名
1	テレビ
1	パソコン
1	携帯電話
1	洗濯機
2	テレビ
2	パソコン
2	携帯電話
3	パソコン
3	テレビ
3	冷蔵庫
3	電子レンジ
3	DVDレコーダー
4	電子レンジ
5	テレビ

プログラム

<?php

$arr1 = array(
	  array(1,'テレビ'),
	  array(1,'パソコン'),
	  array(1,'携帯電話'),
	  array(1,'洗濯機'),
	  array(2,'テレビ'),
	  array(2,'パソコン'),
	  array(2,'携帯電話'),
	  array(3,'パソコン'),
	  array(3,'テレビ'),
	  array(3,'冷蔵庫'),
	  array(3,'電子レンジ'),
	  array(3,'DVDレコーダー'),
	  array(4,'電子レンジ'),
	  array(5,'テレビ')
	);

$arr2 = array('テレビ','パソコン','携帯電話');

$view_arr = array();

$str = '';

// 会員テーブルAの行を1行ずつ見ていく
for ($i = 0; $i < count($arr1); $i++) {
  // 商品名一致カウンター
  $hits = 0;
  // 商品一覧テーブルの行を1行ずつ見ていく
  for ($j = 0; $j < count($arr2); $j++) {
    // 会員テーブルBの行を1行ずつ見ていく
    for ($k = 0; $k < count($arr1); $k++) {
      // 会員テーブルの会員IDが同じものの中で商品を比較したいので
      // 会員テーブルの会員IDが同じかどうかチェックする
      if ($arr1[$i][0] === $arr1[$k][0]) {
	// 同じ会員IDの中で、商品名が一致するかどうかをチェック
	if (true === in_array($arr2[$j], $arr1[$k])) {
	  // 商品名が同じなら商品名一致カウンターを増やし、商品一覧テーブルの参照行を次の行にする
	  $hits++;
	  break;
	}
      }
    }
    // 商品名一致カウンターと商品一覧テーブルの商品数が同じかどうかチェックする
    if ($hits === count($arr2)) {
      // 同じ場合は、商品一覧テーブル内の商品が全て含まれているので、表示対象行とする
      $view_arr[] = $arr1[$i];
    }
  }
}

for ($i = 0; $i < count($view_arr); $i++) {
  for ($j = 0; $j < count($view_arr[$i]); $j++) {
    echo $view_arr[$i][$j] . ',';
  }
  echo "\r\n";
}

-------------------------------------------------------------------------------------------------------------------------------------------------------------

一応、NOT EXISTSの入れ子のSQLにも触れておくと、
「商品一覧テーブルに存在する"全て"の商品を購入した人を表示するSQL」です。
プログラムはもう、メチャクチャですね（苦笑）
単に、SQL実行結果に合わせてプログラムを書いただけみたいなモンです。

まぁ、今後の課題ということで、今回はここまでにします。
もうちょっと「集合論」というものを、勉強する必要がある様に感じました。