How to write a query showing records from table A without a matching record in table B?

Question

Say I have two tables called A (fields: id, phase, name) and B(fields: id, AID, APHASE, void).

I need to show all records from A except for records where A.id = B.AID and A.phase = B.APHASE and void = 0.

Environment is MySQL.

Answer 1

fixed I guess :\

select * from A, B

where

not( A.id = B.AID and A.phase = B.APHASE and void = 0 )

Am getting old :|

Answer 2

SELECT  *
FROM    A
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    B
        WHERE   b.aid = a.id
                AND b.aphase = a.phase
                AND b.void = 0
        )

Note that this query will always return each rows from A exactly 1 or 0 times.

The LEFT JOIN can return rows from A many times if B (aid, aphase) is not UNIQUE.

These queries have different semantics, so choosing a correct one is a matter of validity, not performance.

As for performance, MySQL will always use A as a leading table since it's not capable of doing joins in other way than NESTED LOOPS.

Since EXISTS will always return as soon as it finds a first matching record, EXISTS query will always be more efficient than a LEFT JOIN (just because it returns at least not later than a LEFT JOIN), at expense of returning at most one record from A.

Answer 3

SELECT 
    id, phase, name 
FROM A 
WHERE NOT EXISTS 
    (SELECT id FROM B WHERE AID=A.id AND APHASE=A.phase AND void=0)

Answer 4

   SELECT *
     FROM `A`
LEFT JOIN `B`
       ON `A`.`id` = `B`.`id`
    WHERE NOT ( `A`.`id` = `B`.`AID` AND `A`.`phase` = `B`.`APHASE`
                AND `void` = 0 )

or:

   SELECT *
     FROM `A`
LEFT JOIN `B`
       ON NOT ( `A`.`id` = `B`.`AID`
                AND `A`.`phase` = `B`.`APHASE`
                AND `void` = 0 )

no guarantee the second one actually works, it just came to my mind