Basic MySQL question: how to get rows from one table that are not in another table?

Question

I have a list of usernames (with a column called "username") + other info in my "users" table.

I also have another list of usernames (with a column called "username") in my smaller "recentusers" table.

How can I create a mysql query to get all the usernames that are in the "users" table but not in the "recentusers" table?

I know it is probably very simple, but any help would be appreciated!

Answer 1

select username from users where username not in (select username from recentusers)

Then after the first username add in the other information you want to select as well.

As pointed out by OMG Ponies, this query will run as fast as using Left Join or Is null.

Answer 2

The NOT IN keyword should be helpful :

SELECT username
FROM users
WHERE username NOT IN (
    SELECT username
    FROM recentusers
)

Answer 3

Joins should be faster than subqueries, though I do not really know if mysql supports this.

select * from users
left outer join recentusers
on users.key = recentusers.key

Answer 4

SELECT * FROM users
    LEFT JOIN recentusers USING (username) 
    WHERE recentusers.username IS NULL;

Answer 5

You can also use a left join (which will perform faster than a subquery):

SELECT
    users.username
FROM
    users LEFT JOIN recentusers
        ON users.username = recentusers.username
WHERE
    recenterusers.username is null