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実行結果に合わせてプログラムを書いただけみたいなモンです。
まぁ、今後の課題ということで、今回はここまでにします。
もうちょっと「集合論」というものを、勉強する必要がある様に感じました。
