Search code examples
sql-server-2008joinquery-optimizationsubquerycommutativity

How to optimize conditional sub-selects that involve inner joins?


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 

Solution

  • 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
           ...