I have got an SQL query (MS SQL 2008) that I would like to optimize speed-wise. It has got the following structure (only in reality there are 10 different when-cases in the case statement).
The important bits are the sub selects in the case statement that involve inner joins between additional tables and a reference to one of the tables in the FROM clause (table1).
I was thinking that I could optimize this using left (outer) joins in the FROM clause instead of sub select, but am not sure because the sub selects also involve inner joins as well. Would I then use two left joins in the FROM clause where now I am using an inner join in the sub selects? And how would that work with AnotherTable3 from the second when-case?
Any ideas are much appreciated.
SELECT table1.a,
table2.b,
CASE
WHEN table1.XY = 1 THEN
(SELECT something
FROM AnotherTable1 at1
INNER JOIN AnotherTable2 at2
ON at1.x = at2.y
WHERE at1.abc = table2.abc)
WHEN table1.XY = 2 THEN
(SELECT something
FROM AnotherTable1 at1
INNER JOIN AnotherTable3 at3
ON at1.x = at3.y
WHERE at1.abc = table2.abc)
END AS [problem]
FROM MyTable1 table1
INNER JOIN MyTable2 table2
ON table1.a = table2.b
In this special case where AnotherTable1 is joined in both when parts with the same condition you only need three left joins:
SELECT table1.a,
table2.b,
CASE
WHEN table1.XY = 1 THEN
?.something -- from whereever it's coming
WHEN table1.XY = 2 THEN
?.something -- from whereever it's coming
END AS [problem]
FROM MyTable1 table1
INNER JOIN MyTable2 table2
ON table1.a = table2.b
LEFT JOIN AnotherTable1 at1
ON at1.abc = table2.abc
LEFT JOIN AnotherTable2 at2
ON at1.x = at2.y
LEFT JOIN AnotherTable3 at3
ON at1.x = at3.y
In more general case you would have this option
FROM MyTable1 table1
INNER JOIN MyTable2 table2
ON table1.a = table2.b
LEFT JOIN
(select *
from AnotherTable1 at1
INNER JOIN
AnotherTable2 at2
ON at1.x = at2.y
) at1
ON at1.abc = table2.abc
...