Sql server无法解析子查询

I have the below query in SQL server 2012 which is running fine when the whole query is run, but the inner query is not running individually because AdviceRecordID doesn't belong to tblB.

我在SQL Server 2012中有以下查询,它在运行整个查询时运行正常,但内部查询未单独运行,因为AdviceRecordID不属于tblB。

SELECT DISTINCT SubLOBID 
FROM tblA 
WHERE AdviceRecordID 
IN (SELECT AdviceRecordID
    FROM tblB
)

The first case, where the whole query is run, is not considering the WHERE condition at all and returing all the results without any error which is strange for me.

运行整个查询的第一种情况是,根本不考虑WHERE条件并且撤回所有结果而没有任何对我来说很奇怪的错误。

2 个解决方案

#1

This works as expected and required by the SQL standard. If a subquery references a column that is not available in the tables of the subquery, but is a valid column in the outer query, the value from the outer query's column is used.

这符合SQL标准的预期和要求。如果子查询引用了子查询的表中不可用但在外部查询中是有效列的列,则使用外部查询列中的值。

So the query:

所以查询:

SELECT DISTINCT SubLOBID 
FROM tblA 
WHERE AdviceRecordID IN (SELECT AdviceRecordID
                         FROM tblB);

Is in fact a co-related subquery and is parsed and executed as:

实际上是一个共同相关的子查询,并被解析和执行为:

SELECT DISTINCT SubLOBID 
FROM tblA 
WHERE AdviceRecordID IN (SELECT tblA.AdviceRecordID
                         FROM tblB);

So for each row in tblA the subquery returns the value of tblA.AdviceRecordID once for each row in tblB and compares that to the rules of the IN operator.

因此,对于tblA中的每一行,子查询为tblB中的每一行返回tblA.AdviceRecordID的值一次,并将其与IN运算符的规则进行比较。

That's why the query as a whole is valid, and behaves like no where clause was used as the above is equivalent to:

这就是为什么查询作为一个整体是有效的,并且行为就像没有使用where子句,因为上面相当于:

SELECT DISTINCT SubLOBID 
FROM tblA 
WHERE AdviceRecordID IS NOT NULL;

And if there are no NULL values in the column AdviceRecordID then the WHERE clause is not filtering out anything.

如果AdviceRecordID列中没有NULL值,则WHERE子句不会过滤掉任何内容。

#2

Better to use EXISTS instead of IN clause.

最好使用EXISTS而不是IN子句。

Check the difference:

检查区别:

Difference1

Difference2

SELECT DISTINCT A.SubLOBID 
FROM tblA A
WHERE EXISTS 
(
    SELECT B.AdviceRecordID
    FROM tblB B
    WHERE B.AdviceRecordID=A.AdviceRecordID
)

#1