MySql，Postgres，Oracle和SQLServer忽略IS NOT NULL过滤器 [英] MySql, Postgres, Oracle and SQLServer ignoring IS NOT NULL filter

问题描述

当我准备回答我们的一个同事在这里，我遇到一个奇怪的情况，至少对我。原始问题在此处：数据透视表省略具有空值的行 a>

我修改了查询以使用 max ，而不是 group_concat 以显示所有数据库中的问题。

  SELECT 
 id，
 max（case colID = 1 then value else''end）AS fn，
 max（case colID = 2 then value else''end）AS ln，
 max（colID = 3 then then else''end）AS jt 
 FROM tbl 
 GROUP BY id 
  

此查询的结果是：

  ID FN LN JT 
 1 Sampo Kallinen办公室经理
 2 Jakko Salovaara副总裁
 3（null）Foo No名字
  

以过滤ID 3 的行，因为字段 value 为空。

当它显然很明显，只需要添加一个 WHERE值IS NOT NULL 该查询实现用户期望的。它不会工作。

所以我开始在其他数据库上测试，看看会发生什么（使用WHERE CLAUSE的查询）

  SELECT 
 id，
 max（case colID = 1 then value else''end）AS fn，
 max case colID = 2 then value else''end）AS ln，
 max（case colID = 3 then value else''end）AS jt 
 FROM tbl 
 WHERE值不为null 
 GROUP BY id 
  

• Mysql： http://sqlfiddle.com/#!2/78395/1


• Postgres： http://sqlfiddle.com/#!15/78395/1


• SQLServer： http://sqlfiddle.com/#！ 6/78395/1


• Oracle： http ：//sqlfiddle.com/#！4/78395/1

令我吃惊的是，结果是一样的，没有工作。

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

  SELECT * FROM（
 SELECT 
 id，
 max（case colID = 1 then value else''end）AS fn，
 max（case colID = 2 then value 'end）AS ln，
 max（case colID = 3 then value else''end）AS jt 
 FROM tbl 
 GROUP BY id 
）T 
 WHERE fn IS NOT NULL 
和ln不是NULL 
和jt不是NULL 
  

• Oracle： http://sqlfiddle.com/#!4/ 78395/2 WORKED


• MySql： http://sqlfiddle.com/#!2/78395/2


• Postgres： http://sqlfiddle.com/#!15/78395/2


• SQLServer： http://sqlfiddle.com/#!6/78395/2

我可以使它在所有数据库上工作的唯一方法是使用以下查询：

  SELECT 
 id，
 max（case colID = 1 then value else''end）AS fn，
 max（case colID = 2 then value else''end ）AS ln，
 max（case 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 过滤器？

源 中包含任何 源 中的解决方案 strong>将使用聚合函数 every（） 或（由于历史原因而为同义词） HAVING 中的 bool_and（）

  SELECT id 
，max（case colID = 1 then value else''end）AS fn 
，max（case colID = 2 then value else''end）AS ln 
，max（case colID = 3 then then else''end）AS jt 
 FROM tbl 
 GROUP BY id 
  HAVING every（value IS NOT NULL）;  

a href =http://sqlfiddle.com/#!15/b8991/4 =nofollow> SQL Fiddle。

说明

您尝试使用 WHERE 子句只会删除一个您的示例中的 id = 3 的源行（具有 colID = 1 的源列） id 。所以我们仍然在聚合后的结果中得到 id = 3 的一行。

但是因为我们没有行与 colID = 1 ，我们得到一个空字符串（注意：不是 NULL 在 id = 3 的结果中

Postgres将使用 crosstab（）。详细资料：

• PostgreSQL Crosstab Query

其他RDBMS

EVERY 在SQL：2008标准中定义，许多RDBMS不支持它，可能是因为它们中的一些有布尔类型的阴影实现。 （不丢弃任何名称，如MySQL或Oracle...）。你可以用下面的代替任何地方（包括Postgres）：

  SELECT id 
，max（case colID = value else''end）AS fn 
，max（case colID = 2 then value else''end）AS ln 
，max（case colID = 3 then value else''end）AS jt 
 FROM tbl 
 GROUP BY id 
  HAVING count（*）= count（value）;  

因为 count（）不会计算NULL值。在MySQL中还有 bit_and（） 。
此相关问题下的更多：

• 是否有任何等同于PostgreSQL其他RDBMS上的集合函数？

While I was preparing an answer to one of our fellows here on SO I've encounter an odd situation, at least to me. The original question is here: Pivot Table Omitting Rows that Have Null values

I've modified the query to use max instead of group_concat in order to show the "problem" in all databases.

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

The result of this query is this:

ID    FN        LN            JT
1    Sampo    Kallinen     Office Manager
2    Jakko    Salovaara    Vice President
3    (null)   Foo          No First Name

The user asks to filter the row with id 3 because the field value is null.

When it seems pretty obvious that only it needs to do was to add a WHERE value IS NOT NULL constraint on that query to achieve what the user expect. It won't work.

So I start to test it on the other databases to see what happens (Queries with the 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

• Mysql: http://sqlfiddle.com/#!2/78395/1
• Postgres: http://sqlfiddle.com/#!15/78395/1
• SQLServer: http://sqlfiddle.com/#!6/78395/1
• Oracle: http://sqlfiddle.com/#!4/78395/1

For my surprise the result was the same, none worked.

Then I tried a different version of the same query:

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

• Oracle: http://sqlfiddle.com/#!4/78395/2 WORKED
• MySql: http://sqlfiddle.com/#!2/78395/2
• Postgres: http://sqlfiddle.com/#!15/78395/2
• SQLServer: http://sqlfiddle.com/#!6/78395/2

The only way I could make it work on all databases was with this query:

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

So I ask:
What is happening here that except for that specific case on Oracle all other DBs seem to ignore the IS NOT NULL filter?

解决方案

To omit the row from the result if any of the source rows for the same id has value IS NULL, a solution in Postgres would be to use the aggregate function every() or (synonym for historical reasons) bool_and() in the HAVING 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 
GROUP  BY id
HAVING every(value IS NOT NULL);

SQL Fiddle.

Explain

Your attempt with a WHERE clause would just eliminate one source row for id = 3 in your example (the one with colID = 1), leaving two more for the same id. So we still get a row for id = 3 in the result after aggregating.

But since we have no row with colID = 1, we get an empty string (note: not a NULL value!) for fn in the result for id = 3.

A faster solution in Postgres would be to use crosstab(). Details:

• PostgreSQL Crosstab Query

Other RDBMS

While EVERY is defined in the SQL:2008 standard, many RDBMS do not support it, presumably because some of them have shady implementations of the boolean type. (Not dropping any names like "MySQL" or "Oracle" ...). You can probably substitute everywhere (including Postgres) with:

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
HAVING count(*) = count(value);

Because count() doesn't count NULL values. In MySQL there is also bit_and(). More under this related question:

• Is there any equivalent to Postgresql EVERY aggregate function on other RDBMS?

这篇关于MySql，Postgres，Oracle和SQLServer忽略IS NOT NULL过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！