MySQL加入不存在的地方

本文介绍了MySQL加入不存在的地方的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

I have a MySQL query that joins two tables

• 选民
• 家庭

他们加入 voters.household_id 和 household.id.

现在我需要做的是修改它，将选民表连接到第三个称为消除的表，沿着 voter.id 和 elimination.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. However 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，即使没有匹配也会返回行，然后你可以只选择通过检查 NULLs 没有匹配的行.

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.