MySQL哪里不是全部为空

Question

一个能够验证所有选定记录的查询如何不为空？例如，对于以下查询，验证字段为is_field_null的所有行是否不是返回的level1中的NULL？

SELECT * FROM (
SELECT tb_a.A, tb_b.is_field_null FROM tb_a, tb_b, tb_c
WHERE tb_a.a = "some" AND tb_a.b = "thing" AND tb_c.c = "else"
AND tb_a.b = tb_b.a
AND tb_c.b = tb_b.c
) AS level1

假设level1中有一些is_field_null的行实际上是NULL，并且表是

tb_a

a          |    b        |      A
------------------------------------------
"some"     | "thing"     | "XXX"
"some"     | "thing"     | "YYY"

tb_b

a            | c           | is_field_null 
----------------------------------------------
"thing"       | "else"      | "I have things here" 
"thing"       | "else"      | NULL

tb_c

b            | c           | mapper 
----------------------------------------------
"else"       | "else"      | "ZZZ" 
"else"       | "else"      | "KKK"

我尝试过以下操作，它返回一些is_field_null不为空的行。例如

A        |     is_field_null
-----------------------------
"XXX"    | "I have things here"
"YYY"    | "I have things here"

SELECT * FROM (
SELECT tb_a.A, tb_b.is_field_null FROM tb_a, tb_b, tb_c
WHERE tb_a.a = "some" AND tb_a.b = "thing" AND tb_c.c = "else"
AND tb_a.b = tb_b.a
AND tb_c.b = tb_b.c
) AS level1
WHERE level1.is_field_null IS NOT NULL

我希望有一张空桌子。我该怎么做？

例如

SELECT * FROM (
SELECT tb_a.A, tb_b.is_field_null FROM tb_a, tb_b, tb_c
WHERE tb_a.a = "some" AND tb_a.b = "thing" AND tb_c.c = "else"
AND tb_a.b = tb_b.a
AND tb_c.b = tb_b.c
) AS level1
WHERE level1.is_field_null IS NOT ALL NULL ??

Answer 1

如果您运行的是MySQL 8.0，一个简单的方法使用窗口函数：

SELECT *
FROM (
SELECT 
tb_a.A,
tb_b.is_field_null,
MAX(tb_b.is_field_null IS NULL) has_null
FROM tb_a 
INNER JOIN tb_b ON tb_a.b = tb_b.a
INNER JOIN tb_c ON tb_c.b = tb_b.c
WHERE 
tb_a.a = 'some' 
AND tb_a.b = 'thing' 
AND tb_c.c = 'else'
) t
WHERE has_null = 0

请注意，这使用了标准的显式联接，而不是老式的隐式联接——这种古老的语法不应该在新代码中使用。

此外，我建议文字字符串使用单引号而不是双引号(这是MySQL语法(。