Skip to main content
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Ryan Scheuermann
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Ryan Scheuermann

Using MySQL's Null-Safe Equality Operator With CFQueryParam's Null Option In Lucee CFML 5.3.3.62

By
Published in , Comments (10)

The other day, I was in the middle of writing a "typical" conditional MySQL query that had special handling for NULL values when it suddenly occurred to me that I was doing more work than I had to. I don't work with NULL values very often in SQL and ColdFusion; so, I fear that my instincts for them have never been honed properly. That said, in a moment of clarity, it occurred to me that I can use MySQL's Null-Safe equality operator in conjunction with the null attribute of the CFQueryParam tag to simplify certain types of prepared SQL statements in Lucee CFML 5.3.3.62.

To illustrate what I mean, consider the following SQL query using the ColdFusion CFQuery tag:

<!--- Mock out some search values. --->
<cfset filterProjectID = 9 />
<cfset filterParentID = 0 />

<cfquery name="test" datasource="testing">
	SELECT
		f.*
	FROM
		asset_folder f
	WHERE
		f.projectID = <cfqueryparam value="#filterProjectID#" sqltype="integer" />

	<!---
		Since we can't compare a NULL value using the "=" operator, we have to create
		two different logic branches in order to handle the NULL value specially.
	--->
	<cfif filterParentID>
		
		AND
			f.parentID = <cfqueryparam value="#filterParentID#" sqltype="integer" />

	<cfelse>

		AND
			f.parentID IS NULL

	</cfif>
	;
</cfquery>

For the sake of this discussion, imagine that this query is being run as part of a dynamic filter where the filterParentID is optional and defaults to 0. In this query, the parentID column is either a NULL value or an INT value. And so, if the filter value is 0, I have to use the IS NULL comparison; and, if the filter value is non-zero, I have to use the traditional equality operator.

The reason for this dichotomy is that the following comparison in MySQL will always result in NULL, even if the parentID column is NULL:

f.parentID = NULL

At least in MySQL, NULL never equals anything - not even another NULL value. Which is why I have to conditionally use the IS operator instead of the = operator.

That said, MySQL does have a Null-Safe equality operator: <=>. This operator can safely compare one NULL value to another NULL value. And this is where my epiphany was: I can use the Null-Safe operator to simply the above query:

<!--- Mock out some search values. --->
<cfset filterProjectID = 9 />
<cfset filterParentID = 0 />

<cfquery name="test" datasource="testing">
	SELECT
		f.*
	FROM
		asset_folder f
	WHERE
		f.projectID = <cfqueryparam value="#filterProjectID#" sqltype="integer" />

	<!---
		With the NULL-Safe equality operator, NULL = NULL results in "1", which means
		that the following condition works for both NULL and non-NULL values.
	--->
	AND
		f.parentID <=> <cfqueryparam value="#filterParentID#" sqltype="integer" null="#( ! filterParentID )#" />
	;
</cfquery>

As you can see, no more branching logic in my CFQuery tag. I have a single Null-Safe condition for the f.parentID column. And, my CFQueryParam tag will yield a NULL value using the null attribute if the filterParentID is zero. Easy peasy!

Now, not all conditional filter queries work this way. For example, if my filterParentID value defaulted to null instead of 0, then my CFQueryParam tag would have to become a bit more complex:

<!--- Mock out some search values. --->
<cfset filterProjectID = 9 />
<cfset filterParentID = nullValue() />

<cfquery name="test" datasource="testing">
	SELECT
		f.*
	FROM
		asset_folder f
	WHERE
		f.projectID = <cfqueryparam value="#filterProjectID#" sqltype="integer" />

	<!---
		With the NULL-Safe equality operator, NULL = NULL results in "1", which means
		that the following condition works for both NULL and non-NULL values.
	--->
	AND
		f.parentID <=> <cfqueryparam value="#( filterParentID ?: 0 )#" sqltype="integer" null="#isNull( filterParentID )#" />
	;
</cfquery>

In this case, I can't just use the filterParentID value or I would get a ColdFusion null-reference error. Instead, I have to use the Elvis Operator to safely consume the filterParentID value in the value attribute. Then, I use the isNull() function to determine whether or not the CFQueryParam tag yields a NULL value. It's not quite as pretty as the query before it; but, it's still doable.

I can't believe that I've been using MySQL for over a decade and this is literally the first time that it has occurred to me that I can use the Null-Safe equality operator within my CFQuery code in ColdFusion. Though, to be fair, I hardly ever use NULL values. And, not all contexts that involve NULL values will be able to leverage this technique. But, for this particular type of query, it works quite nicely.

ASIDE: Technically, I have used the MySQL <=> operator before, when looking at how to keep prepared statements consistent even with dynamic parameterized queries in ColdFusion. But, that was a proof-of-concept - I've yet to use that type of technique in a production app.

Epilogue / SoapBox On NULL Values In SQL

Almost 15-years ago, I wrote about how little I enjoy using NULL values in SQL. And, in the many years of writing large, SQL-intensive ColdFusion applications since that post, I can report that nothing has changed. I still try to avoid NULL as much as possible, delegating it to Date/Time stamps and soft-delete techniques that leverage uniqueness constraints.

For the most part, I find that NULL values offer little more than academic ceremony; and tend to over-complicate queries (as demonstrated by this post). As such, I still encourage people to avoid NULL values whenever possible; and, instead, leverage values like 0 and "" in lieu of meaningful external references.

Want to use code from this post? Check out the license.

Reader Comments

449 Comments

Just out of interest, can you not just compare the zero:

f.parentID = <cfqueryparam value="0" sqltype="integer" />

So you could just do:

f.parentID = <cfqueryparam value="#filterParentID#" sqltype="integer" />

Assuming filterParentID is always an integer. If it isn't you could do something like:

f.parentID = <cfqueryparam value="#Val(filterParentID)#" sqltype="integer" />

I am pretty sure I have done this before in MySQL, but I can't say this with 100% certainty?

I am assuming your parentID column defaults to 0 rather than NULL

Or maybe I have missed something?

15,902 Comments

@Charles,

So, if I wrote the code, then yes, I could compare to Zero as the parentID column would be an INT that defaults to 0 if it was the "root" folder. Unfortunately, the parentID column defaults to NULL, not 0 :( As such, attempting to compare it anything other than NULL would result in a potentially false negative. And, this is exactly why I try to use 0 instead of NULL as a column default wherever humanly possible.

449 Comments

Oh I see. Sorry. I missed that column default was NULL.

That's makes perfect sense.

Just out of interest, what is the advantage of setting an INT column default to NULL?

Our company sets all of these column defaults to 0, but I would like to know whether this is correct or not?

15,902 Comments

@Charles,

.... what is the advantage of setting an INT column default to NULL?

In my opinion, there is no advantage. I think people who care about being "academically correct" like to use this technique because they will argue that an "unknown" value is different than a "default" value. But, they can argue that until they are blue in the face - it doesn't give it any practical value.

Now, that's not to say that NULL is bad - it definitely has it's use-cases, where you do need to differentiate an "unset" value (such as with a deletedAt date), where a NULL pretty much has to be used.

449 Comments

That's very interesting information. I will bear that in mind, in future.

I think I tend to use 0, because I am still not too confident about how to deal with NULL values, in general!
I mean the very definition of NULL is like an existential nightmare!!!

But, this tutorial has been very useful, for the future, in case I have a scenario, where I have to use a NULL column default.

6 Comments

Normally, I would just have cfif statements in the cfquery so this was my first time digging deeper into a nicer solution.

This solution did work for me. Thanks for that.

I just wished <cfqueryparam NULL="true".../> would just null if value is an empty string instead of having to provide the logic on our end. Just my opinion.

6 Comments

@Charles,

it's a good solution...I'm just saying it would be nice IMO, if that logic was built-in "under the hood" and activated with NULL="True|Yes" .

15,902 Comments

@Adrian,

It's hard to make the cfqueryparam too smart as there's so much variety to how the underlying database schema is actually structured. I personally try to stay away from NULL fields as much as possible and just use "sane defaults" instead (like empty string and zero). This way, we never have to worry about null-comparisons and the queries become much more simple.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel