使用 where 过滤 sql 查询结果丢失空值
我有一个连接超过 7 个表的复杂查询.加入后，我想过滤我的查询结果.
I have a complex query which joins more than 7 tables. After the joins, I would like to filter the result of my query .
这是我观察到的.
当我执行 where 子句时
When I do a where clause
where X.Name != 'xxx'
and XY.Product != 1
我得到过滤结果，但 X.Name 和 XY.Product 的所有空值也消失了从我的结果.我想保留空值.
I get the filtered results , but all the null values for the X.Name and XY.Product also disappear from my result. I would like to retain the null values.
我也试过了:
and X.Name != 'xxx'
and XY.Product != 1
我完全删除了 where 子句并放入了一个 and ，但我根本看不到这种方法的过滤.
I removed the where clause totally and put in an and , but I dont see the filtering at all by this approach.
有没有一种方法可以过滤我的结果而不会丢失空值??
Is there a way I can filter my results without losing the null values ??
推荐答案
试试这样的:
where (X.Name <> 'xxx' or X.Name is null)
and (XY.Product <> 1 or XY.Product is null)
因为根据定义 NULL 是一个未知值(有点简化，但对于这个解释来说可以)，它既不等于也不等于给定值 - 这就是为什么 IS NULL 在这里是必需的.
Since, by definition NULL is an unknown value (bit simplified but OK for this explanation), it will neither equal or not equal a given value - that's why the IS NULL is required here.
相关文章