MySQL differences between two select queries

Question

I have two mysql queries that return a column of phone numbers. I want am trying to end up with a list of phone numbers that are in one list, but not in the other.

So the two queries I have are:

SELECT phone
FROM civicrm_phone phone
LEFT JOIN civicrm_participant participant 
    ON phone.contact_id = participant.contact_id
WHERE phone.is_primary = 1
    AND participant.id IS NULL

and

SELECT phone
FROM civicrm_phone phone
LEFT JOIN civicrm_participant participant 
    ON phone.contact_id = participant.contact_id
WHERE phone.is_primary = 1
    AND participant.id IS NOT NULL

And before anyone asks, the above two queries do not provide mutually exclusive results (based on using IS NULL and IS NOT NULL for the last WHERE statement), since we have related individuals in the database who use the same phone number, but do not necessarily all have a participant.id.

Thanks for any help.

Answer 1

The following query puts the second query in the WHERE clause for the comparison:

SELECT phone
FROM civicrm_phone phone
LEFT JOIN civicrm_participant participant ON phone.contact_id = participant.contact_id
WHERE phone.is_primary = 1
AND participant.id IS NULL and
    not exists (SELECT 1
                FROM civicrm_phone phone2
                LEFT JOIN civicrm_participant participant ON phone2.contact_id = participant.contact_id
                WHERE phone.is_primary = 1
                      AND participant.id IS NULL
                      and phone2.phone = phone.phone
              )

Normally in SQL, you would use NOT IN for this, but the NOT EXISTS with a correlated subquery is more efficient in MySQL.