在嵌套子查询中,是不是不能出现值为 null 的记录?
如
select *
from 表1
where 字段名1 not in (select 字段名1 from 表2 where 条件) 其中,(select 字段名1 from 表2 where 条件) 就是一个子查询,是不是必须要保证这个子查询中,不能出现值为 null 的记录呢?
好象说, null 与 null 是无法比较的,我的理解对吗? 也就是说,能不能出现类似
select *
from 表1
where 字段名1 not in (null)这种情况
如
select *
from 表1
where 字段名1 not in (select 字段名1 from 表2 where 条件) 其中,(select 字段名1 from 表2 where 条件) 就是一个子查询,是不是必须要保证这个子查询中,不能出现值为 null 的记录呢?
好象说, null 与 null 是无法比较的,我的理解对吗? 也就是说,能不能出现类似
select *
from 表1
where 字段名1 not in (null)这种情况
select *
from 表1
where 字段名1 not in (null)这种情况
FROM 表1
WHERE 字段名1 not in (SELECT 字段名1 FROM 表2 WHERE 条件 AND ISNULL(字段名1,'') <>'')
(select 字段名1 from 表2 where 条件)
这个子查询,能不出是以下的返回集
23
564
null
67
即,能不能存在 null 的记录。
因为 null 值不能参与比较呀?
1、
NOT IN Equivalent to !=ALL. Evaluates to FALSE if any member of the set is NULL. 2、
If any item in the list following a NOT IN operation evaluates to null, then all rows evaluate to FALSE or UNKNOWN, and no rows are returned. For example, the following statement returns the string 'True' for each row:SELECT 'True' FROM employees
WHERE department_id NOT IN (10, 20);However, the following statement returns no rows:SELECT 'True' FROM employees
WHERE department_id NOT IN (10, 20, NULL); The preceding example returns no rows because the WHERE clause condition evaluates to:department_id != 10 AND department_id != 20 AND department_id != null Because the third condition compares department_id with a null, it results in an UNKNOWN, so the entire expression results in FALSE (for rows with department_id equal to 10 or 20). This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.Moreover, if a NOT IN condition references a subquery that returns no rows at all, then all rows will be returned, as shown in the following example:SELECT 'True' FROM employees
WHERE department_id NOT IN (SELECT 0 FROM DUAL WHERE 1=2);
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a varchar(9))
go
insert into tb
select 'a' union all
select 'b' union all
select NULL go
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1(b varchar(9))
go
insert into tb1
select 'a'
go
select * from tb1 where b in(select * from tb )
/*------------
a
------a-*/
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a varchar(9))
go
insert into tb
select 'a' union all
select 'b' union all
select NULL go
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1(b varchar(9))
go
insert into tb1
select 'a' union all
select null
go
select * from tb1 where b in(select * from tb )
/*------------
a
------a-*/
564
null
67 可以显示23,564的,
过了null 以后 即使有67也不显示记录