Sometimes a SQL query ends up needing an expression to be represented more than once. For example, some queries I've worked on have required a complex-to-calculate denominator that needs to be reused in multiple division calculations.
Usually one just duplicates the expression, but this is undesirable when:
- the SQL for the expression is particularly long
- or, particularly computationally expensive to calculate
I had long wished there was a way to embed a variable in the body of a select statement. Something like:
select t.numerator1 / denominator, t.numerator2 / denominator,from mytable tlet demoninator = ....some complex calculation ... -- 'let' is NOT valid SQL
Sadly the above is not valid SQL. However, it can be achieved as follows:
select t.numerator1 / v.denominator, t.numerator2 / v.denominator,from mytable touter apply ( select demoninator = ....some complex calculation ...) v
Or more generally, this can be used with multiple variables, and the variables can be used in subsequent joins, and other query clauses.
select v.variable1, v.variable2, v.variable3from mytable touter apply ( select variable1 = ..., variable2 = ..., variable3 = ...) v
join
mytable2 t2 on t2.id = v.variable1
where
v.variable2 = ...
order by
v.variable3
There is an important caveat with this: the outer-apply manifests in the query execution plan as a nested loop join. However, this isn't necessarily a bad thing - rather it's representing the fact that the variables will always be calculated prior to other elements of the query being processed.
Note: don't go overboard with this method. But it can be a useful solution when duplication of expressions becomes problematic.
No comments:
Post a Comment