MySQL Join Where Not Exists

Question

I have a MySQL query that joins two tables    Voters Households   They join on voters household id and 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 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   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

User · Answer

There are three possible ways to do that.

Option

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

Option

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

Option

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

User · Answer

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

User · Answer

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 much faster than using IN

[mysql] MySQL Join Where Not Exists

Examples related to mysql

Examples related to join

Examples related to not-exists