使用 where 过滤 sql 查询结果丢失空值 [英] losing null values filtering sql query results using where

问题描述

我有一个连接超过 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.

这篇关于使用 where 过滤 sql 查询结果丢失空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！