在mysql中查找与另一个表w.r.t不匹配的行

Question

我有两个表'chem_qc_stage1'和'chem_qc_stage2'。我想通过使用以下查询显示'chem_qc_stage1'中而不是'chem_qc_stage2'中的行:

select * 
from (select chem_name,raw_mat,blnd_no 
from chem_qc_stage1 
where m_m_no='318') as tmp 
where not exists (select chem_name,raw_mat,blnd_no 
from chem_qc_stage2 
where m_m_no='318');

得到如下输出:

Empty set (0.01 sec)

，但是每个查询给出了以下各自的输出:

select chem_name,raw_mat,blnd_no from chem_qc_stage2 where m_m_no='318';
+-----------+---------+---------+
| chem_name | raw_mat | blnd_no |
+-----------+---------+---------+
| HTPB      | YY      | TT      |
+-----------+---------+---------+
1 row in set (0.00 sec)
select chem_name,raw_mat,blnd_no from chem_qc_stage1 where m_m_no='318';
+-----------+---------+---------+
| chem_name | raw_mat | blnd_no |
+-----------+---------+---------+
| HTPB      | YY      | TT      |
| HTPB      | YY      | ZZ      |
| HTPB      | FF      | FF      |
+-----------+---------+---------+
3 rows in set (0.00 sec)

我知道如果我在where子句中使用'raw_mat'和'blnd_no'，那么它将给我完美的输出。但是用户事先并不知道这两个属性的值。

这里我附加了我的表创建查询:

create table chem_qc_stage1 (chem_name varchar(20),m_m_no varchar(10),raw_mat varchar(10),blnd_no varchar(10));
create table chem_qc_stage2 (chem_name varchar(20),m_m_no varchar(10),raw_mat varchar(10),blnd_no varchar(10));
insert into chem_qc_stage1 values('HTPB','318','YY','TT');
insert into chem_qc_stage1 values('HTPB','318','YY','ZZ');
insert into chem_qc_stage1 values('HTPB','318','FF','FF');
insert into chem_qc_stage2 values('HTPB','318','YY','TT');

请帮助我找到解决方案，将给我不匹配的行'chem_qc_stage1'表;有时，'chem_qc_stage2'表也可能为空，那么查询也应该给出不匹配的行。

Answer 1

您是在寻找这样的not exists吗?

select s1.*
from chem_qc_stage1 s1
where not exists (select 1
from chem_qc_stage2 s2
where s2.chem_name = s1.chem_name and
s2.raw_mat = s1.raw_mat and
s2.blnd_no = s1.blnd_no and
s2.m_m_no = s1.m_m_no
);

也可以使用元组编写，假设没有一个值是NULL:

select s1.*
from chem_qc_stage1 s1
where (s1.chem_name, s1.raw_mat, s1.blnd_no, s1.m_m_no) not in
(select s2.chem_name, s2.raw_mat, s2.blnd_n, s2.m_m_no
from chem_qc_stage2 s2
);