Mysql, as 1 query, if row does not exist, do other query

Question

For a preferences module I have "system defaults", and "user preferences".
If there is no personal/user preference stored, then use the system default values instead.

Here is my system preferences table:

mysql> desc rbl;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| id          | varchar(3)          | NO   | PRI |         |       | 
| rbl_url     | varchar(100)        | NO   |     |         |       | 
| description | varchar(100)        | NO   |     |         |       | 
| is_default  | tinyint(1) unsigned | YES  |     | 1       |       | 
+-------------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Example data from system prefs:

mysql> select * from rbl;
+----+----------------------+------------------------------+------------+
| id | rbl_url              | description                  | is_default |
+----+----------------------+------------------------------+------------+
| 1  | sbl-xbl.spamhaus.org | Spamhaus SBL-XBL             |          1 | 
| 2  | pbl.spamhaus.org     | Spamhaus PBL                 |          1 | 
| 3  | bl.spamcop.net       | Spamcop Blacklist            |          1 | 
| 4  | rbl.example.com      | Example RBL - not functional |          0 | 
+----+----------------------+------------------------------+------------+

... and Query for system defaults:

mysql> SELECT rbl_url FROM rbl WHERE is_default='1';
+----------------------+
| rbl_url              |
+----------------------+
| sbl-xbl.spamhaus.org | 
| pbl.spamhaus.org     | 
| bl.spamcop.net       | 
+----------------------+
3 rows in set (0.01 sec)

So far so good. OK. Now I need a user preferences table, and I came up with this:

mysql> desc rbl_pref;
+-----------+-----------------------+------+-----+---------+----------------+
| Field     | Type                  | Null | Key | Default | Extra          |
+-----------+-----------------------+------+-----+---------+----------------+
| id        | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment | 
| domain_id | mediumint(8) unsigned | NO   |     | NULL    |                | 
| rbl_id    | tinyint(1) unsigned   | NO   |     | NULL    |                | 
+-----------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

(FYI - A "user" is represented by "domain_id". )

Let's view the preferences of a specific user who has personalized preferences saved:

mysql> select * from rbl_pref where domain_id='2277';
+----+-----------+--------+
| id | domain_id | rbl_id |
+----+-----------+--------+
|  4 |      2277 |      1 | 
|  5 |      2277 |      2 | 
|  6 |      2277 |      4 | 
+----+-----------+--------+
3 rows in set (0.00 sec)

... again, but in a simpler format:

mysql> SELECT rbl.rbl_url FROM rbl_pref,rbl 
       WHERE rbl_pref.rbl_id=rbl.id AND domain_id='2277';
+----------------------+
| rbl_url              |
+----------------------+
| sbl-xbl.spamhaus.org | 
| pbl.spamhaus.org     | 
| rbl.example.com      | 
+----------------------+
3 rows in set (0.00 sec)

.. so far so good. If a user has stored a preference, a result is found.

The problem example now is, user 1999 has no custom preferences. In place of the "Empty set" result, I want the system defaults.

mysql> SELECT rbl.rbl_url FROM rbl_pref,rbl 
       WHERE rbl_pref.rbl_id=rbl.id AND domain_id='1999';
Empty set (0.00 sec)

I was excited to find a very similar question: mysql if row doesn't exist, grab default value However after a couple of days trial and error and documentation review, I could not translate that answer over to here.

Like the above question, this must be done as a single MySQL query. I am not actually making this query from PHP, but from Exim macros (and it is a very picky language... best to feed it "one liners" as variable assignments, as I try to do here.. )

UPDATE: Tried one type of a UNION query suggested by @Biff McGriff, below. The table did not display in my comment reply, so here it is again:

mysql> SELECT rbl.rbl_url FROM rbl_pref,rbl 
       WHERE rbl_pref.rbl_id=rbl.id AND domain_id='2277' 
       UNION SELECT rbl_url FROM rbl WHERE is_default='1';
+----------------------+
| rbl_url              |
+----------------------+
| sbl-xbl.spamhaus.org | 
| pbl.spamhaus.org     | 
| rbl.example.com      | 
| bl.spamcop.net       | 
+----------------------+
4 rows in set (0.00 sec)

As you can see above, user 2277 did not opt in to rbl_id 3 (bl.spamcop.net), but that's showing up anyways.

What my UNION query seems to be doing is combining the result set. So user_pref acts as "in addition to" global defaults, and I was assuming/expecting I would get a result set matching either half of the query.

So my question now is, is it better (or possible, how) to solve this as "either result set" (either subquery on either side of the UNION)? OR do I really need a new field on rbl_pref, called for example "enabled". The latter seems to be more correct - that I need something in rbl_pref to explicitly designate opt-in or opt-out (other than the implicit "that pref is not here - no rbl_id=3 - in the over ridden user result SET")

UPDATE: All set, thanks @Imre L, and everyone else. I learned something through this example.

Answer 1

NOTE: you have to enter the domain_id in two places.

SELECT rbl.rbl_url FROM rbl
  JOIN rbl_pref ON rbl_pref.rbl_id=rbl.id AND domain_id=2277
UNION 
SELECT rbl.rbl_url FROM rbl 
 WHERE rbl.is_default 
   AND NOT EXISTS (SELECT 1 FROM rbl_pref WHERE domain_id=2277 LIMIT 1)
;

Now one or the other side of UNION will be optimized away with impossible where

You also should not use varchar(3) for rbl.id but some sort of integer and preferable same type as rbl_pref.rbl_id for which tinyint is too tiny

and when you compare integers fields in sql code domain_id='2277' you should not use ' or " around constants integers. You can get away whith it mostly but sometimes it may confuse mysql optimizer.

Also for optimal performance and consistency i suggest you the add the index:

ALTER TABLE rbl_pref
    ADD UNIQUE INDEX ux_domain_rbl (domain_id, rbl_id);

Answer 2

You should be able to use a left join and then coalesce the user's field with the default field.