SQL 服务器查询中的 NULL 值

2021-09-10 00:00:00 sql tsql sql-server

我在表中有一个 Status 列,它有 3 个值 - 'N/A'、'Single'、'Multiple'.某些行的状态列具有 NULL 值.

I've a Status column in a table which has 3 values - 'N/A' , 'Single' ,'Multiple'. Some rows have a NULL value for the Status column.

我需要提取 Status 不为空且不为N/A"的所有行.基本上,我需要状态为单个"或多个"的所有行.

I need to pull up all the rows in which Status is not null and is not 'N/A'. Basically, I need all the rows whose status is "Single" or "Multiple".

我刚刚了解到 NULL 实际上等同于UNKNOWN".

I've been just reading up about NULL actually being equivalent to 'UNKNOWN'.

如果我说

SELECT *
FROM t_userstatus
WHERE status <> 'N/A'

我得到了结果(仅包含单个"或多个"的所有行).

I get the results (All rows containing "Single" or "Multiple" only).

我想知道的是,上面的 WHERE 子句是否总是排除具有 NULL 值的行?这是预期的行为吗?

What I would like to know is that , does the above WHERE clause always exclude the rows having NULL values?Is that the expected behaviour?

是什么导致即使我没有明确指定它也会排除空行?

在我的查询中,我是否必须明确说状态 IS NOT NULL ?

In my query,do I have to explicitly say status IS NOT NULL ?

我对编程比较陌生,不胜感激.

I am relatively new to programming, any help is appreciated.

推荐答案

SQL 使用 三值逻辑:真、假、未知.与 null 的任何比较都会导致 unknown.

SQL uses three-valued logic: true, false, and unknown. Any comparison to null results in unknown.

所以 null <>'N/A' 计算结果为 unknown.由于 unknown 不正确,这意味着该行被排除在外.

So null <> 'N/A' evaluates to unknown. Since unknown is not true, that means the row gets excluded.

相关文章