Select where null only if a non-null value doesn't exist?

Question

I have a simple user preferences table that looks like this:

   id   |   user_id   |   preference_name   |   preference_value

What makes this table unique though is if the user_id field is null, it means it is the default value for that preference. I'm trying to get all the preferences for a user and use the default value only if an actual value hasn't been specified for that user.

So basically I need to:

SELECT * FROM user_preferences WHERE user_id = {userIdVar} OR user_id IS NULL;

BUT, I want to throw out a user_id is null result if there is another row in the result set with the same preference_name and a value for user_id.

Is there a way to do this with a single SQL query or should I do this in code?

score 4 · Answer 1 · answered Aug 29 '13 at 21:55

Use NOT EXISTS:

SELECT up1.* 
FROM   user_preferences up1 
WHERE  ( NOT EXISTS(SELECT 1 
                    FROM   user_preferences up2 
                    WHERE  user_id = {userIdVar}) 
         AND user_id IS NULL ) 
        OR ( user_id = {userIdVar} );

score 1 · Answer 2 · 2013-08-29T22:41:49.693

There are various ways you can do this, but if all preferences have a default value, or you have a complete list of preferences somewhere else, I would do it like this:

select
  default_preferences.preference_name,
  coalesce(
    real_user_preferences.preference_value,
    default_preferences.preference_value) as preference_value
from
  (select * from user_preferences where user_id is null)
  as default_preferences
left join
  (select * from user_preferences where user_id = @user_id)
  as real_user_preferences
on
  real_user_preferences.preference_name = default_preferences.preference_name

You've tagged your question both MySQL and SQL Server, I don't know which dialect you're looking for. I know SQL Server accepts this syntax, but it might need some tweaking for MySQL.

Edit: funkwurm points out that subqueries make this likely to perform poorly on MySQL. If that turns out to be a problem, it can be rewritten without subqueries as

select
  default_preferences.preference_name,
  coalesce(
    real_user_preferences.preference_value,
    default_preferences.preference_value) as preference_value
from
  user_preferences as default_preferences
left join
  user_preferences as real_user_preferences
on
  real_user_preferences.preference_name = default_preferences.preference_name
  and real_user_preferences.user_id = @user_id
where
  default_preferences.user_id is null

Edit 2: if there are preferences that do not have a default value, the first version can be modified to use full join instead of left join, and take preference_name from either the defaults or the user-specific preferences, just like preference_value. However, the second version is not so easily modified.

In MySQL subqueries tend to be resource intensive, maybe SQL-Server has optimisation that makes that subqueries are preferred. I would opt for selecting directly `FROM user_preferences` in stead of a subquery and aliasing it so you can join it with itself for the default-value. — asontu, Aug 29 '13 at 22:17
@amaster507 I made one assumption, and mentioned that in my answer: "if all preferences have a default value". In your sample data, "pictures" has no default value. — , Aug 29 '13 at 22:23
@hvd Ok that is true, but I don't like assuming things... sorry I didn't read that part. — amaster, Aug 29 '13 at 22:26
MySQL does not have full or outer joins, you need to `UNION` it with a second query to get the preferences that don't have a default. — asontu, Aug 29 '13 at 22:44
@funkwurm Something like that will be needed anyway to avoid subqueries. And if the second part of the union takes care not to return results already present in the first part, `union all` can be used. But I'll not edit my answer to address that (for now, anyway), because if the query then becomes that complex, I'd sooner go with Tim Schmelter's approach. — , Aug 29 '13 at 23:09

score 0 · Answer 3 · answered Aug 29 '13 at 21:58

COALESCE returns the first non null values of the params provided: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

So if you grab the set of default preferences and JOIN them with the users preferences, you can use the COALESCE in your columns to populate the correct values.

amaster · Answer 4 · 2013-08-29T22:26:40.207

0

This should work to select the first row that is either NULL or set the the user_id variable where the user_id variable is preffered if both are set and then shows every preference_name only once.

SELECT
  *
FROM 
  (
    SELECT
      *
    FROM
      user_preferences
    WHERE
      user_id = {userIdVar} OR
      user_id IS NULL
    ORDER BY
      CASE WHEN user_id IS NULL THEN 1 ELSE 0 END
  ) sub_query
GROUP BY
  preference_name

SQL FIDDLE

edited Aug 29 '13 at 22:26

answered Aug 29 '13 at 22:02

amaster

1,915
5
25
51

Clever idea, but this is not guaranteed to work in MySQL, and will cause a hard error in SQL Server and most other database systems. Quoting from [Extensions to GROUP BY](https://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html): "The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. " – Aug 29 '13 at 22:05
@hvd I use similiar to this is MySQL and it always works. How I understand it, is that MySQL will group and use the first instance. If the subquery is ordered correctly then the query will always return the expected results... working on sql-fiddle now – amaster Aug 29 '13 at 22:10
It's entirely possible that for your specific version of MySQL, for your specific tables, it will always give you the results you want, because the query plan you get causes your query to be executed exactly the way you expect. But if the documentation explicitly states that it will indeterminately choose a value, and that your `ORDER BY` is not enough to make this query reliable, I would be very cautious about trying this on other databases, or other versions of MySQL. – Aug 29 '13 at 22:13
@hvd read further `Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.` to fix this order before the group by with a sub query – amaster Aug 29 '13 at 22:24
That is a very good point. It still says "the values chosen are indeterminate", and I do not see an exception why this would not apply here, but I agree that where the documentation mentions `ORDER BY`, that isn't intended to address how you are using it. – Aug 29 '13 at 22:28
@hvd this would make an interesting question to ask a MySQL expert. Maybe I will do some more research and post in SO. – amaster Aug 29 '13 at 22:31

Select where null only if a non-null value doesn't exist?

4 Answers4

Linked