test on a value and not (test on a value) are false

Question

WITH a as 
     (  select       1  b,   null     c from dual)  
select       *  from     a

As expected: one line is returned

WITH a as 
     (  select       1  b,   null     c from dual)  
select       *  from     a
where c=1

As expected: no line is returned. c is null. therefore it can't be 1

WITH a as 
     (  select       1  b,   null     c from dual)  
select       *  from     a
where not(c=1 )

I didn't expected that. No lines are returned. c=1 is false. Therefore not (c=1) should be true

code

score 1 · Answer 1 · answered Jun 14 '22 at 06:16

1

Regarding your second query:

WITH a AS (
    SELECT 1 b, NULL c
    FROM dual
)

SELECT *
FROM a
WHERE NOT (c = 1);

The expression c = 1, when the value of c be null, is unknown. This means that it cannot be said for certainty whether c does, or does not, equal 1. As a result, the single record which your CTE defines is not returned for this query.

answered Jun 14 '22 at 06:16

Tim Biegeleisen

502,043
27
286
360

CTE: what is that? – Pierre-olivier Gendraud Jun 14 '22 at 06:27
1

CTE = common table expression. You used a CTE when you defined your SQL using `WITH`. – Tim Biegeleisen Jun 14 '22 at 06:33

score 1 · Accepted Answer · answered Jun 14 '22 at 06:17

NULL is the unknown value. You cannot compare it.

NULL = 1 results in "unknown"
NULL <> 1 results in "unknown"
NOT (NULL = 1) results in "unknown"

In a WHERE clause the condition must be true for the row to be returned, though.

One way to go about this is to explicitely consider nulls:

WHERE (c <> 1 OR c IS NULL)

Or (for the lack of standard SQL's IS DISTINCT FROM) with Oracle's DECODE:

WHERE DECODE(c, 1, 'same', 'different') = 'different'

test on a value and not (test on a value) are false

2 Answers2