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 184.108.40.206.
To illustrate what I mean, consider the following SQL query using the ColdFusion
<!--- 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
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
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
"" in lieu of meaningful external references.
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?
The only reason I am saying this, is because at work, we always default INT columns to 0
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,
parentID column defaults to
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.
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?
.... 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
NULL pretty much has to be used.
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
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.
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.
@Adrian, I tend to do this to varchar columns:
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" .
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
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.