SQL join on one-to-many relation where none of the many match a given value

Question

Say I have two tables

User
-----
id
first_name
last_name

User_Prefs
-----
user_id
pref

Sample data in User_Prefs might be

user_id | pref
  2     |  SMS_NOTIFICATION
  2     |  EMAIL_OPT_OUT
  2     |  PINK_BACKGROUND_ON_FRIDAYS

And some users might have no corresponding rows in User_Prefs.

I need to query for the first name and last name of any user who does NOT have EMAIL_OPT_OUT as one of their (possibly many, possibly none) User_Pref rows.

SELECT DISTINCT u.* from User u
LEFT JOIN User_Prefs up ON (u.id=up.user_id)
WHERE up.pref<>'EMAIL_OPT_OUT'

gets me everyone who has at least one row that isn't "EMAIL_OPT_OUT", which of course is not what I want. I want everyone with no rows that match "EMAIL_OPT_OUT".

Is there a way to have the join type and the join conditions filter out the rows I want to leave out here? Or do I need a sub-query?

Answer 1

I personally think a "where not exists" type of clause might be easier to read, but here's a query with a join that does the same thing.

select distinct u.* from User u
left join User_Prefs up ON u.id = up.user_id and up.pref = 'EMAIL_OPT_OUT'
where up.user_id is null

Answer 2

Why not have your user preferences stored in the user table as boolean fields? This would simplify your queries significantly.

SELECT * FROM User WHERE EMAIL_OPT_OUT = false