SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Wednesday, May 17, 2006

NULLs in Ingres

SQL Apprentice Question
I'm seeing SQL like this work fine:

UPDATE topics SET bonus_time=NULL, last_read=NULL WHERE id=3

While SQL like this fails:

SELECT * FROM topics WHERE (topics.parent_id = NULL) AND ( (topics.type
= 'Reply' OR topics.type = 'SillyReply' ) )

With this error: 'line 1, Column 'null' not found in any specified
table.'


So, I can set a value equal to NULL but I can't seach on NULL?


I'm starting to read about the ifnull ingres SQL command but thought
I'd ping the group about the "proper" way to use a NULL in a WHERE
statement. Is it simply not possible or is there a special way to
escape it?


Celko Answers
"SET x = NULL" is an assignment clause.
"WHERE x = NULL" is a search condition

The nature of NULLs is to propagate and they do not compare to anything
because they are ,arkers and have no values or data type. To help the
SQL engine you can CAST (NULL AS ), however.


I have a good section on them in SQL FOR SMARTIES.

No comments: