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
过滤器?
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()
。详细资料: 其他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()
。
此相关问题下的更多:
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);
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:
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:
这篇关于MySql,Postgres,Oracle和SQLServer忽略IS NOT NULL过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!