SQL query to fetch unmatched records from two tables

Question

I have two tables userprofile and deviceid.

• userprofile contains columns username, studentname
• deviceid contains columns username, device id

I want to fetch username records from userprofile which are not in deviceid (username).

My query is

SELECT 
    userprofile.username, userprofile.studentname 
FROM 
    userprofile
LEFT JOIN 
    deviceid ON deviceid.username = userprofile.username
WHERE 
    deviceid.username IS NULL;

It's not fetching any values.

Solution 1

Your wording was actually the exact way to go:

I want to fetch username records from userprofile which are not in deviceid(username)

Now, we just need to put this in to SQL:

SELECT username 
FROM   userprofile
WHERE  username NOT IN (SELECT username FROM deviceid)

Solution 2

This is how I usually do it

select * from userprofile a where not exists (select 1 from device b where a.username = b.username)

Comments

[Dan Bracuk]

This is much more complicated than necessary.