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

Question

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 such cases I usually write `select ... from MayTable1 table1 ... left join (select ... from AnotherTable1 at1 ...) at1 on at1.abc = table2.abc and table1.XY = 1 ...` — Claude, Mar 14 '13 at 11:03
Compare [this question and its answers](http://stackoverflow.com/questions/15384979/case-clause-execution-procedure) — Claude, Mar 14 '13 at 11:06

score 0 · Answer 1 · answered Mar 14 '13 at 11:08

0

You can try following:

SELECT 
    table1.a,
    table2.b,
    CASE 
        WHEN table1.XY = 1 THEN at2.something
        WHEN table1.XY = 2 THEN at3.something
    END
FROM   MyTable1 table1
INNER JOIN MyTable2 table2
    ON table1.a = table2.b 
LEFT JOIN AnotherTable1 at1
    ON at1.abc = table2.abc
INNER JOIN AnotherTable2 at2
    ON at1.x = at2.y
INNER JOIN AnotherTable3 at3
    ON at1.x = at3.y

But check results, also keep in mind that at2 and at3 columns must have same data type

answered Mar 14 '13 at 11:08

Sergio

6,900
5
31
55

This seems to be wrong: when left join between at1 and table2 evaluates at1 to null then inner join between at1 and at2 fails and the row is lost. This is different from the original query – Claude Mar 14 '13 at 11:17
row would be lost in original query as well, I don't know db schema, so guess there's foreign key or smth else, because @DotNetDeveloper does it this way – Sergio Mar 14 '13 at 11:26
I do get fewer results this way, the problem is as Michael stated above. I think the problem is that INNER JOIN AnotherTable2 at2 not only means inner join on the previous left join but on all data. – DotNetDeveloper Mar 14 '13 at 11:31

score 0 · Accepted Answer · answered Mar 14 '13 at 11:16

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

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

2 Answers2