I am just curious - I know about NULL safe equal operator <=>, but is there some NULL safe NOT equal operator? Or I have to always use something like this:
(tab.id != 1 OR tab.id IS NULL)
or someone prefers
!(tab.id <=> 1)
Add a comment
|
4 Answers
I found that NOT (NULL <=> 1) works and I think it is also ISO standard compliant, but is cumbersome. A better way to show using column names would be like this: NOT (tbl.col1 <=> 1)
1 Comment
Bill Karwin
<=> is not standard SQL, it's a MySQL extension. The standard equivalent null-safe comparison is, typically for SQL, much more verbose: IS [NOT] DISTINCT FROM.COALESCE(tab.id, 0) != 1
Can be used here if you like it. I goes through the parameters, and returns the first value that isn't NULL. In this case if it's NULL, it will compare 0 != 1. Although it may use more signs, it's still easier to manage instead of being forced to always have opposite "booleans" as a solution in those cases.
Read documentation for COALESCE()
4 Comments
Craig Jacobs
I think this is the preferred method as this query will also function on SQL Server too. The best method is to not have nulls and have a proper default , but the source data cannot always be controlled by the developer.
Arth
Unfortunately if you don't know the value on the RHS of this comparison, you don't know what to COALESCE() your NULL to!
Peter de Groot
This works if you want to compare tab.id with a value but it does not work if you want to compare two fields like tab.id with tab_childs.tab_id.
You Old Fool
For what it's worth, the method outlined in another answer
!(tab.id <=> 1) seems to get a performance boost as compared to this one. I ran explain on a table with a few thousand rows and the former gets a better value in the filtered column.Now MySQL does not have a NULL-safe not equal operator.
Using MySQL the most universal solution is:
!(tab.id <=> 1)
or
NOT tab.id <=> 1
because it will work properly if even in place of 1 you will use NULL.
Comments
If you know that the RHS of the comparison IS NOT NULL:
COALESCE(tab.id != 1, 1)
Or
COALESCE(tab.id != 1, TRUE)
will give you the correct result.
Whether this is better more readable than:
(tab.id != 1 OR tab.id IS NULL)
Is debatable..
I'd also be tempted to filter out the NULL logic first with comparisons, so I don't have to think about them! I'd probably write your first equation as:
(tab.id IS NULL OR tab.id != 1)
Performance will depend on prevalence of NULLs however.
1 Comment
törzsmókus
“Whether this is better more readable (…) is debatable” it clearly is when the expression is far more complex than
tab.id