Preserved Topic: NULL MySQL values.

I seem to recall in a past message someone making an offhanded comment about how using NULL values in a MySQL table is questionable practice. I think I understand why now. It would seem you can not use null in a where clause, err, you can, but it doesn't match with null values.

Does anyone know anything about this? Is there any way to match null values? Luckily I'm still in the coding stage of my project, and so it will be easy to fill my tables with empty strings rather than null.

-jiblet

You can use null in a where clause, but not with regular operators (i.e., =, !=) you must use IS NULL or IS NOT NULL when trying to match null values in tables.

- Resolutions, Of All My Fruitless Searches -

Damn fine answer.

Are the "IS NOT NULL" and related constructs legal ANSI SQL, or MySQL extensions?

Hm, thanks, sadly that adds a lot of complexity to my PHP scripts since any one of 20 fields could be null. Luckily the empty string solution seems to work great as long as I reserve the integer 0 only for 'empty' fields.

Does someone know why the empty string is not considered null in MySQL?

-jiblet

Because NULL does not have a definable value. The only way to describe null, is that it has no value, which is why you can't use mathematical operators to test for null, you can't compare something that has no legal value.

On the other hand, an empty string has a value, it's "", and can be compared to a string with something in between the quotes. Hope my explination makes sense for you.

Linear

I don't know if it's ANSI legal or not, the extent of my experience with a database is solely MySQL.

Sorry

- Resolutions, Of All My Fruitless Searches -

I have used test for "Is NULL" and "Is Not NULL" on MySQL, Sybase, and Access. I don't know if it's ANSII standard, but a lot of databases seem to use it.