16

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?

Paulie-C
  • 1,674
  • 1
  • 13
  • 29
fields
  • 879
  • 2
  • 9
  • 19

6 Answers6

19

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.

almcnicoll
  • 405
  • 3
  • 21
  • Nice example. To paraphrase and possibly elaborate. `null` expressions can be useful when fields have a finite number of possible values, however allowing the fields to be undefined until a later point in the application's execution? – Paulie-C Jun 23 '17 at 15:24
  • I'd go a step further - I don't see why they're only useful with a finite number of values either (obviously all SQL fields have a finite number of values, but I'm assuming you mean a _small_ finite number!) Null values work well in all kinds of cases where data values are not known (whether or not they might later be known). – almcnicoll Jul 13 '17 at 15:13
12

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).

Simon Forsberg
  • 13,086
  • 10
  • 64
  • 108
4

Who really needs an operator that is effectively undefined with built in language types?

you also need it for relations inside your database. specially if you are using foreign key constraints.

for example if you have a table for tasks (in your company). then you assign these tasks to employees. so you have a relation from your tasks-table to your employees-table.
and there will always be some unassigned tasks. in this case the field in your tasks-table you use for the relation to the employees table will contain NULL. this will make sure, that this task is unassigned. which means: there is no possibility that there is a relation to the employees table.

if NULL = NULL would be true, then in my example there would be always the possibility that the foreign key in the employees table also is NULL. thus the task would be assigned to one or some employees. and you never would be able to know for sure wheter a task is assigned to some employee or not.

low_rents
  • 4,481
  • 3
  • 27
  • 55
  • 1
    I don't think this is a valid example; you'd need a NULL for a primary key for this to happen. (when the fk is NULL that's only one side) – Steve Horvath Feb 06 '20 at 03:58
4

Yes.

This must be because relational databases use the theory of three-valued logic (TRUE, NULL, FALSE).

And the three-valued logic must work so because it must be internally consistent.

It follows from the rules of mathematics.

Comparisons with NULL and the three-valued logic

simhumileco
  • 31,877
  • 16
  • 137
  • 115
3

Is there a reason MySql has both and not JUST the functionality in <=> ? The operators are completely different from each other.

<=> 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.

Who really needs an operator that is effectively undefined with built in language types?

This depends on case, just because you haven't encountered such cases, does not mean nobody needs it.

Starx
  • 77,474
  • 47
  • 185
  • 261
  • Can anyone think of any cases? Because I cannot think back to where I could have used the ='s behavior over <=>'s in over a decade of professional development. – fields Apr 05 '12 at 23:59
  • I could see the use if ='s provided a different behavior but it provides NO behavior when used with NULL since the result is always null as long as null is in the expression.. – fields Apr 06 '12 at 00:00
  • 2
    @cellige It provides no (reasonable, useful) behavior when used with a NULL literal. But when comparing two non-literal and nullable expressions, it definitely does something which someone might reasonably want it to. – Brett Widmeier Sep 05 '14 at 15:57
0

When this <=> null safe equals operator combines with NOT operator, it fits to real-world use cases. Example to return records that are not dormant:

WHERE NOT dormant_status <=> 'Y'

it is equivalent to

WHERE dormant_status IS DISTINCT FROM 'Y' (only valid in certain databases)

or

WHERE dormant_status <> 'Y' OR dormant_status IS NULL

Lee Chee Kiam
  • 11,450
  • 10
  • 65
  • 87