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!
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.
The NOT IN keyword should be helpful :
SELECT username
FROM users
WHERE username NOT IN (
SELECT username
FROM recentusers
)
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
SELECT * FROM users
LEFT JOIN recentusers USING (username)
WHERE recentusers.username IS NULL;
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