CODEWITHSUNDEEP
phpmysql
chris
chris

Reputation: 21197

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

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!

Upvotes: 1

Views: 159

Answers (5)

Wookai
Wookai

Reputation: 21723

The NOT IN keyword should be helpful :

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

Upvotes: 2

Corey Sunwold
Corey Sunwold

Reputation: 10254

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.

Upvotes: 4

jonstjohn
jonstjohn

Reputation: 60266

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

Upvotes: 1

jcdyer
jcdyer

Reputation: 19145

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

Upvotes: 1

Oliver Friedrich
Oliver Friedrich

Reputation: 9240

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

Upvotes: 0

Related Questions