MySQL select rows that do not have matching column in other table

Question

I can't seem to figure this out so far. I am trying to join two tables and only select the rows in table A that do not have a matching column in table B. For example, lets assume we have a users table and a sent table.

users table has the following columns: id, username
sent table has the following columns: id, username

I want to select all rows from users where username does not exist in sent table. So, if tom is in users and in sent he will not be selected. If he is in users but not in sent he will be selected. I tried this but it didn't work at all:

SELECT pooltest.name,senttest.sentname  FROM pooltest,senttest  WHERE pooltest.name != senttest.sentname 

Answer 1

Typically, you would use NOT EXISTS for this type of query

SELECT p.Name FROM   pooltest p WHERE  NOT EXISTS (SELECT s.Name                    FROM   senttest s                    WHERE  s.Name = p.Name) 

An alternative would be to use a LEFT OUTER JOIN and check for NULL

SELECT p.Name FROM   pooltest p        LEFT OUTER JOIN senttest s ON s.Name = p.Name WHERE  s.Name IS NULL 

Note that the implicit join syntax you are using is considered obsolete and should be replaced with an explicit join.