A Join that exlcudes if data does exist

[itxtme]

I have two tables that I want to use a join to exclude data if it exists in the other table. I basically want a list of people from table B that dont exist in table A

If A.account_id exists as B.account_id then dont want it to return in the result set. I have done it as an inclusive query using an INNER JOIN. What I need however is for it to exclude them not include them.

My thoughts are to do a LEFT JOIN and then use PHP to exclude the result sets that have a B.account_id value. This seems awfully expensive if a query can return only the required accounts instead. (there could be thousands!)

A push in the right direction would be greatly appreciated

[guelphdad]

SELECT
 b.account_id
 larry,
 curly,
 moe
FROM 
 table b
WHERE
 b.account_id
NOT IN
 (SELECT a.account_id FROM table a)

[Truffle]

SELECT id,name,number FROM goodcustomers

UNION

SELECT id,name,number FROM badcustomers WHERE joinme NOT IN (SELECT id FROM goodcustomers)

[itxtme]

I love this place, thanks guys. Just when you feel you are starting to gain good developer skills something like this pops up!

[Old Pedant]

> I basically want a list of people from table B that dont exist in table A

Your idea of a left join is right (in addition to Bubikol's method). But you don't need to use PHP at all.

SELECT B.* 
FROM B LEFT JOIN A ON B.accountid = A.accountid
WHERE A.accountid IS NULL

This will be a bit more efficient than the NOT IN scheme when you have large tables. (If MySQL were a bit smarter, the two would be identical in performance--as they are with SQL Server for example--but it's not. The LEFT JOIN has the advantage that MySQL can optimize the query better especially if both accountid fields are indexed.)