better way to condition preference sql

Question

I have a table named items as follows:

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

Above rows with oId=0 specifies the default values of items .
I need to select all key, value of an item with a particular oId which will include the default (oId=0), if a specific key and value for that oId does not exists.

For eg. For item 2 , it should return

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

I have written 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 on the right 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