CODEWITHSUNDEEP
mysqljoinduplicatesunique
xendi
xendi

Reputation: 2522

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

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

Upvotes: 40

Views: 59823

Answers (3)

Upendra Singh
Upendra Singh

Reputation: 1

May be this one can help you ....

I had also the same problem but Solved using this this query

INSERT INTO tbl1 (id,name) SELECT id,name from tbl2 where (name) not in(select name from tbl1);

hope this one will solve your problem

Upvotes: -3

Haroon
Haroon

Reputation: 1155

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.

Upvotes: 30

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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.

Upvotes: 65

Related Questions