Menu
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?
681
The JOIN or INNER JOIN does not return any non-matching rows at all. It returns only the rows that match in both of the tables you join. If you want to get any unmatched rows, you shouldn't use it. The LEFT JOIN and the RIGHT JOIN get you both matched and unmatched rows.
LEFT JOIN is used; this will return ALL rows from Table1 , regardless of whether or not there is a matching row in Table2 .
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
