MySQL select rows that do not have matching column in other table
Solution 1
Try this SQL:
SELECT users.username
FROM users
LEFT JOIN sent ON sent.username = users.username
WHERE sent.username IS NULL;
The better way in my opinion would be:
SELECT users.username
FROM users
LEFT JOIN sent ON sent.id = users.id
WHERE sent.id IS NULL;
As both the id fields, would be indexed (primary key I would have thought) so this query would be better optimised than the first one I suggested.
However you may find my first suggestion better for you, it depends on what your requirements are for your application.
Solution 2
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.
xendi
Updated on October 27, 2020Comments
-
xendi over 3 years
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
whereusername
does not exist insent
table. So, iftom
is inusers
and insent
he will not be selected. If he is inusers
but not insent
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
-
xendi about 12 yearsthrough some other help I also found this to work: SELECT * FROM pooltest LEFT JOIN senttest ON pooltest.name = senttest.sentname WHERE senttest.sentname IS NULL