Menu
MySQL provides a nice operator <=> that works with comparisons that could contain a null such as null <=> null or null <=> 5 etc. giving back intuitive results as many programming languages. Whereas the normal equals operator always just returns null, which catches many new MySQL users such as myself awry.
Is there a reason MySQL has both and not JUST the functionality in <=> ? Who really needs an operator that is effectively undefined with built in language types?
423
No its not the same as null means a value that is unavailable unassigned or unknown and zero is a defined value.
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 . The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.
Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. 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.
NULL means you do not have to provide a value for the field... NOT NULL means you must provide a value for the fields. Outside of JDBC, MySQL lets you omit value for a NOT NULL column if there is a DEFAULT on the column.
Who really needs an operator that is effectively undefined with built in language types?
You asked for some real-world examples. Here's a spurious one. Let's say that you have a residential youth programme or similar, and one of the requirements is that the kids only share a room with someone of the same sex. You have a nullable M/F field in your database - nullable because your data feed is incomplete (you're still chasing down some of the data). Your room-matching code should definitely not match students where t1.Gender<=>t2.Gender, because it could end up matching two kids of unknown gender, who might be of opposite genders. Instead, you match where they're equal and not both null.
That's just one example. I admit that the behaviour of NULL and the = operator have caused a lot of confusion over the years, but ultimately the fault probably lies with the plethora of online MySQL tutorials that make no mention of how NULL interacts with operators, nor of the existence of the <=> operator.
104
The big difference between null in mySQL and in programming languages is that in mySQL, null means unknown value while in programming it means undefined value.
In mySQL, null does not equal null (unknown does not equal unknown). While in programming languages, null does equal null (undefined equals undefined).
30
answered Sep 29 '22 14:09
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
