MySQL MySQL加入不存在的地方

MySQL Join Where Not Exists

提问by gsueagle2008

I have a MySQL query that joins two tables

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

• Voters
• Households

• 选民
• 家庭

They join on voters.household_idand household.id.

他们加入voters.household_id和household.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.idand 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.

现在我需要做的是修改它，其中选民表连接到第三个表，称为消除，沿voter.id和elimination.voter_id。然而，问题是我想排除选民表中在消除表中有相应记录的任何记录。

How do I craft a query to do this?

我如何制作一个查询来做到这一点？

This is my current query:

这是我当前的查询：

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 

回答by NickZoic

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.

我可能会使用 a LEFT JOIN，即使没有匹配，它也会返回行，然后您可以通过检查NULLs来仅选择不匹配的行。

So, something like:

所以，像这样：

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.

这是否比使用子查询效率更高或更低取决于优化、索引、每个选民是否可能有不止一个淘汰，等等。

回答by Ian Clelland

I'd use a 'where not exists' -- exactly as you suggest in your title:

我会使用“不存在的地方”——正如你在标题中所建议的那样：

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%'

AND NOT EXISTS (
  SELECT * FROM `elimination`
   WHERE `elimination`.`voter_id` = `voter`.`ID`
)

ORDER BY `Last_Name` ASC
LIMIT 30

That may be marginally faster than doing a left join (of course, depending on your indexes, cardinality of your tables, etc), and is almost certainly muchfaster than using IN.

这可能是稍快比做左连接（当然，这取决于您的索引，你的表的基数，等等），并且几乎可以肯定得多比使用更快。

回答by Dumidu Madushanka

There are three possible ways to do that.

有三种可能的方法来做到这一点。

1. Option

   SELECT  lt.* FROM    table_left lt
   LEFT JOIN
       table_right rt
   ON      rt.value = lt.value
   WHERE   rt.value IS NULL
   

2. Option

   SELECT  lt.* FROM    table_left lt
   WHERE   lt.value NOT IN
   (
   SELECT  value
   FROM    table_right rt
   )
   

3. Option

   SELECT  lt.* FROM    table_left lt
   WHERE   NOT EXISTS
   (
   SELECT  NULL
   FROM    table_right rt
   WHERE   rt.value = lt.value
   )
   

1. 选项

   SELECT  lt.* FROM    table_left lt
   LEFT JOIN
       table_right rt
   ON      rt.value = lt.value
   WHERE   rt.value IS NULL
   

2. 选项

   SELECT  lt.* FROM    table_left lt
   WHERE   lt.value NOT IN
   (
   SELECT  value
   FROM    table_right rt
   )
   

3. 选项

   SELECT  lt.* FROM    table_left lt
   WHERE   NOT EXISTS
   (
   SELECT  NULL
   FROM    table_right rt
   WHERE   rt.value = lt.value
   )