CODEWITHSUNDEEP
mysqlleft-join
Qiao
Qiao

Reputation: 17049

Select all rows that are absent in other table

There are two tables: users1 and users2. They both have name column. I need select all users from users1 that are absent in users2 table.

I can only select all users and iterate them by PHP, checking every in second table.

Is there a way to do it by SQL?

Upvotes: 1

Views: 1682

Answers (4)

regality
regality

Reputation: 6554

select * from users1 where name not in (select name from users2);

Upvotes: 0

awm
awm

Reputation: 6570

SELECT `users1`.* FROM `users1` LEFT JOIN `users2` USING (`name`)
        WHERE `users2`.`name` IS NULL

For maximum performance, be sure you have an index defined on name in both tables.

This can also be done with a subquery (as others have pointed out), but a join will execute much faster.

Upvotes: 6

Nikoloff
Nikoloff

Reputation: 4160

SELECT * FROM users1 WHERE name NOT IN(SELECT name FROM users2)

Depending on your RMDB and data in this tables, you might want to turn all names to lower case:

SELECT * FROM users1 WHERE LOWER(name) NOT IN(SELECT LOWER(name) FROM users2)

Upvotes: 0

ShareChiWai
ShareChiWai

Reputation: 298

Maybe you can try to write a sub query like

SELECT *
FROM Users1
WHERE Username NOT IN
(SELECT Username FROm Users2)

Hope this could help

Upvotes: 2

Related Questions