MySQL Comparison With Null Value

MySQL comparison with null value

In MySQL, NULL is considered as a 'missing, unknown value', as opposed to no value. Take a look at this MySQL Reference on NULL.

Any arithmetic comparison with NULL does not return true or false, but returns NULL instead., So, NULL != 'C' returns NULL, as opposed to returning true.

Any arithmetic comparison with 'NULL' will return false. To check this in SQL:

SELECT IF(NULL=123,'true','false') 

To check NULL values we need to use IS NULL & IS NOT NULL operator.

Unexpected NOT EQUAL TO NULL comparison in MySQL

According to MySQL Reference Manual, section 3.3.4.6: Working with NULL values the following is why:

Because the result of any arithmetic comparison with NULL is also
NULL, you cannot obtain any meaningful results from such comparisons.

In MySQL, 0 or NULL means false and anything else means true. The
default truth value from a boolean operation is 1.

This means that NULL != 'BHR' will evaluate to NULL, which in turn will mean false to MySQL. In order for the query to work as you want, you have to append OR city_code IS NULL to your query.

Comparing with NULL values

Any comparison with NULL yields NULL. To overcome this, there are three operators you can use:

• x IS NULL - determines whether left hand expression is NULL,
• x IS NOT NULL - like above, but the opposite,
• x <=> y - compares both operands for equality in a safe manner, i.e. NULL is seen as a normal value.

For your code, you might want to consider using the third option and go with the null safe comparison:

SELECT * FROM mycompare 
WHERE NOT(name <=> fname OR name <=> mname OR name <=> lname)

Comparing nulls during join

The values you have inserted are strings. 'NULL' is a string and is a definite value. To insert NULL you shouldn't use the quotes, for example:

INSERT INTO table (field1, field2) VALUES ('foo', NULL)

DEMO

And you can't compare with NULL, to understand its meaning it's something like undefined. Although, You can test if a value is or is not NULL.

include null values in date comparison mysql

This should work for you:

WHERE start_period_Date <= @periodDate 
AND (end_period_Date >= @periodDate OR end_period_Date IS NULL);

I would also always advise against using BETWEEN with dates.

The problem with your query is that you are limiting to one of two conditions:

1. @periodDate BETWEEN start_period_date AND end_period_date
2. end_period_date IS NULL

Either one can be true to return a row, since your second predicate has no reference at all to @periodDate then you will end up with all rows where end_period_date is null, regardless of the start_period_date.

Why NULL is not equal to anything is a false statement?

Almost any comparison to NULL returns NULL. This is because NULL has the semantics of "unknown value" rather than "missing".

When you have the comparison 9 <> NULL, then the returned value is NULL (for the comparison).

WHERE clauses treat NULL values as "false" so rows get filtered out. Similarly, CASE expressions treat NULL values as "false".

This is not always the case. CHECK constraints treat NULL values as "true", so the check constraint passes even when the values are NULL.

Null Comparison in MySQL

From the MySQL documentation at the Working with null values page:

The NULL value can be surprising until you get used to it.
Conceptually, NULL means “a missing unknown value” and it is treated
somewhat differently from other values.

since NULL is "unknown", the comparison NULL = NULL will be unknown as well and will return NULL, and since NULL is not TRUE your function will return 'false'.

You could use IS NULL operator:

SELECT IF(NULL IS NULL, 'true', 'false')

or you can also use then NULL-safe equal to operator:

SELECT IF(NULL <=> NULL, 'true', 'false')

MySQL using BETWEEN comparison with NULL

IFNULL might help out here:

WHERE A.event_date BETWEEN IFNULL(B.start_date,"1900-01-01") 
                   AND IFNULL(B.end_date,now());

MySQL Comparison with NULL in Where condition

Comparison operator = will return NULL if at-least one of the operand is NULL. So for the sentence_id = 3, you are getting NULL for all the three practice field conditions.

Also, NOT NULL is NULL; thus for sentence_id = 3, your Where condition will be TRUE AND NULL, which is equal to NULL, hence the row does not appear. Check more details about operators and their behavior at MySQL Documentation.

Solution 1: You can use Ifnull() function, to check for NULL returning from the practice field conditions, and set it to FALSE.

Try the following:

SELECT * FROM learnbox 
WHERE learn_status=0 
  AND NOT IFNULL((practice_1 = 1 AND 
                  practice_2 = 1 AND 
                  practice_3 = 1)
                 , FALSE)

Solution 2: You can also use null-safe equal operation (<=>). From documentation:

NULL-safe equal. This operator performs an equality comparison like
the = operator, but returns 1 rather than NULL if both operands are
NULL, and 0 rather than NULL if one operand is NULL.

So it will return 0 here for the practice field conditions.

You may try the following as well:

SELECT * FROM learnbox 
WHERE learn_status = 0 
  AND NOT (practice_1 <=> 1 AND 
           practice_2 <=> 1 AND 
           practice_3 <=> 1)

Comparing NULL value in MYSql

You could use the null safe comparison operator:

LocationId <=> IFNULL(LocId, LocationId)

The difference between the regular = operator is that the null value is not treated as special, e.g. if both left and right hand argument are null it will yield 1.

See also this answer for more examples.

---

Related Topics