MySQL JOIN ignoring NULL values

Question

I have the following SQL with a JOIN, if I run this, it ignores records that have NULL values in the group_id in the users table. Therefore, I get less results

SELECT u.user_id, u.email, g.group_name 
FROM users u 
JOIN groups g USING (group_id) 
WHERE u.year=2017

However, If I run it without the JOIN like this:

SELECT u.user_id, u.email 
FROM users u 
WHERE u.year=2017

Then it gives all the users I need. Some of the users do not have the group_id set as this is not a required field. Is there a way to JOIN the NULL values too? Do I nee to have a NULL value in the groups table? This would not be possible as my group_id field in the groups table is NOT NULL and PRIMARY KEY

cn0047 · Accepted Answer · 2017-11-20 21:44:01Z

6

You have to use LEFT JOIN:

SELECT u.user_id, u.email, g.group_name 
FROM users u 
LEFT JOIN groups g USING (group_id) 
WHERE u.year=2017

answered Nov 20, 2017 at 21:44

cn0047

17.2k7 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M Khalid Junaid · Accepted Answer · 2017-11-20 21:45:19Z

1

Use left join if you want to get all users whether they have group_id or not

SELECT u.user_id, u.email, g.group_name 
FROM users u 
LEFT JOIN groups g USING (group_id) 
WHERE u.year=2017

or using your's version tweak on clause

SELECT u.user_id, u.email, g.group_name 
FROM users u 
JOIN groups g ON(u.group_id = g.group_id OR u.group_id IS NULL) 
WHERE u.year=2017

answered Nov 20, 2017 at 21:45

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Collectives™ on Stack Overflow

MySQL JOIN ignoring NULL values

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related