Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Katie Maher
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Katie Maher

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

By Ben Nadel on
Tags: ColdFusion, SQL

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.



Reader 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?

Reply to this Comment

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

Reply to this Comment

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?

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.