oracle mysql is null_MySql,Postgres,Oracle和SQLServer忽略IS NOT NULL过滤...

当我准备在这里回答我们其中一位同伴的问题时,我遇到了一个奇怪的情况,至少对我而言.原来的问题在这里：Pivot Table Omitting Rows that Have Null values

我已修改查询以使用max而不是group_concat,以便在所有数据库中显示“问题”.

SELECT

id,

max(case when colID = 1 then value else '' end) AS fn,

max(case when colID = 2 then value else '' end) AS ln,

max(case when colID = 3 then value else '' end) AS jt

FROM tbl

GROUP BY id

该查询的结果是这样的：

ID FN LN JT

1 Sampo Kallinen Office Manager

2 Jakko Salovaara Vice President

3 (null) Foo No First Name

用户要求过滤ID为3的行,因为该字段值为null.

很明显,仅需要做的就是在该查询上添加WHERE值IS NOT NULL约束以实现用户期望的结果.它不会工作.

因此,我开始在其他数据库上对其进行测试,以查看会发生什么情况(WHERE CLAUSE查询)

SELECT

id,

max(case when colID = 1 then value else '' end) AS fn,

max(case when colID = 2 then value else '' end) AS ln,

max(case when colID = 3 then value else '' end) AS jt

FROM tbl

WHERE value is not null

GROUP BY id

令我惊讶的是结果是一样的,没有一个奏效.

然后,我尝试了同一查询的不同版本：

SELECT * FROM (

SELECT

id,

max(case when colID = 1 then value else '' end) AS fn,

max(case when colID = 2 then value else '' end) AS ln,

max(case when colID = 3 then value else '' end) AS jt

FROM tbl

GROUP BY id

) T

WHERE fn IS NOT NULL

AND ln IS NOT NULL

AND jt IS NOT NULL

我可以使其在所有数据库上运行的唯一方法是使用此查询：

SELECT

id,

max(case when colID = 1 then value else '' end) AS fn,

max(case when colID = 2 then value else '' end) AS ln,

max(case when colID = 3 then value else '' end) AS jt

FROM tbl

WHERE NOT EXISTS (SELECT * FROM tbl b WHERE tbl.id=b.id AND value IS NULL)

GROUP BY id

所以我问：

除了Oracle上的特定情况外,所有其他DB似乎都忽略了IS NOT NULL过滤器,这是怎么回事？