Query over 2 tables with no match in 2nd table

Question

I have got 2 tables:

users (user-id, user-name)  
devices (device-id, user-id, device-name)

Both tables are linked via the foreign key user-id in the table devices.
One user can have n devices or NO device.

I am looking for those users who do NOT have a device.
In other words, users for whom there is NO user-id in the devices table.

What is the SQL statement?

The opposide works fine:

SELECT users.user-name, devices.device-name 
FROM users,devices WHERE users.user-id=devices.user-id;

Try using JOIN not comma to separate tables. Then do a join on the field and you can add a where column (you want to show up in 2nd table) is NULL. — Brad, Aug 15 '22 at 17:02
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — Igor, Aug 15 '22 at 17:12

score 1 · Answer 1 · answered Aug 15 '22 at 17:08

1

SELECT U.USER_ID,U.USER_NAME
FROM USERS U
WHERE NOT EXISTS
(
   SELECT 1 FROM DEVICES D WHERE U.USER_ID=D.USER_ID
)

answered Aug 15 '22 at 17:08

Sergey

nbk · Accepted Answer · 2022-08-16T07:18:39.690

1

You can use a LEFT JOIN to get user that have no device, they would have a deviceid NULL

SELECT users.user-name
FROM users LEFT JOIN devices ON users.user-id=devices.user-id
WHERE device-id IS NULL;

edited Aug 16 '22 at 07:18

answered Aug 15 '22 at 17:13

nbk

score 0 · Answer 3 · answered Aug 15 '22 at 17:12

0

This will list out all users that does not have devices.

SELECT users.user-name 
FROM users
WHERE users.user-id NOT in 
  (SELECT devices.user-id
    FROM  devices)

answered Aug 15 '22 at 17:12

jose_bacoy

3 Answers3