Best way to configure sql preferences

Question

I have a table called items as follows:

 id oId key value 1 0 color green 2 0 size 30 3 1 color red 4 2 color blue 

Above the lines with oId=0 , the default values ​​for the elements are set.
I need to select all the key , value element with a specific oId , which will include the default value (oId = 0) if the specific key and value for this oId does not exist.

For example, For item 2 it should return

 id oId key value 1 0 size 30 2 2 color blue 

I wrote the following query:

 SELECT * FROM items AS i WHERE i.oId=0 AND i.key NOT IN (SELECT key FROM items WHERE oId=2) UNION ALL SELECT * FROM items WHERE oId=2 

Is there a way to optimize the above query?

Answer 1

You can use JOIN at the desired index:

 SELECT t1.`key`, IFNULL(t2.value, t1.value) FROM `items` AS t1 LEFT JOIN `items` AS t2 ON t1.`key` = t2.`key` AND t2.`oId` = 2 WHERE t1.`oId` = 0; 

SQLFiddle