October 29, 2015 at 9:53 am
I have always (or at least intended to) treat NULL and empty strings separately in my SQL querying history. Now I have run across something that mystifies me (but probably shouldn't) that I would like an explanation for.
Consider this bit o' code:
DECLARE @ORDER CHAR(10)
SET @ORDER=
(
SELECT NULL
)
IF @ORDER <> ''
PRINT 'Not an empty string'
IF @ORDER IS NULL
PRINT 'It is NULL'
Run this and you will get:
It is NULL
I was expecting:
Not an empty string
It is NULL
Why is NULL not passing the 'not an empty string' test? In other words, how does NULL = '' ? Is NULL cast to an empty string for this comparison?
Hopefully my feeling mystified is just from not enough caffeine yet.
October 29, 2015 at 9:59 am
SQL uses three-value logic. So anything = NULL evaluates to False and anything <> NULL also evaluates to FALSE. Also, NULL = NULL is FALSE as is NULL <> NULL
Gerald Britton, Pluralsight courses
October 29, 2015 at 10:00 am
seware74 (10/29/2015)
I have always (or at least intended to) treat NULL and empty strings separately in my SQL querying history. Now I have run across something that mystifies me (but probably shouldn't) that I would like an explanation for.Consider this bit o' code:
DECLARE @ORDER CHAR(10)
SET @ORDER=
(
SELECT NULL
)
IF @ORDER <> ''
PRINT 'Not an empty string'
IF @ORDER IS NULL
PRINT 'It is NULL'
Run this and you will get:
It is NULL
I was expecting:
Not an empty string
It is NULL
Why is NULL not passing the 'not an empty string' test? In other words, how does NULL = '' ? Is NULL cast to an empty string for this comparison?
Hopefully my feeling mystified is just from not enough caffeine yet.
Because NULL isn't an empty string. It is unknown. It might be an empty string or it might be a watermelon. Since there is no value there is no way to know. As such, any equality check done with NULL on either side will return NULL. So when you say where NULL <> '' it doesn't return true or false, it returns NULL. Think of NULL as the same as NaN. When checking for NULL you must use ISNULL or COALESCE. Does that help?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2015 at 10:02 am
g.britton (10/29/2015)
SQL uses three-value logic. So anything = NULL evaluates to False and anything <> NULL also evaluates to FALSE. Also, NULL = NULL is FALSE as is NULL <> NULL
Actually, in both cases it evaluates to UNKNOWN because NULL isn't different or equal to anything.
October 29, 2015 at 10:07 am
Setting ANSI_NULLS off would give you the expected results. But I won't recommend on doing that other than general testing for additional knowledge.
:exclamation:Important
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
SET ANSI_NULLS OFF
DECLARE @ORDER CHAR(10)
SET @ORDER=
(
SELECT NULL
)
IF @ORDER <> ''
PRINT 'Not an empty string'
IF @ORDER IS NULL
PRINT 'It is NULL'
October 29, 2015 at 10:31 am
Luis Cazares (10/29/2015)
g.britton (10/29/2015)
SQL uses three-value logic. So anything = NULL evaluates to False and anything <> NULL also evaluates to FALSE. Also, NULL = NULL is FALSE as is NULL <> NULLActually, in both cases it evaluates to UNKNOWN because NULL isn't different or equal to anything.
This is about the easiest way to remember that I know of. JBM states it similar to this too, something like "NULL isn't equal to anything. It isn't NOT equal to anything, either"
Yet, we have this:
SET @var = NULL
to confuse the unwary 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 29, 2015 at 10:56 am
This is about the easiest way to remember that I know of. JBM states it similar to this too, something like "NULL isn't equal to anything. It isn't NOT equal to anything, either"
Yet, we have this:
SET @var = NULL
to confuse the unwary
Thanks very much all... I guess I have never run into this because I have always explicitly checked for IS NULL or IS NOT NULL (which I sheepishly :blush: admit makes me one of the unwary). I surmise it is by accident that the actual piece of code I am evaluating is working as intended, as I am certain the writer did not have the knowledge you all have shared. (3rd party db, originally all heaps, lots of RBAR... you get the picture. <sigh>:blink:) or else he was brilliant and I am but a mere mortal.
Thanks again for the enlightenment! (which is why I come to SSC!)
October 29, 2015 at 11:32 am
Luis Cazares (10/29/2015)
g.britton (10/29/2015)
SQL uses three-value logic. So anything = NULL evaluates to False and anything <> NULL also evaluates to FALSE. Also, NULL = NULL is FALSE as is NULL <> NULLActually, in both cases it evaluates to UNKNOWN because NULL isn't different or equal to anything.
True, though a little pedantic. In any case it makes no difference here. An IF statement only has two options, not three (the 'Then' clause and the 'Else' clause, unless you have SET FMTONLY ON). The first is executed if the expression returns True; the second if the expression does not return True (i.e. either FALSE or UNKNOWN). So, in the context of an IF statement, where two-value logic is required, NULL = NULL returns the equivalent of FALSE, which is good enough for practical purposes.
FWIW I wish we could access the intermediate results of Boolean expressions. e.g. I wish this worked:
select case when (NULL = NULL) is NULL then 'Unknown' end
For that matter I wish we had true Boolean variables. But I dream.
Gerald Britton, Pluralsight courses
October 29, 2015 at 11:53 am
For that matter I wish we had true Boolean variables. But I dream.
I wish I didn't have to try and unravel performance issues from databases servers I know nothing about. But I dream also.:hehe:
October 29, 2015 at 12:16 pm
Just as a side note:
I don't know on what RDMS you learned SQL, but at least in Oracle, NULL and empty string '' are evaluated as equivalent. Also, by default on most any platform, a NULL cannot be equal, less than, or greater than anything, not even itself. Therefore, the condition '' = ''evaluates to False in Oracle. For those of you used to working exclusively with SQL Server, think about that one for a minute. It can be a potential pitfall for those who context switch between SQL Server and Oracle.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 29, 2015 at 2:07 pm
g.britton (10/29/2015)
For that matter I wish we had true Boolean variables. But I dream.
We do.
create table MyTrueBoolean
(
TrueBool bit not null
)
If you have a bit column and no value is provided would have it be True or False??? That is why the bit datatype allows NULL. With no known value it can't logically be either.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2015 at 2:31 pm
Sean Lange (10/29/2015)
g.britton (10/29/2015)
For that matter I wish we had true Boolean variables. But I dream.We do.
create table MyTrueBoolean
(
TrueBool bit not null
)
If you have a bit column and no value is provided would have it be True or False??? That is why the bit datatype allows NULL. With no known value it can't logically be either.
No we don't. Bit expressions cannot be used in place of Boolean expressions. Try the following:
DECLARE @MyPseudoBoolean BIT = 1;
SELECT GETDATE()
WHERE @MyPseudoBoolean
And you will get an error "An expression of non-boolean type specified in a context where a condition is expected, near '@MyPseudoBoolean'." Here the non-boolean type is bit, so a bit is not a boolean in T-SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 29, 2015 at 2:52 pm
drew.allen (10/29/2015)
Sean Lange (10/29/2015)
g.britton (10/29/2015)
For that matter I wish we had true Boolean variables. But I dream.We do.
create table MyTrueBoolean
(
TrueBool bit not null
)
If you have a bit column and no value is provided would have it be True or False??? That is why the bit datatype allows NULL. With no known value it can't logically be either.
No we don't. Bit expressions cannot be used in place of Boolean expressions. Try the following:
DECLARE @MyPseudoBoolean BIT = 1;
SELECT GETDATE()
WHERE @MyPseudoBoolean
And you will get an error "An expression of non-boolean type specified in a context where a condition is expected, near '@MyPseudoBoolean'." Here the non-boolean type is bit, so a bit is not a boolean in T-SQL.
Drew
I knew somebody would split this hair. While it is certainly valid I was demonstrating how you can make a the bit datatype in a table work almost like a boolean. As a variable it certainly won't work and a boolean in a programming sense it certainly doesn't work. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2015 at 3:04 pm
Sean Lange (10/29/2015)
drew.allen (10/29/2015)
Sean Lange (10/29/2015)
g.britton (10/29/2015)
For that matter I wish we had true Boolean variables. But I dream.We do.
create table MyTrueBoolean
(
TrueBool bit not null
)
If you have a bit column and no value is provided would have it be True or False??? That is why the bit datatype allows NULL. With no known value it can't logically be either.
No we don't. Bit expressions cannot be used in place of Boolean expressions. Try the following:
DECLARE @MyPseudoBoolean BIT = 1;
SELECT GETDATE()
WHERE @MyPseudoBoolean
And you will get an error "An expression of non-boolean type specified in a context where a condition is expected, near '@MyPseudoBoolean'." Here the non-boolean type is bit, so a bit is not a boolean in T-SQL.
Drew
I knew somebody would split this hair. While it is certainly valid I was demonstrating how you can make a the bit datatype in a table work almost like a boolean. As a variable it certainly won't work and a boolean in a programming sense it certainly doesn't work. 😉
Yes, but I assume that g.britton was already aware of that which is why he included the word "true" in his comment.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 29, 2015 at 3:11 pm
drew.allen (10/29/2015)
Sean Lange (10/29/2015)
drew.allen (10/29/2015)
Sean Lange (10/29/2015)
g.britton (10/29/2015)
For that matter I wish we had true Boolean variables. But I dream.We do.
create table MyTrueBoolean
(
TrueBool bit not null
)
If you have a bit column and no value is provided would have it be True or False??? That is why the bit datatype allows NULL. With no known value it can't logically be either.
No we don't. Bit expressions cannot be used in place of Boolean expressions. Try the following:
DECLARE @MyPseudoBoolean BIT = 1;
SELECT GETDATE()
WHERE @MyPseudoBoolean
And you will get an error "An expression of non-boolean type specified in a context where a condition is expected, near '@MyPseudoBoolean'." Here the non-boolean type is bit, so a bit is not a boolean in T-SQL.
Drew
I knew somebody would split this hair. While it is certainly valid I was demonstrating how you can make a the bit datatype in a table work almost like a boolean. As a variable it certainly won't work and a boolean in a programming sense it certainly doesn't work. 😉
Yes, but I assume that g.britton was already aware of that which is why he included the word "true" in his comment.
Drew
You more than likely correct and as always thanks for clarifying.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply