(SELECT ID FROM (SELECT 1 AS AA) A) 这个里面的ID是从 ( SELECT 2 AS ID ) A 里边取出来的吧 始终返回真
这个句子等价于 select * from (select 2) a
SELECT * FROM ( SELECT 2 AS ID ) A WHERE ID IN (SELECT ID FROM (SELECT 1 AS AA)B)也是一样,不信你可以运行下看看
因为后面的where 条件永远成立 所以没起到限定作用
fredrickhu 老大:为什么会等价与select * from (select 2) a 这个呢。 SELECT * FROM ( SELECT 2 AS ID1 ) A WHERE ID1 IN (SELECT ID FROM (SELECT 1 AS AA)B) 这样就出ID无效的错误提示
看结果,确实是 Where 后面永远成立,这个是什么,是SQL2000的问题么?
SQL SERVER技术内幕对此问题进行了解释
有更详细的解释么。。 Where 字段 in (Select 字段) 这个到底怎么解释呢
SELECT * FROM ( SELECT 3 AS F1,3 AS F2 ) A WHERE F1 IN (SELECT F1 FROM (SELECT 1 AS F3)B) /* F1 F2 ----------- ----------- 3 3 */ SELECT * FROM ( SELECT 3 AS F1,3 AS F2 ) A WHERE F1 IN (SELECT F2 FROM (SELECT 1 AS F3)B)/* F1 F2 ----------- ----------- 3 3 */ SELECT * FROM ( SELECT 9999999 AS F1,3 AS F2 ) A WHERE F1 IN (SELECT F1 FROM (SELECT 1 AS F3)B)/* F1 F2 ----------- ----------- 9999999 3 */ SELECT * FROM ( SELECT 9999999 AS F1,3 AS F2 ) A WHERE F1 IN (SELECT F2 FROM (SELECT 1 AS F3)B) /* F1 F2 ----------- ----------- (0 件処理 */SELECT * FROM ( SELECT 9999999 AS F1,3 AS F2 ) A WHERE F1 IN (SELECT F4 FROM (SELECT 1 AS F3)B)/*'F4'無効*/ 看上面的执行结果。 Select * FROM (临时表或者结果集)_1 Where Field IN (SELECT FieldX From (临时表或者结果集)_2)如果 IN () 里面FieldX 不在(临时表或者结果集)_2 中的时候,就会去参照(临时表或者结果集)_1SQL也就成了Select * FROM (临时表或者结果集)_1 Where Field IN (SELECT FieldX From (临时表或者结果集)_1)
SELECT * FROM ( SELECT 2 AS ID ) A WHERE ID IN (SELECT ID FROM (SELECT 1 AS AA)A) 等价于 SELECT * FROM ( SELECT 2 AS ID UNION ALL SELECT 3 AS ID) A WHERE ID IN (SELECT DISTINCT ID FROM (SELECT 1 AS AA)B CROSS JOIN A)
错了 应该是 SELECT * FROM ( SELECT 2 AS ID ) A WHERE ID IN (SELECT ID FROM (SELECT 1 AS AA)A) 等价于 SELECT * FROM ( SELECT 2 AS ID ) A WHERE ID IN (SELECT DISTINCT ID FROM (SELECT 1 AS AA)B CROSS JOIN A)
这个里面的ID是从
( SELECT 2 AS ID ) A
里边取出来的吧 始终返回真
SELECT *
FROM ( SELECT 2 AS ID ) A
WHERE ID IN (SELECT ID FROM (SELECT 1 AS AA)B)也是一样,不信你可以运行下看看
这个呢。
SELECT *
FROM ( SELECT 2 AS ID1 ) A
WHERE ID1 IN (SELECT ID FROM (SELECT 1 AS AA)B)
这样就出ID无效的错误提示
看结果,确实是 Where 后面永远成立,这个是什么,是SQL2000的问题么?
Where 字段 in (Select 字段) 这个到底怎么解释呢
FROM ( SELECT 3 AS F1,3 AS F2 ) A
WHERE F1 IN (SELECT F1 FROM (SELECT 1 AS F3)B)
/*
F1 F2
----------- -----------
3 3
*/
SELECT *
FROM ( SELECT 3 AS F1,3 AS F2 ) A
WHERE F1 IN (SELECT F2 FROM (SELECT 1 AS F3)B)/*
F1 F2
----------- -----------
3 3
*/
SELECT *
FROM ( SELECT 9999999 AS F1,3 AS F2 ) A
WHERE F1 IN (SELECT F1 FROM (SELECT 1 AS F3)B)/*
F1 F2
----------- -----------
9999999 3
*/
SELECT *
FROM ( SELECT 9999999 AS F1,3 AS F2 ) A
WHERE F1 IN (SELECT F2 FROM (SELECT 1 AS F3)B)
/*
F1 F2
----------- ----------- (0 件処理
*/SELECT *
FROM ( SELECT 9999999 AS F1,3 AS F2 ) A
WHERE F1 IN (SELECT F4 FROM (SELECT 1 AS F3)B)/*'F4'無効*/
看上面的执行结果。
Select *
FROM (临时表或者结果集)_1
Where Field IN (SELECT FieldX From (临时表或者结果集)_2)如果 IN () 里面FieldX 不在(临时表或者结果集)_2 中的时候,就会去参照(临时表或者结果集)_1SQL也就成了Select *
FROM (临时表或者结果集)_1
Where Field IN (SELECT FieldX From (临时表或者结果集)_1)
FROM ( SELECT 2 AS ID ) A
WHERE ID IN (SELECT ID FROM (SELECT 1 AS AA)A)
等价于
SELECT *
FROM ( SELECT 2 AS ID UNION ALL SELECT 3 AS ID) A
WHERE ID IN (SELECT DISTINCT ID FROM (SELECT 1 AS AA)B CROSS JOIN A)
SELECT *
FROM ( SELECT 2 AS ID ) A
WHERE ID IN (SELECT ID FROM (SELECT 1 AS AA)A)
等价于
SELECT *
FROM ( SELECT 2 AS ID ) A
WHERE ID IN (SELECT DISTINCT ID FROM (SELECT 1 AS AA)B CROSS JOIN A)