SQL query to get data if fields match or null if they dont-CodePudding

I need to get a list of users with their corresponding bed_id in case user has bed_id and fill with null if user doesn't have bed_id since there will always been more users than beds.

When I try this:

select users.*, beds.bed_id from users, beds where users.user_id = beds.user_id;

I got a list of users who have assigned bed_id alone but I need the complete list of users with their bed_id or null.

USERS
--------------
| user_id    |
| name       |
| field1     |
| field2     |
| ...        |
--------------

BEDS
--------------
| bed_id     |
| user_id    |
| room_id    |
| field1     |
| ...        |
--------------

Thanks for the great support you always share here.

CodePudding user response：

Use LEFT OUTER JOIN:

select users.*,
       beds.bed_id
from   users
       LEFT OUTER JOIN beds
       ON (users.user_id = beds.user_id);