読者です 読者をやめる 読者になる 読者になる

奇特なブログ

「殊勝に値する行いや心掛け」を意味する、奇特な人になる為のブログです

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

3月頃から、SQL ゼロからはじめるデータベース操作を読んでいたのですが、

1箇所大ハマリした所があったので、その復習も兼ねて。

で、参考になる人もいらっしゃるかもしれないのでブログに書きます。

プログラミングをやっている手続き型に慣れた人にとって、

SQLのEXISTSって馴染みづらくないですかね?

少なくともウチはそうなんですけど。

なので今回は、EXISTSの挙動をイメージ出来る様になる為に、

「おそらく内部挙動はこうなっているのではないか」と予測したプログラムを書いてみました。

合っているかどうかは分からないですけどね。

では、本題に入りますが、

まず、以下の様なデータが入っている2つのテーブルを作成しました。

参考までに、ウチの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を紐解いてみると。

サブクエリを実行した時点での結果は、上記2つで差異はないのですが、

「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実行結果に合わせてプログラムを書いただけみたいなモンです。

まぁ、今後の課題ということで、今回はここまでにします。

もうちょっと「集合論」というものを、勉強する必要がある様に感じました。