Q:

Query optimization with nested select

I have a table products and a Table translations. I have n translations for each product, but there is always at least a default translation (LangID = 0).
What I want to do is a "search-query" for a string a user entered. If there is a translation in the language which the users uses (let's say LangID = 1) I want only to search translations for this language (and also NOT in the default one), but if there is no translation in the desired language I want to search in the default translation. So basically the default translation is just a fallback.
What I came up with:
SELECT * FROM products p JOIN translations t ON p.ID = t.ProductID 
WHERE (t.LangID = 1 OR (t.LangID = 0 AND NOT EXISTS (SELECT id FROM translations t2 WHERE t2.ProductID = p.ID AND t2.LangID = 1))
      AND t.Translation LIKE "Foo%" `

Is this the best way to go or is it possible to do this without the nested-select? Or is there a far better (performance wise) query / approach?

A:

You could do it like this but it is unclear whether the execution plan is good or erratically bad. The combination of OR and EXISTS does not reliably result in a good plan.
It is safer and faster to just join twice:
select *
from products p
left join translations t1 on t1.langid = 1 and ...
left join translations t0 on t1.langid is null and t0.langid = 0 and ...

You then use the values of t1 if they exist. If not you use the values of t0. The predicate t1.langid is null is optional but it allows SQL Server (hopefully) to skip the second join in case the first one succeeds. I have seen this pattern work but it's not safe to assume that it always does.
This results in a reliable and simple plan.

