SQL Queries using Null fields -- Must use "is null" or "is not null"?

[WWGD]

Hi all,
This is for SQL queries, sorry ran out of space in the title.
Say we are doing a query that includes a null field/attribute. Must we always use either an " Is null" or
" Is not null" statement?
Thanks.

 

[Greg Bernhardt]

Depends if that factor is important for your result. I don't think there is a requirement. Are you getting errors?

 

[WWGD]

Depends if that factor is important for your result. I don't think there is a requirement. Are you getting errors?

Hi Greg, no, I am learning the topic and the source material did not seem clear on this, i.e., on whether we should use, for the null field 'field' either "where field is null" , or " where field is not null".

 

[Greg Bernhardt]

whether we should use, for the null field 'field' either "where field is null" , or " where field is not null".

Again depends on what result you are after. If you want to factor in nulls for a field then use IS NULL, if you want to factor in records that have a field that is not null, then use IS NOT NULL. If you don't care, then don't add the clause :)

 

[jedishrfu]

Here's more about SQL nulls

http://www-cs-students.stanford.edu/~wlam/compsci/sqlnulls

Null behavior is kind of funny compared to regular programming languages especially in using equality tests or in table joins.

 

[jim mcnamara]

NULL is not equal to anything - including itself - what jedishrfu is telling you. When you employ a where clause you are looking for a resultset that matches what you are looking for. There may be some 'yellow' values but you do have to say '!= yellow', just say '=red' instead.

So you can ignore NULLS

Except when the select statement uses some kind of function and operates on a field that can be NULL. You cannot have a NULL in a function, so you must either exclude those rows or wrap the result in a default NVL() function to transform the NULL to something usage.

select UPPER(NVL(column_name, 'dummy')) from some_table;

In this pretend example upper will barf if column_name returns a NULL, so you throw some 'dummy' value out there to feed it a usable value when the column_name is NULL.

All indexed columns in a table should have the NOT NULL property. For the same reason.

 

[newjerseyrunner]

When people first get into computer science, they have trouble with certain special values. The concept that x = x is not necessarily true is hard to grasp, it's even worse, when that equal sign doesn't even resolve to true or false.

In most computer languages NULL is a constant, and it does equal itself. That's not true in SQL.

PHP

echo (NULL == NULL) ? 'true' : 'false';
echo (NULL === NULL) ? 'true' : 'false';
echo (NULL == 0) ? 'true' : 'false';
echo (NULL === 0) ? 'true' : 'false';
echo (NULL == 1) ? 'true' : 'false';
echo (NULL === 1) ? 'true' : 'false';

Will spit out
true
true
true
false
false
falseMySQL

SELECT NULL = NULL;
SELECT NULL IS NULL;
SELECT NULL = 0;
SELECT NULL = 1;
SELECT 0 = 0;
SELECT 0 = 1;

Will spit out
NULL
1
NULL
NULL
1
0