Menu
I have a table with a nullable varchar column. When selecting rows and specifying that I want rows with a value not equal to a given string, it does NOT return the rows where the value is null.
For example:
## if `value` is null, that row is ignored
SELECT * FROM test_table WHERE value != 'some string'
I'd like to understand why that's happening.
Example: http://sqlfiddle.com/#!2/83f0d/1
597
To search for column values that are NULL , you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true for any expression: mysql> SELECT * FROM my_table WHERE phone = NULL; To look for NULL values, you must use the IS NULL test.
If your select statement returns one column, but no rows, NULL will be returned.
ISNULL() function returns true if argument is a NULL value, while IFNULL() returns the first argument if it is non-null.
In ANSI SQL, NULL is neither equal to nor unequal to any value, including itself.
NULL = 'foo'
NULL != 'foo'
NULL = NULL
NULL != NULL
all evaluate to NULL. To test for nullness you must use is null or is not null in your query.
68
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
