MySQL连接不存在的地方 [英] MySQL Join Where Not Exists

问题描述

我有一个连接两个表的MySQL查询

I have a MySQL query that joins two tables

• 投票人

• 家庭 他们加入了选民.household_id和家庭.

• Voters

• Households they join on voters.household_id and household.id

现在，我需要做的就是将选民表与第三个称为消除"的表联接在一起，沿着表决器.id和消除.voter_id进行修改，但是要抓住的是我想排除选民中的任何记录在消除表中具有相应记录的表.我该如何制作查询来做到这一点?

Now what i need to do is to modify it where the voter table is joined to a third table called elimination, along voter.id and elimination.voter_id, how ever the catch is that i want to exclude any records in the voter table that have a corresponding record in the elimination table. how do i craft a query to do this?

这是我当前的查询

SELECT `voter`.`ID`, `voter`.`Last_Name`, `voter`.`First_Name`,
       `voter`.`Middle_Name`, `voter`.`Age`, `voter`.`Sex`,
       `voter`.`Party`, `voter`.`Demo`, `voter`.`PV`,
       `household`.`Address`, `household`.`City`, `household`.`Zip`
FROM (`voter`)
JOIN `household` ON `voter`.`House_ID`=`household`.`id`
WHERE `CT` = '5'
AND `Precnum` = 'CTY3'
AND  `Last_Name`  LIKE '%Cumbee%'
AND  `First_Name`  LIKE '%John%'
ORDER BY `Last_Name` ASC
LIMIT 30 

推荐答案

我可能会使用Left Join，即使没有匹配项，它也会返回行，然后您可以通过检查是否只有不匹配的行来选择空值.

I'd probably use a Left Join, which will return rows even if there's no match, and then you can select only the rows with no match by checking for NULLs.

所以，像这样:

SELECT V.*
FROM voter V LEFT JOIN elimination E ON V.id = E.voter_id
WHERE E.voter_id IS NULL

这是否比使用子查询更有效率取决于优化，索引，每个选民是否可以消除多个投票等.

Whether that's more or less efficient than using a subquery depends on optimization, indexes, whether its possible to have more than one elimination per voter, etc.

这篇关于MySQL连接不存在的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！