我在MySQL中创建了一个相对简单的查询,根据名字和姓氏的匹配情况,在三个表上给我一个JOIN
。从那时起,我想写另一个查询,只向我显示没有从JOIN
中匹配的记录,但我不太清楚如何做到这一点。我假设它必须使用一个子查询,包括NOT IN
和我的原始查询,但我无法获得它来给我想要的结果。
这是我试图提出的部分功能正常的工作:
SELECT *,
if(t2.first=t1.first AND t2.last=t1.last, "Match", "No Match") AS "t2 Match",
if(t3.first=t1.first AND t3.last=t1.last, "Match", "No Match") AS "t3 Match"
FROM t1
LEFT JOIN t2 ON t2.first=t1.first AND t2.last=t1.last
LEFT JOIN t3 ON t3.first=t1.first AND t3.last=t1.last
WHERE if(t2.first=t1.first AND t2.last=t1.last, "Match", "No Match")="No Match"
OR if(t3.first=t1.first AND t3.last=t1.last, "Match", "No Match")="No Match";
我觉得这是一件相当简单和直接的事情,但我没有得到正确的结果。有人能帮忙吗?
谢谢!
不匹配意味着t2
(或t3
)列在结果中填充了Nulls。因此,您可以使用IS NULL
检查:
SELECT t1.*
FROM t1
LEFT JOIN t2 ON t2.first = t1.first AND t2.last = t1.last
LEFT JOIN t3 ON t3.first = t1.first AND t3.last = t1.last
WHERE t2.first IS NULL
OR t3.first IS NULL ;
你是对的,你也可以使用NOT IN
编写查询(警告:只有在连接列不可为null的情况下。否则你可能会有意外的结果):
SELECT t1.*
FROM t1
WHERE (first, last) NOT IN
( SELECT first, last FROM t2 )
OR (first, last) NOT IN
( SELECT first, last FROM t3 )
;
或使用NOT EXISTS
:
SELECT t1.*
FROM t1
WHERE NOT EXISTS
( SELECT 1
FROM t2
WHERE t1.first = t2.first
AND t1.last = t2.last
)
OR NOT EXISTS
( SELECT 1
FROM t3
WHERE t1.first = t3.first
AND t1.last = t3.last
) ;