Get records from Table A, that are not in Table B

Question

I'm new to SQL, and havn't been able to get this SQL query right yet. I currently have:

SELECT * FROM tableA
LEFT OUTER JOIN tableB
ON tableA.`full_name` = tableB.`full_name`
WHERE tableB.`id` IS NULL

Both tables have records of people, complete with names and addresses. I need to get ALL the records for those who are in tableA, but not tableB. The diagram below is basically what I need:

The problem is that two people may have the same name, but different addresses. So ultimately, I need to get the records of ALL the people that are in tableA, excluding the duplicates that have duplicate names AND addresses.

Each table has columns as follows:

id,full_name,first_name,last_name,title,phone,address,city,state,postal_code

Answer 1

The following query will give you all the ids of people in tableA that are not in tableB based on full name and adress:

SELECT tableA.id FROM tableA
LEFT OUTER JOIN tableB
-- people are the same if fullname and adress match
ON tableA.`full_name` = tableB.`full_name`
   AND tableA.adress = tableB.adress
-- filter people that re in tableA only
WHERE tableB.`id` IS NULL
-- filter duplicates
GROUP BY tableA.id

You can easily edit this selet to include whatever information you need from tableA.

Answer 2

Since you're joining on two fields you're options are an ANTI-JOIN (Friederike S' answer) Not exists,

SELECT DISTINCT tablea.* 
FROM   tablea 
WHERE  NOT EXISTS (SELECT * 
                   FROM   tableb 
                   WHERE  tablea.`full_name` = tableb.`full_name`
                          AND tableA.adress = tableB.adress) 

DEMO

You can also use not in see (Christian Ammer's) answer

Another more obscure solution is to use the ALL keyword. It's very similar to NOT IN

SELECT DISTINCT tablea.* 
FROM   tablea 
WHERE 

( tablea.`full_name`  , tableA.address) 

  !=  ALL (SELECT tableb.`full_name`, tableB.address 
           FROM tableb)

DEMO