SQL 使用子查询分配变量 [英] SQL assign variable with subquery

问题描述

我对以下 2 个 SQL 有疑问:

I have a question for following 2 SQL:

declare @i1 bit, @b1 bit
declare @i2 bit, @b2 bit
declare @t table (Seq int)
insert into @t values (1)

-- verify data
select case when (select count(1) from @t n2 where 1 = 2) > 0 then 1 else 0 end
-- result 0

select @i1 = 1, @b1 = case when @i1 = 1 or ((select count(1) from @t n2 where 1 = 2) > 0) then 1 else 0 end from @t n where n.Seq = 1
select @i1, @b1
-- result 1, 0

select @i2 = 1, @b2 = case when @i2 = 1 or (0 > 0) then 1 else 0 end from @t n where n.Seq = 1
select @i2, @b2
-- result 1, 1

SQL Fiddle Here

在执行之前，我认为case部分应该是null = 1 or (0 > 0)，它会返回0.

Before the execute, I thought the case part should be null = 1 or (0 > 0), and it will return 0.

但是现在，我想知道为什么第二个 SQL 会返回 1

But now, I wondering why the 2nd SQL will return 1

推荐答案

我会将此作为答案发布，因为它来自 Training Kit (70-461):

I will post this as an answer as it is quite large text from Training Kit (70-461):

WHERE propertytype = 'INT' AND CAST(propertyval AS INT) >10

有些人认为除非优先规则另有规定，否则谓词将从左到右评估，短路将在可能的情况下进行.换句话说，如果第一个谓词propertytype = 'INT' 评估为 false，SQL Server 不会评估第二个谓词 CAST(propertyval AS INT) >10 因为结果是已经知道.基于这个假设，期望是查询应该永远不会失败尝试转换不是敞篷车.

Some assume that unless precedence rules dictate otherwise, predicates will be evaluated from left to right, and that short circuiting will take place when possible. In other words, if the first predicate propertytype = 'INT' evaluates to false, SQL Server won’t evaluate the second predicate CAST(propertyval AS INT) > 10 because the result is already known. Based on this assumption, the expectation is that the query should never fail trying to convert something that isn’t convertible.

然而，现实是不同的.SQL Server 可以内部支持短路概念；然而，由于一次性概念在语言中，不一定会按从左到右的顺序计算表达式.它可以决定，基于成本相关的原因，从第二个表达式开始，然后如果第二个表达式的计算结果为真，则计算第一个表达式也是如此.这意味着如果有行属性类型不同于INT"的表，并且在这些行中propertyval 不能转换为 INT，查询可能由于转换错误.

The reality, though, is different. SQL Server does internally support a short-circuit concept; however, due to the all-at-once concept in the language, it is not necessarily going to evaluate the expressions in left-to-right order. It could decide, based on cost-related reasons, to start with the second expression, and then if the second expression evaluates to true, to evaluate the first expression as well. This means that if there are rows in the table where propertytype is different than 'INT', and in those rows propertyval isn’t convertible to INT, the query can fail due to a conversion error.

这篇关于SQL 使用子查询分配变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！