如何在Oracle中使null等于null [英] How to make null equal null in oracle

问题描述

我有一个变量传递给我存储的proc，它(基本上)是一个过滤器.但是，该字段有时可以为空，如果是的话，我希望能够检查具有该字段为空的行.

例如，

表A:

VALUE_COLUMN | FILTER_COLUMN
----------------------------
A            |  (NULL)
B            |  (NULL)           
C            |  (NULL)
D            |  (NULL)
A            |  1
E            |  (NULL)
F            |  (NULL)
B            |  1

查询(带有输入，val，过滤器):

SELECT  COUNT(1)
  FROM    TableA
WHERE 
  wrap_up_cd = val
  AND brn_brand_id = filter

预期的I/O:

val = A, filter = (null) = 1
val = A, filter = 1 = 1
val = C, filter = 1 = 0

我如何使Oracle表现为这种方式?

解决方案

怎么样:

SELECT  COUNT(1)
  FROM    TableA
WHERE 
  wrap_up_cd = val
  AND ((brn_brand_id = filter) OR (brn_brand_id IS NULL AND filter IS NULL))

我不是Oracle专家，但我期望可以工作-如果过滤器和的值均为NULL，则基本上使查询匹配. /p>

I have a variable being passed to my stored proc and it's a filter (basically). However, that field can sometimes be null, and if it is, I want to be able to check against the rows that have that field as null.

For example,

Table A:

VALUE_COLUMN | FILTER_COLUMN
----------------------------
A            |  (NULL)
B            |  (NULL)           
C            |  (NULL)
D            |  (NULL)
A            |  1
E            |  (NULL)
F            |  (NULL)
B            |  1

The query (With inputs, val, filter):

SELECT  COUNT(1)
  FROM    TableA
WHERE 
  wrap_up_cd = val
  AND brn_brand_id = filter

Expected I/O:

val = A, filter = (null) = 1
val = A, filter = 1 = 1
val = C, filter = 1 = 0

How can I make Oracle behave this way?

解决方案

How about:

SELECT  COUNT(1)
  FROM    TableA
WHERE 
  wrap_up_cd = val
  AND ((brn_brand_id = filter) OR (brn_brand_id IS NULL AND filter IS NULL))

I'm not an Oracle expert, but I'd expect that to work - basically make the query match if both the filter and the value are NULL.

这篇关于如何在Oracle中使null等于null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！