MySQL で２つの結果を比較して片方に含まれないデータを抽出したい（not existsを利用しました）

Question

次のような2つのSQLと結果があります・

SQL１
 select d.no as no
 from tbl_d as d 
 inner join tbl_e as e 
 on e.no = d.no 
 where e.auth1 = '0'
 and e.auth1 <> '1';

この結果が

結果１
| no  |
| 001 |
| 003 |
| 007 |
| 008 |
| 010 |
| 011 |
| 012 |
| 015 |
| 016 |
| 020 |
| 025 |
| 030 |
| 035 |
| 045 |
| 050 |
| 060 |
| 065 |
| 070 |
| 080 |
| 085 |
| 090 |
| 095 |
| 096 |
| 105 |
| 106 |
| 107 |
| 110 |
| 112 |
| 115 |
| 121 |
| 125 |
| 130 |
| 135 |
| 136 |
| 140 |
| 142 |
| 145 |
| 150 |
| 160 |
| 165 |
+-----+
40 rows in set (0.00 sec)

そして2つ目のSQL

select a.no as no 
from tbl_a as a 
inner join tbl_b as b 
on a.j_num = b.j_num 
inner join tbl_c as c 
on a.no = c.no 
where c.no <> '141' 
and b.j_num = '5432456782';

この結果が

+-----+
| eno |
+-----+
| 012 |
| 130 |
+-----+

とします。
結果１－結果２を得ようと次のSQLを組みましたがうまく結果が得られません。

select a.no as no 
from tbl_a as a 
inner join tbl_b as b 
on a.j_num = b.j_num 
inner join tbl_c as c 
on a.no = c.no 
where c.no <> '141' 
and b.j_num = '5432456782'
and not exists(
 select *
 from tbl_d as d 
 inner join tbl_e as e 
 on e.no = d.no 
 where e.auth1 = '0'
 and e.auth1 <> '1');

希望する結果
| no  |
| 001 |
| 003 |
| 007 |
| 008 |
| 010 |
| 011 |
| 015 |
| 016 |
| 020 |
| 025 |
| 030 |
| 035 |
| 045 |
| 050 |
| 060 |
| 065 |
| 070 |
| 080 |
| 085 |
| 090 |
| 095 |
| 096 |
| 105 |
| 106 |
| 107 |
| 110 |
| 112 |
| 115 |
| 121 |
| 125 |
| 135 |
| 136 |
| 140 |
| 142 |
| 145 |
| 150 |
| 160 |
| 165 |
+-----+

（追記）希望する結果を記述しました。

とするとempty となってしまいます。
どこがおかしいのか分からず困っています。
ご助言、ご指導いただければと思います。
宜しくお願いいたします。

Comments

[hihijiji]

2つ目のSQLの結果の '012', '130' は一つ目のSQLの結果に含まれてますが？

[hidepon]

この場合ってEMPTY　になるのが正解なのでしょうか？この時の対処法ってPHP側でするのでしょうか？よくわかっていなくて困っています。SQLで解決できる方法があれば教えてください

[hihijiji]

2つ目のSQLの結果のなかで、1つ目のSQLに含まれないものが欲しいのですね？でしたらそのように書いてあります。

[hidepon]

やりたいこととしては結果の引き算をしたいんです。not in のほうがいいんでしょうか？

[hihijiji]

質問欄に希望する出力結果を書いてください。

[hidepon]

希望する結果を追記しました。宜しくお願いいたします。

Answer 1

not exists を使う場合は、相関サブクエリにする必用があります。
サブクエリのWhere句にメインの主キーと関連付ける条件を追加する。
そうしないと、サブクエリのレコードは常に存在するので、not exists は常にFalse、よって結果はemptyです。

下記などを参照してサブクエリの抽出条件を考えてみてください。

【 SQL 備忘録 】相関サブクエリ ～ 理解に挫折したとき & 忘れたときに 訪れると すごくわかりやすい入門者向け 解説ウェブサイトへのインデックス - Qiita

ただ、相関サブクエリは複雑になるし、重いので、今回の要件には適していないと思います。

Not In を使うとシンプルに記述できるが、重いらしい。

両者を Left Join で結合して、2つ目のSQLのキーフィールドがNullでないものを抽出するという方法もあります。こちらの方が軽いかも知れません。インデックスの設定にもよると思いますので、実際に試してみて、決めてください。

単純化したモデルでのSQL例を提示しておきますので、参考にして実際のSQLを記述してください。

tbl_a (id)
tbl_b (id)
tbl_a から tbl_b の id を除いたものを抽出。

相関サブクエリ

select a.id
from tbl_a as a
not exists(
 select * from tbl_b as b
 where a.id = b.id
 );

not in 使用

select id
from tbl_a
id not in (
 select id from tbl_b
 );

left join 使用

select a.id
from
 tbl_a as a
 left join
 tbl_b as b
 on a.id = b.id
where
 b.id is null;

Answer 2

書かれたSQLの頭とexistsの中が反対になっているだけです。

環境が無いので未チェックですがだいたいこんな感じで

SELECT d.no
FROM tbl_d AS d 
INNER JOIN tbl_e AS e 
ON e.no = d.no 
WHERE e.auth1 = '0' AND e.auth1 <> '1'
    AND NOT EXISTS
(
	SELECT 1
	FROM tbl_a as a 
	INNER JOIN tbl_b as b 
	ON a.j_num = b.j_num 
	INNER JOIN tbl_c as c 
	ON a.no = c.no 
	WHERE c.no <> '141' 
	AND b.j_num = '5432456782'
	AND a.no = d.no
    LIMIT 1
)

Comments

[hidepon]

ご返答有難うございます。逆にしても同じ結果になります・・・orz