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?

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.

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

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)

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