Optimizing nested selection queries
I have a table products
and a table translations
. I have n
translations for each product, but there is always a default translation ( LangID = 0
).
What I want to do is "search-query" for the string entered by the user. If there is a translation in the language the user is using (say LangID = 1
), I only want to search for translations for that language (and also NOT the default), but if there is no translation in the language I want, I want the default search. 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 nested selection? Or is there a much better (quality) query / approach?
source to share
You can do it this way, but it's not clear if the execution plan is good or bad. The combination of OR
and EXISTS
does not give a reliable result in a good plan.
It's safer and faster to just connect 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 ...
Then you use the values t1
if they exist. If you don't use values t0
. The predicate t1.langid is null
is optional, but allows SQL Server to (hopefully) skip the second connection if the first succeeds. I've seen this template work, but it's not safe to assume it always happens.
This provides a reliable and simple plan.
source to share
with t as (
SELECT * FROM products p JOIN translations t ON p.ID = t.ProductID
WHERE t.LangID = 1 AND t.Translation LIKE "Foo%")
select * from t
union all
SELECT * FROM products p, translations t
where (select count(*) from t) = 0
and p.ID = t.ProductID
and t.LangID = 0 AND t.Translation LIKE "Foo%"';
This is Oracle syntax, t is just a subquery (WITH allows you to name subqueries)
If the process of computing t is complex, this query may be faster
PS Your database cannot use short evaluation for WHERE clauses. Needs to be tested (actually Oracle does not guarantee its use, but Oracle's optimizer is smart enough so it worked in my case)
source to share