IF NULL THEN statement

Databases
1

the IF statement can’t recognize a “NULL” in a column of INT?


FETCH c INTO x;

# lets say the first x = NULL.

IF x = NULL THEN
...
END IF;

That IF statement won’t execute

next when:
x=1

IF x = 1 THEN
 ...
 END IF;

the If statement executes. What’s up with that?

2

NULL is never equal to anything else not even NULL.

use ISNULL() to test if something is NULL.

3

i prefer COALESCE :slight_smile:

4

Are you sure it’s NULL and not empty?

@felgall, I use

	if ( mysql_result($result,$i,'fieldname') != NULL )
	{
		echo "..... mysql_result($result,$i,'fieldname') .....

and it seems to work OK. That is, it doesn’t echo the "…"s when the field is NULL

5

A mySQL NULL value and a PHP NULL value are not exactly the same thing and it may have something to do with the difference between the way NULLs are implemented in both. I think you’ll find that trying to compare to NULL within the SQL is where it will not work. I was assuming that the query was with regard to testing the field for NULL in the SQL itself.

6

:d’oh: of course. I’m too darn PHP-centric at times.
I tried finding info on FETCH without any luck (yet), but I did see documentation for queries like
Where field IS NULL
so the “IS” instead of the “=” is what to use for MySQL as far as NULLs are concerned.

7

FETCH is used where you have opened a cursor to hold the results of a query and you want to return the next record from the results in the cursor.

Either x IS NULL or ISNULL(x) should work to test if x contains null. I suppose which of them to use would depend on which fitted better with the way the surrounding SQL is coded. I wouldn’t expect it would make any difference to the efficiency of the processing (although I could be wrong as I have never actually tested it).

8

and every other database system, too :slight_smile:

9

thanks everyone, i understand now, thanks for the suggestions.