SQL 查询 - 如何过滤空值或非空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5454342/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query - how do filter by null or not null
提问by 001
I want to filter a record....
我想过滤一条记录....
If statusid is null, filter the record (where statusId is not null)
如果 statusid 为 null，则过滤记录（其中 statusId 不为 null）
If statusid is not null, filter the record where statusid is equal to the specified statusid.
如果statusid 不为空，则过滤statusid 等于指定statusid 的记录。
How do I do this?
我该怎么做呢？
回答by JeremyWeir
Just like you said
就像你说的
select * from tbl where statusid is null
or
或者
select * from tbl where statusid is not null
If your statusid is not null, then it will be selected just fine when you have an actual value, no need for any "if" logic if that is what you were thinking
如果您的 statusid 不为空，那么当您有实际值时，它将被很好地选择，如果这是您的想法，则不需要任何“if”逻辑
select * from tbl where statusid = 123 -- the record(s) returned will not have null statusid
if you want to select where it is null or a value, try
如果要选择空值或值的位置，请尝试
select * from tbl where statusid = 123 or statusid is null
回答by Zachary Scott
How about statusid = statusid. Null is never equal to null.
statusid = statusid 怎么样？Null 永远不等于 null。
回答by Scherbius.com
WHERE something IS NULL
and
和
WHERE something IS NOT NULL
回答by JStead
set ansi_nulls off go select * from table t inner join otherTable o on t.statusid = o.statusid go set ansi_nulls on go
set ansi_nulls off go select * from table t inner join otherTable o on t.statusid = o.statusid go set ansi_nulls on go
回答by programmer
Wherever you are trying to check NULL value of a column, you should use
IS NULLand IS NOT NULL
You should not use =NULL or ==NULL
无论您在何处尝试检查列的 NULL 值，都应该使用
IS NULL和IS NOT NULL
您不应该使用=NULL 或 ==NULL
Example(NULL)
示例（空）
select * from user_registration where registered_time IS NULL
will return the rows with registered_timevalue is NULL
将返回registered_time值为的行 NULL
Example(NOT NULL)
示例（非空）
select * from user_registration where registered_time IS NOT NULL
will return the rows with registered_timevalue is NOT NULL
Note:The keyword null, not nulland isare not case sensitive.
将返回registered_time值为 is的行 NOT NULL
注意：关键字null, not nulland isare not case sensitive。
回答by pcofre
I think this could work:
我认为这可以工作：
select * from tbl where statusid = isnull(@statusid,statusid)
