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.
1 comment:
Additionally, they have a “hot and cold” slots characteristic which tells you which ones slots video games are paying out in the meanwhile. Ignition likes to keep issues easy, but practical with their banking options. They provide credit and debit card options properly as|in addition to} Bitcoin, Bitcoin Cash, bank wires, and checks as their suite of banking options. If you want 카지노사이트 to withdraw money from your online on line casino account, received't|you will not} obtain your earnings immediately, so count on a ready interval. This ensures a secure and authorized process between you and the operator.
Post a Comment