ramblings on PHP, SQL, the web, politics, ultimate frisbee and what else is on in my life
back 1  2  »  

The NULL-safe equal to operator

Here is a little tid bit about a useful MySQL extension to the SQL standard, since I have given this same hint to several people in a short timeframe. A good friend from good old Berlin who runs a site for selling car parts was the first to ask, so I am giving him credit for bringing this nifty operator back to mind. Essentially the problem is that frequently we want to easily handle matching against some value or NULL with the same code. Obviously the standard expects you to switch from the standard equal operator to "IS" for matching a NULL. After all NULL is not equal to anything. Right, but its tedious to handle this in the code so MySQL invented the "<=>" operator aka the "NULL-safe equal to operator". Also very useful in combination with prepared statements.

Side note: I must admit that I keep bashing proprietary vendors for inventing bogus non standard syntax while I am praising OSS projects for some of their syntax inventions, but I think its not really being hypocritical, but the mere fact that OSS guys invent much more down to earth syntax that solve frequent real world problems, while the proprietary guys seem to focus on obscure stuff that gets them the wow factor at their launch events.


Re: The NULL-safe equal to operator

News flash: MySQL is a proprietary vendor.

While I'll grant that it can be annoying to have to alter code to handle IS NULL vs. = NULL, chances are that there is a reason for this syntax difference -- and heck it might even be a good reason. This is a good example of why MySQL is often called a developer's database (instead of a DBA's database, for instance). Obviously, for people whose primary concern is writing code to build SQL statements this type of change "makes sense", while it may make absolutely no sense from the perspective of someone that cares about the types and values of data stored in an RDBMS.

Re: The NULL-safe equal to operator

What's wrong with coalesce()?

Re: The NULL-safe equal to operator

When I want to do a simple comparison I much rather prefer an operator than a function call, its much more expressive and surely faster too.

What I am talking about is stuff like the following


$sql = "SELECT * FROM foo WHERE bar <=> ? OR baz <=> ?";
$stmt = $db->prepare($sql);
$stmt->execute(array(null, 123));
$stmt->execute(array('bla', null));
$stmt->execute(array('grr', 42));
$stmt->execute(array(null, null));


Also I think this only allows me to handle the situation where I have a NULL on the input side, but what if I want to actually match a NULL in the table with one in my query? For example what if I have NULL's in bar and baz?

Re: The NULL-safe equal to operator

If the column permits NULLs, you need to handle them as distinct from ordinary values.

If you don't want to handle them, define the column NOT NULL. That would eliminate all the complexity of logical comparisons and query parameters.

I would even say that most columns should be defined NOT NULL as a matter of habit, unless you have a specific purpose for NULL in that column. Writers like Joe Celko make similar recommendations.

Re: The NULL-safe equal to operator

I actually make it a habit to make most of my columns NULLable, why? Because most web applications are about inviting people to provide as much content as they want and still operate. At the same time I want to remember if they have or have not entered anything explicitly.

Re: The NULL-safe equal to operator

This is the kind of thing that really bugs me... people who I think are smart (Lukas) take entirely defensible positions (vendors should try not to make up syntax), and then completely compromise those positions because they like mysql and mysql happens to do it wrong (but it's developer friendly!).

What you should be doing is railing on MySQL for inventing kooky syntax like <=>, rather than using the far more ANSI friendly syntax "IS DISTINCT FROM", which does just what you need (aka, it's "developer friendly"). Look it up, it's support in "real open source databases"... like Firebird.

Re: The NULL-safe equal to operator

Careful, there is a known bug when using the NULL-safe equal to operator: it doesn't work for datetime or timestamp columns:


Re: The NULL-safe equal to operator

Its not really about me liking MySQL. Its about me liking simple solutions for simple problems. Take LIMIT as an example. Sure all the other guys could do a LIMIT with a funky subquery.

I did not know about "IS DISTINCT FROM", its not quite as extreme as the convoluted subqueries for LIMIT, but it still strikes me as ueber-fancy, where something simple like "<=>" would have done. That being said "IS DISTINCT FROM" is probably the right thing, more flexible and I guess its in some version of the standard (seems like SQL2003)? Standards are good, I like standards, I think I just do not like the people that get to work on the SQL standard these days (cannot find the blog post from Josh about LIMIT finally making it into the standard, with a different syntax than all OSS RDBMS, but WTF?).

Re: The NULL-safe equal to operator

Oh, I should say that I am still very much fond of the idea of us OSS friendly RDBMS-guys getting together to drive more real world SQL standard extensions, rather than letting the guys do this, that invent features just because they need some for the next release.

Before you can post a comment please solve the following captcha.

your name:

1  2  »