CODEWITHSUNDEEP
sqlsql-server
Joel
Joel

Reputation: 1574

Simplifying CASE Statement with Correlated Subqueries

Can I simplify the below CASE statement, which runs the same correlated subquery twice?

CASE
    WHEN (SELECT val1
            FROM t1
                WHERE out.id = t1.t1
                    AND condition) IS NULL THEN 0
    ELSE (SELECT val1
            FROM t1
                WHERE out.id = t1.t1
                    AND condition)
END     AS desired_value

In the above code, out is the table being referenced outside the subquery, and the SELECT statement is guaranteed to always give one value or NULL.

Ideally, I would like to have this subquery run only once.

Upvotes: 0

Views: 188

Answers (2)

Dmitriy Bayraktar
Dmitriy Bayraktar

Reputation: 126

You definitely have your "out" table in FROM clause of a query, so try to get rid of the sub-query completely:

SELECT ISNULL(t1.val1, 0) as desired_value
FROM out
LEFT JOIN t1 on out.id = t1.t1 AND condition

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Using coalesce() doesn't actually fix the problem (because SQL Server runs the first argument twice when the value is null). . . the correlated query is still run twice. isnull() does fix that problem:

isnull( (SELECT val1
         FROM t1
         WHERE out.id = t1.t1 AND condition
        ) , 0
     ) AS desired_value

Or, you can use an aggregation query:

(SELECT COALESCE(MAX(val1), 0)
 FROM t1
 WHERE out.id = t1.t1 AND condition
) AS desired_value

Upvotes: 1

Related Questions