SQL: left join vs CASE subquery - which is more efficient

Question

Which one do you guys think is more efficient (less load)?

SELECT t1.a, 
(CASE
WHEN t1.p=1 THEN t2.g 
WHEN t1.p=2 THEN t3.g
END) as v

FROM t1
LEFT JOIN t2 ON t1.x=t2.x
LEFT JOIN t3 ON t1.y=t3.y

OR

SELECT 
t1.a, t2.v 

FROM t1    
outer apply (
SELECT v= CASE
WHEN t1.p=1 THEN (SELECT v FROM t2 WHERE t1.x=t2.x)
WHEN t1.p=2 THEN (SELECT v FROM t3 WHERE t1.y=t3.y)
END
) t2

Just want to figure out how sql engine works..

[What im trying to study in this question] In first query the tables t2 andt3 will always be called, but in the 2nd query will they be only called if the case matches (and hence be less load = only 2 tables checked instead of 3 per row)?

Answer 1

I would go with the left join, but write this as:

SELECT t1.a, COALESCE(t2.g, t3.g) as v
FROM t1 LEFT JOIN
     t2
     ON t1.x = t2.x AND t1.p = 1 LEFT JOIN
     LEFT JOIN t3
     ON t1.y = t3.y AND t1.p = 2;

This logic is not necessarily exactly equivalent to your logic, but I suspect that this query does what you intend. (For instance, if t2.g is ever NULL and t1.p = 1, then the results are not the same.)

As for performance, LEFT JOIN gives the optimizer more options for the query than a CASE expression does, so it is likely to be faster. However, with proper indexing, all three queries probably have good performance.

Answer 2

Place an extra predicate t1.p= into each left join so that these become mutually exclusive as needed for the result. This would permit use of coalesce in lieu of the case expression (but case expression is OK, it's just an option).

There is no compelling reason to use an apply operator in your example and although these do optimize better than correlated subqueries placed in a select clause, they remain correlated subqueries. In my opinion, if there is no compelling reason for a "more exotic" option, don't use it. So, don't use an apply operator here, go with the more standard left join.

SELECT
    t1.a
  , COALESCE(t2.g,t3.g) AS v
FROM t1
LEFT JOIN t2 ON t1.x = t2.x AND t1.p = 1
LEFT JOIN t3 ON t1.y = t3.y AND t1.p = 2
;

If however you had a functional need for just one (or a prescribed number) of rows from those correlated subqueries then you would have a compelling reason to use an apply, e.g.

SELECT
    t1.a
  , t2.v
FROM t1
OUTER APPLY (
        SELECT
            CASE
                WHEN t1.p = 1 THEN (
                        SELECT TOP(1) t2.g FROM t2  -- top 
                        WHERE t1.x = t2.x
                        ORDER BY t2.z               -- order
                    )
                WHEN t1.p = 2 THEN (
                        SELECT TOP(1) t3.g FROM t3  -- top
                        WHERE t1.y = t3.y
                        ORDER BY t3.z               -- order
                    )
            END AS v
    ) t2

Regardless of opinion, using execution plans is the best method to compare query options.