CODEWITHSUNDEEP
sql
OschtärEi
OschtärEi

Reputation: 2255

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?

Upvotes: 3

Views: 56

Answers (2)

Multisync
Multisync

Reputation: 8797

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%"';

It's an Oracle syntax, t is just a subquery (WITH lets you give names to subqueries)

If the process of calculating t is complex this query may be faster

P.S. Your database may not use short-cut evaluation for WHERE conditions. Need to test (actually Oracle doesn't guarantee its usage, but Oracle optimizer is smart enough, so it worked in my case)

Upvotes: 1

usr
usr

Reputation: 171218

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.

Upvotes: 2

Related Questions