CODEWITHSUNDEEP
sql
Bostonian
Bostonian

Reputation: 687

What expression could allow a tuple with NULL attribute to pass

For common expression like a + b > 5, it is obvious that as long as b or a is NULL, the the expression would not be true. In other words, the the null attribute could not pass this expression.

However, expression like coalesce could allow a tuple with NULL attribute to pass. For example, coalesce(a,b) > 0, even if a is NULL, as long as b > 0, then this tuple could still pass.

My question is that except coalesce, is there any other common expression could allow a tuple with null attribute to possibly pass the condition test?

Upvotes: 0

Views: 393

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Here are some standard expressions that pass for NULL values:

a is not null
a is distinct from 1
coalesce(a, 1) > 0
(case when a > 0 then 1 when a is null then 1 else 0 end)
a || 'abc' = 'abc'

Some databases have synonyms for coalesce()-like functionality -- nz(), ifnull(), nvl() come to mind.

Upvotes: 1

Related Questions