Pick column with default value or column with user defined value if not null ?

Question

I'm working on a webapp with a set of data that users can browse and edit.

Users want to customize one of the fields that appear for each item, say "product_name".

Each product has a set of default values in a table, which is the same for all users:

The product table would be like (skipping syntax cruft, you get the idea):

CREATE TABLE products (
  pid     int,            # product id
  pname   varchar         # product name (users want to customize this)
);

I need to display the user's customized product name in all tables/lists on my site whenever there is a customized value for that product, but otherwise display the default value.

For space efficiency I thought of using an extra table that sort of "overrides" the default values, for any given user, for any given product. The rows are added in this table as needed. A user that did not customize any product name would not have any rows in there:

CREATE TABLE custom (
  pid     int,
  userid  int,
  pname   varchar
);

I can easily join these two tables on product id, and could pick the default (products.pname) , or user-edited value (custom.pname) for each row in php, but I'm hoping there is a more efficient way to do this in MySQL.

Is there an expression that lets me pick one column if not null otherwise pick another (accross two tables) ?

Would you suggest a different way to handle this ?

Thank you.

PS: maybe a type of JOIN that would allow a column of table 2 with the same name to override a column in table 1 for those rows where it exists in table 2 ?

Answer 1

Take a look at MySQL's CASE-statement. This should do the trick:

   SELECT CASE
              WHEN `custom`.`pname` IS NULL
              THEN `products`.`pname`
              ELSE `custom`.`pname`
          END CASE AS `pname`
     FROM `products`
LEFT JOIN `custom`
       ON `custom`.`pid` = `products`.`pid`
    WHERE `products`.`pid` = 123

I did not test this, but this is how it should work basically.

Answer 2

You are looking for the COALESCE function.

SELECT COALESCE(the_field, "This literal") FROM YOUR_TABLE

results in the_field if the_field is not null, otherwise it is "This literal". You can mix fields and literals as much as you want .. so SELECT COALESCE(this_field, that_field) works too. You can have more than 2 parameters ... e.g. SELECT COALESCE(this_field,that_field, theother_field)

Answer 3

Use a combination of LEFT JOIN and COALESCE:

   SELECT p.pid,
          COALESCE(c.pname, p.pname) AS product_name
     FROM PRODUCT p
LEFT JOIN CUSTOM c ON c.pid = p.pid
                  AND c.userid = ?

Because c.pname will be null if the user hasn't provided a custom name, the COALESCE function will default to using the next non-NULL value--from p.pname.

Answer 4

select if(c.pname is not null, c.pname, p.pname) pname
  from products p left outer join custom c on c.pid = p.pid;

The left outer join always delivers a row match, which will have null fields if the join fails, you can test that with the if() function.