null date parameter for a stored procedure

Question

null date parameter for a stored procedure

Viewing 15 posts - 16 through 30 (of 41 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

sqlfriends - Tuesday, March 13, 2018 11:56 AM
Agree on that.
This is not a query, it is a stored procedure that has a date parameter that has a default value null

It is a query in the stored procedure.

sgmunson SSC Guru Points: 110640 More actions · Answer 2

ScottPletcher - Tuesday, March 13, 2018 11:49 AM
sgmunson - Tuesday, March 13, 2018 11:40 AM
ScottPletcher - Tuesday, March 13, 2018 11:30 AM
sqlfriends - Tuesday, March 13, 2018 11:05 AM
Thanks,
oh it returns not true, then 0 record. Thanks,
So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)
Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')
Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
"A comparison to NULL always returns false." Uh, no, the whole point of my post is that it does NOT return "false" (or "not true", as the OP put it). If it did return "false", then NOT (<NULL comparison>) would, by definition, return "true", but of course it does not.
Btw, I very clearly stated "a comparison to NULL" myself, as any function that's designed to deal with NULLs may be able to yield a true or false result. But a comparison to NULL does not and cannot, and I think that's an important concept for posters here to understand,even though it can be a painful step for them. Frankly NULLs are a royal pain to deal with, but better to have some mechanism for dealing with missing values other than old-time "magic" values, which were even worse.

I should have clarified that the net result of any comparison involving a NULL in a WHERE clause, will give you the equivalent of a false comparison, resulting in 0 rows affected. A simple way to demonstrate it:
SELECT 1 WHERE NOT (NULL = 1)

The clarification is that it's the entire comparison that nets out false, because the NULL value cascades upward, and only an IS NULL or ISNULL() can interfere with that cascade. Thanks for keeping me honest, Scott

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

sgmunson - Tuesday, March 13, 2018 12:00 PM
sqlfriends - Tuesday, March 13, 2018 11:56 AM
Agree on that.
This is not a query, it is a stored procedure that has a date parameter that has a default value null
Might want to change the default value to a hardcoded value that precedes any date in your systems.

Something like this?
Create or Replace PROCEDURE myprocedure (p_datestored IN Date DEFAULT TO_DATE('1900/01/01','yyyy/mm/dd'))

This is PLSQL.

Thanks,

sgmunson SSC Guru Points: 110640 More actions · Answer 4

sqlfriends - Tuesday, March 13, 2018 12:25 PM
sgmunson - Tuesday, March 13, 2018 12:00 PM
sqlfriends - Tuesday, March 13, 2018 11:56 AM
Agree on that.
This is not a query, it is a stored procedure that has a date parameter that has a default value null
Might want to change the default value to a hardcoded value that precedes any date in your systems.
Something like this?
Create or Replace PROCEDURE myprocedure (p_datestored IN Date DEFAULT TO_DATE('1900/01/01','yyyy/mm/dd'))
This is PLSQL.
Thanks,

As this is a SQL Server forum, and my exposure to Oracle has a lot of limits, I'd guess that's correct, but I just don't know if Oracle will allow that specification for a DEFAULT value in a stored procedure. You can always try and then go back to the previous code if it doesn't work. Be sure to save a copy of the original code first, of course.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ScottPletcher SSC Guru Points: 101118 More actions · Answer 5

sgmunson - Tuesday, March 13, 2018 12:12 PM
ScottPletcher - Tuesday, March 13, 2018 11:49 AM
sgmunson - Tuesday, March 13, 2018 11:40 AM
ScottPletcher - Tuesday, March 13, 2018 11:30 AM
sqlfriends - Tuesday, March 13, 2018 11:05 AM
Thanks,
oh it returns not true, then 0 record. Thanks,
So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)
Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')
Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
"A comparison to NULL always returns false." Uh, no, the whole point of my post is that it does NOT return "false" (or "not true", as the OP put it). If it did return "false", then NOT (<NULL comparison>) would, by definition, return "true", but of course it does not.
Btw, I very clearly stated "a comparison to NULL" myself, as any function that's designed to deal with NULLs may be able to yield a true or false result. But a comparison to NULL does not and cannot, and I think that's an important concept for posters here to understand,even though it can be a painful step for them. Frankly NULLs are a royal pain to deal with, but better to have some mechanism for dealing with missing values other than old-time "magic" values, which were even worse.
I should have clarified that the net result of any comparison involving a NULL in a WHERE clause, will give you the equivalent of a false comparison, resulting in 0 rows affected. A simple way to demonstrate it:
SELECT 1 WHERE NOT (NULL = 1)
The clarification is that it's the entire comparison that nets out false, because the NULL value cascades upward, and only an IS NULL or ISNULL() can interfere with that cascade. Thanks for keeping me honest, Scott

I'm still not comfortable with "false" because I believe it overstates the result, as I've noted.

WHERE conditions must be true to allow a row to be SELECTed. Not just "not false', but known true. CHECK conditions, otoh, must simply be not false (I suspect there's more accurate wording but that's the best I can do in a rush). The difference is most noticeable with, of course, NULLs.

For example, run the code below, and verify that the row with NULL will get INSERTed into the table despite the CHECK condition. Many people aren't aware of these quirks of SQL / nulls / relational language.

if object_id('tempdb.dbo.#t1') is not null drop table #t1; create table #t1 (c1 int null check(c1 >= 5)) insert into #t1 values(null) --WORKS OK! insert into #t1 values(10) insert into #t1 values(4) --CHECK condition rejects, as expected select 'no WHERE', * from #t1; select 'with WHERE', * from #t1 where c1 >= 5; drop table #t1;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sgmunson SSC Guru Points: 110640 More actions · Answer 6

ScottPletcher - Tuesday, March 13, 2018 2:03 PM
sgmunson - Tuesday, March 13, 2018 12:12 PM
ScottPletcher - Tuesday, March 13, 2018 11:49 AM
sgmunson - Tuesday, March 13, 2018 11:40 AM
ScottPletcher - Tuesday, March 13, 2018 11:30 AM
sqlfriends - Tuesday, March 13, 2018 11:05 AM
Thanks,
oh it returns not true, then 0 record. Thanks,
So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)
Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')
Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
"A comparison to NULL always returns false." Uh, no, the whole point of my post is that it does NOT return "false" (or "not true", as the OP put it). If it did return "false", then NOT (<NULL comparison>) would, by definition, return "true", but of course it does not.
Btw, I very clearly stated "a comparison to NULL" myself, as any function that's designed to deal with NULLs may be able to yield a true or false result. But a comparison to NULL does not and cannot, and I think that's an important concept for posters here to understand,even though it can be a painful step for them. Frankly NULLs are a royal pain to deal with, but better to have some mechanism for dealing with missing values other than old-time "magic" values, which were even worse.
I should have clarified that the net result of any comparison involving a NULL in a WHERE clause, will give you the equivalent of a false comparison, resulting in 0 rows affected. A simple way to demonstrate it:
SELECT 1 WHERE NOT (NULL = 1)
The clarification is that it's the entire comparison that nets out false, because the NULL value cascades upward, and only an IS NULL or ISNULL() can interfere with that cascade. Thanks for keeping me honest, Scott
I'm still not comfortable with "false" because I believe it overstates the result, as I've noted.
WHERE conditions must be true to allow a row to be SELECTed. Not just "not false', but known true. CHECK conditions, otoh, must simply be not false (I suspect there's more accurate wording but that's the best I can do in a rush). The difference is most noticeable with, of course, NULLs.
For example, run the code below, and verify that the row with NULL will get INSERTed into the table despite the CHECK condition. Many people aren't aware of these quirks of SQL / nulls / relational language.
if object_id('tempdb.dbo.#t1') is not null drop table #t1; create table #t1 (c1 int null check(c1 >= 5)) insert into #t1 values(null) --WORKS OK! insert into #t1 values(10) insert into #t1 values(4) --CHECK condition rejects, as expected select 'no WHERE', * from #t1; select 'with WHERE', * from #t1 where c1 >= 5; drop table #t1;

The only reason I conclude that it nets out to false is because a query with a comparison to a NULL value still runs, and produces an empty recordset. If you had to insist that the NULL propagates all the way to the query, then in theory, said query should not run at all, as there's no way to know what the result should be. The WHERE clause forces a conclusion to be drawn; net false in the case of a NULL comparison; but a conclusion none the less. I know that may seem like semantics, but as with so many things, the English language is not particularly good at being able to convey concepts with mere written words. Understanding the vagaries of NULL requires a bit more... as well demonstrated by your check constraint example.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ScottPletcher SSC Guru Points: 101118 More actions · Answer 7

sgmunson - Tuesday, March 13, 2018 2:32 PM
ScottPletcher - Tuesday, March 13, 2018 2:03 PM
sgmunson - Tuesday, March 13, 2018 12:12 PM
ScottPletcher - Tuesday, March 13, 2018 11:49 AM
sgmunson - Tuesday, March 13, 2018 11:40 AM
ScottPletcher - Tuesday, March 13, 2018 11:30 AM
sqlfriends - Tuesday, March 13, 2018 11:05 AM
Thanks,
oh it returns not true, then 0 record. Thanks,
So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)
Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')
Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
"A comparison to NULL always returns false." Uh, no, the whole point of my post is that it does NOT return "false" (or "not true", as the OP put it). If it did return "false", then NOT (<NULL comparison>) would, by definition, return "true", but of course it does not.
Btw, I very clearly stated "a comparison to NULL" myself, as any function that's designed to deal with NULLs may be able to yield a true or false result. But a comparison to NULL does not and cannot, and I think that's an important concept for posters here to understand,even though it can be a painful step for them. Frankly NULLs are a royal pain to deal with, but better to have some mechanism for dealing with missing values other than old-time "magic" values, which were even worse.
I should have clarified that the net result of any comparison involving a NULL in a WHERE clause, will give you the equivalent of a false comparison, resulting in 0 rows affected. A simple way to demonstrate it:
SELECT 1 WHERE NOT (NULL = 1)
The clarification is that it's the entire comparison that nets out false, because the NULL value cascades upward, and only an IS NULL or ISNULL() can interfere with that cascade. Thanks for keeping me honest, Scott
I'm still not comfortable with "false" because I believe it overstates the result, as I've noted.
WHERE conditions must be true to allow a row to be SELECTed. Not just "not false', but known true. CHECK conditions, otoh, must simply be not false (I suspect there's more accurate wording but that's the best I can do in a rush). The difference is most noticeable with, of course, NULLs.
For example, run the code below, and verify that the row with NULL will get INSERTed into the table despite the CHECK condition. Many people aren't aware of these quirks of SQL / nulls / relational language.
if object_id('tempdb.dbo.#t1') is not null drop table #t1; create table #t1 (c1 int null check(c1 >= 5)) insert into #t1 values(null) --WORKS OK! insert into #t1 values(10) insert into #t1 values(4) --CHECK condition rejects, as expected select 'no WHERE', * from #t1; select 'with WHERE', * from #t1 where c1 >= 5; drop table #t1;
The only reason I conclude that it nets out to false is because a query with a comparison to a NULL value still runs, and produces an empty recordset. If you had to insist that the NULL propagates all the way to the query, then in theory, said query should not run at all, as there's no way to know what the result should be. The WHERE clause forces a conclusion to be drawn; net false in the case of a NULL comparison; but a conclusion none the less. I know that may seem like semantics, but as with so many things, the English language is not particularly good at being able to convey concepts with mere written words. Understanding the vagaries of NULL requires a bit more... as well demonstrated by your check constraint example.

Again, not quite. As I noted, the rule is that WHERE conditions must be true. Since a NULL/unknown result is not proven true, the WHERE fails and the row is not selected, but that doesn't require a "false" result. We just have to keep that distinction in mind when we deal with WHERE and NULL and CHECK and NULL. Checks don't have to be true, they just can't be proven false.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 8

ScottPletcher - Tuesday, March 13, 2018 2:44 PM
sgmunson - Tuesday, March 13, 2018 2:32 PM
ScottPletcher - Tuesday, March 13, 2018 2:03 PM
sgmunson - Tuesday, March 13, 2018 12:12 PM
ScottPletcher - Tuesday, March 13, 2018 11:49 AM
sgmunson - Tuesday, March 13, 2018 11:40 AM
ScottPletcher - Tuesday, March 13, 2018 11:30 AM
sqlfriends - Tuesday, March 13, 2018 11:05 AM
Thanks,
oh it returns not true, then 0 record. Thanks,
So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)
Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')
Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
"A comparison to NULL always returns false." Uh, no, the whole point of my post is that it does NOT return "false" (or "not true", as the OP put it). If it did return "false", then NOT (<NULL comparison>) would, by definition, return "true", but of course it does not.
Btw, I very clearly stated "a comparison to NULL" myself, as any function that's designed to deal with NULLs may be able to yield a true or false result. But a comparison to NULL does not and cannot, and I think that's an important concept for posters here to understand,even though it can be a painful step for them. Frankly NULLs are a royal pain to deal with, but better to have some mechanism for dealing with missing values other than old-time "magic" values, which were even worse.
I should have clarified that the net result of any comparison involving a NULL in a WHERE clause, will give you the equivalent of a false comparison, resulting in 0 rows affected. A simple way to demonstrate it:
SELECT 1 WHERE NOT (NULL = 1)
The clarification is that it's the entire comparison that nets out false, because the NULL value cascades upward, and only an IS NULL or ISNULL() can interfere with that cascade. Thanks for keeping me honest, Scott
I'm still not comfortable with "false" because I believe it overstates the result, as I've noted.
WHERE conditions must be true to allow a row to be SELECTed. Not just "not false', but known true. CHECK conditions, otoh, must simply be not false (I suspect there's more accurate wording but that's the best I can do in a rush). The difference is most noticeable with, of course, NULLs.
For example, run the code below, and verify that the row with NULL will get INSERTed into the table despite the CHECK condition. Many people aren't aware of these quirks of SQL / nulls / relational language.
if object_id('tempdb.dbo.#t1') is not null drop table #t1; create table #t1 (c1 int null check(c1 >= 5)) insert into #t1 values(null) --WORKS OK! insert into #t1 values(10) insert into #t1 values(4) --CHECK condition rejects, as expected select 'no WHERE', * from #t1; select 'with WHERE', * from #t1 where c1 >= 5; drop table #t1;
The only reason I conclude that it nets out to false is because a query with a comparison to a NULL value still runs, and produces an empty recordset. If you had to insist that the NULL propagates all the way to the query, then in theory, said query should not run at all, as there's no way to know what the result should be. The WHERE clause forces a conclusion to be drawn; net false in the case of a NULL comparison; but a conclusion none the less. I know that may seem like semantics, but as with so many things, the English language is not particularly good at being able to convey concepts with mere written words. Understanding the vagaries of NULL requires a bit more... as well demonstrated by your check constraint example.
Again, not quite. As I noted, the rule is that WHERE conditions must be true. Since a NULL/unknown result is not proven true, the WHERE fails and the row is not selected, but that doesn't require a "false" result. We just have to keep that distinction in mind when we deal with WHERE and NULL and CHECK and NULL. Checks don't have to be true, they just can't be proven false.

This would be another way to see that NULL comparisons don't return anything.

SELECT CASE WHEN NOT ( 1 != NULL ) THEN 1 ELSE 0 END
SELECT CASE WHEN NOT ( 1 = NULL ) THEN 1 ELSE 0 END
SELECT CASE WHEN NOT ( 1 = 2 ) THEN 1 ELSE 0 END

sgmunson SSC Guru Points: 110640 More actions · Answer 9

ScottPletcher - Tuesday, March 13, 2018 2:44 PM
sgmunson - Tuesday, March 13, 2018 2:32 PM
ScottPletcher - Tuesday, March 13, 2018 2:03 PM
sgmunson - Tuesday, March 13, 2018 12:12 PM
ScottPletcher - Tuesday, March 13, 2018 11:49 AM
sgmunson - Tuesday, March 13, 2018 11:40 AM
ScottPletcher - Tuesday, March 13, 2018 11:30 AM
sqlfriends - Tuesday, March 13, 2018 11:05 AM
Thanks,
oh it returns not true, then 0 record. Thanks,
So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)
Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')
Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
"A comparison to NULL always returns false." Uh, no, the whole point of my post is that it does NOT return "false" (or "not true", as the OP put it). If it did return "false", then NOT (<NULL comparison>) would, by definition, return "true", but of course it does not.
Btw, I very clearly stated "a comparison to NULL" myself, as any function that's designed to deal with NULLs may be able to yield a true or false result. But a comparison to NULL does not and cannot, and I think that's an important concept for posters here to understand,even though it can be a painful step for them. Frankly NULLs are a royal pain to deal with, but better to have some mechanism for dealing with missing values other than old-time "magic" values, which were even worse.
I should have clarified that the net result of any comparison involving a NULL in a WHERE clause, will give you the equivalent of a false comparison, resulting in 0 rows affected. A simple way to demonstrate it:
SELECT 1 WHERE NOT (NULL = 1)
The clarification is that it's the entire comparison that nets out false, because the NULL value cascades upward, and only an IS NULL or ISNULL() can interfere with that cascade. Thanks for keeping me honest, Scott
I'm still not comfortable with "false" because I believe it overstates the result, as I've noted.
WHERE conditions must be true to allow a row to be SELECTed. Not just "not false', but known true. CHECK conditions, otoh, must simply be not false (I suspect there's more accurate wording but that's the best I can do in a rush). The difference is most noticeable with, of course, NULLs.
For example, run the code below, and verify that the row with NULL will get INSERTed into the table despite the CHECK condition. Many people aren't aware of these quirks of SQL / nulls / relational language.
if object_id('tempdb.dbo.#t1') is not null drop table #t1; create table #t1 (c1 int null check(c1 >= 5)) insert into #t1 values(null) --WORKS OK! insert into #t1 values(10) insert into #t1 values(4) --CHECK condition rejects, as expected select 'no WHERE', * from #t1; select 'with WHERE', * from #t1 where c1 >= 5; drop table #t1;
The only reason I conclude that it nets out to false is because a query with a comparison to a NULL value still runs, and produces an empty recordset. If you had to insist that the NULL propagates all the way to the query, then in theory, said query should not run at all, as there's no way to know what the result should be. The WHERE clause forces a conclusion to be drawn; net false in the case of a NULL comparison; but a conclusion none the less. I know that may seem like semantics, but as with so many things, the English language is not particularly good at being able to convey concepts with mere written words. Understanding the vagaries of NULL requires a bit more... as well demonstrated by your check constraint example.
Again, not quite. As I noted, the rule is that WHERE conditions must be true. Since a NULL/unknown result is not proven true, the WHERE fails and the row is not selected, but that doesn't require a "false" result. We just have to keep that distinction in mind when we deal with WHERE and NULL and CHECK and NULL. Checks don't have to be true, they just can't be proven false.

You're kinda proving my point. The WHERE clause is a binary decision tree. If a NULL result appears in a comparison, that comparison may be unknown, but as it can't be true, and a binary decision is required, that decision is effectively false. Just applying NOT to a NULL comparison doesn't change the NULL value at all, because the evaluation for a binary decision isn't being made at that level. It's made at the top level, after evaluating all the specified conditions. If a NULL comparison is part of an AND, then it's going to result in a false binary decision, but if it's part of an OR, it may not matter, but if all the other OR conditions are false, the NULL comparison will bubble up to a false evaluation.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ScottPletcher SSC Guru Points: 101118 More actions · Answer 10

sgmunson - Wednesday, March 14, 2018 11:41 AM
ScottPletcher - Tuesday, March 13, 2018 2:44 PM
sgmunson - Tuesday, March 13, 2018 2:32 PM
ScottPletcher - Tuesday, March 13, 2018 2:03 PM
sgmunson - Tuesday, March 13, 2018 12:12 PM
ScottPletcher - Tuesday, March 13, 2018 11:49 AM
sgmunson - Tuesday, March 13, 2018 11:40 AM
ScottPletcher - Tuesday, March 13, 2018 11:30 AM
sqlfriends - Tuesday, March 13, 2018 11:05 AM
Thanks,
oh it returns not true, then 0 record. Thanks,
So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)
Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')
Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
"A comparison to NULL always returns false." Uh, no, the whole point of my post is that it does NOT return "false" (or "not true", as the OP put it). If it did return "false", then NOT (<NULL comparison>) would, by definition, return "true", but of course it does not.
Btw, I very clearly stated "a comparison to NULL" myself, as any function that's designed to deal with NULLs may be able to yield a true or false result. But a comparison to NULL does not and cannot, and I think that's an important concept for posters here to understand,even though it can be a painful step for them. Frankly NULLs are a royal pain to deal with, but better to have some mechanism for dealing with missing values other than old-time "magic" values, which were even worse.
I should have clarified that the net result of any comparison involving a NULL in a WHERE clause, will give you the equivalent of a false comparison, resulting in 0 rows affected. A simple way to demonstrate it:
SELECT 1 WHERE NOT (NULL = 1)
The clarification is that it's the entire comparison that nets out false, because the NULL value cascades upward, and only an IS NULL or ISNULL() can interfere with that cascade. Thanks for keeping me honest, Scott
I'm still not comfortable with "false" because I believe it overstates the result, as I've noted.
WHERE conditions must be true to allow a row to be SELECTed. Not just "not false', but known true. CHECK conditions, otoh, must simply be not false (I suspect there's more accurate wording but that's the best I can do in a rush). The difference is most noticeable with, of course, NULLs.
For example, run the code below, and verify that the row with NULL will get INSERTed into the table despite the CHECK condition. Many people aren't aware of these quirks of SQL / nulls / relational language.
if object_id('tempdb.dbo.#t1') is not null drop table #t1; create table #t1 (c1 int null check(c1 >= 5)) insert into #t1 values(null) --WORKS OK! insert into #t1 values(10) insert into #t1 values(4) --CHECK condition rejects, as expected select 'no WHERE', * from #t1; select 'with WHERE', * from #t1 where c1 >= 5; drop table #t1;
The only reason I conclude that it nets out to false is because a query with a comparison to a NULL value still runs, and produces an empty recordset. If you had to insist that the NULL propagates all the way to the query, then in theory, said query should not run at all, as there's no way to know what the result should be. The WHERE clause forces a conclusion to be drawn; net false in the case of a NULL comparison; but a conclusion none the less. I know that may seem like semantics, but as with so many things, the English language is not particularly good at being able to convey concepts with mere written words. Understanding the vagaries of NULL requires a bit more... as well demonstrated by your check constraint example.
Again, not quite. As I noted, the rule is that WHERE conditions must be true. Since a NULL/unknown result is not proven true, the WHERE fails and the row is not selected, but that doesn't require a "false" result. We just have to keep that distinction in mind when we deal with WHERE and NULL and CHECK and NULL. Checks don't have to be true, they just can't be proven false.
You're kinda proving my point. The WHERE clause is a binary decision tree. If a NULL result appears in a comparison, that comparison may be unknown, but as it can't be true, and a binary decision is required, that decision is effectively false. Just applying NOT to a NULL comparison doesn't change the NULL value at all, because the evaluation for a binary decision isn't being made at that level. It's made at the top level, after evaluating all the specified conditions. If a NULL comparison is part of an AND, then it's going to result in a false binary decision, but if it's part of an OR, it may not matter, but if all the other OR conditions are false, the NULL comparison will bubble up to a false evaluation.

I think telling people to think of it as "false" will cause them future problems when dealing with NULLs. It's not, strictly speaking, a false result. WHERE may treat it as "false" for projecting a given row or not, if you want to look at it that way, but not really. If it were, a "false" CHECK condition would fail and prevent the row from being INSERTed, but it doesn't.

NULLs result in tri-value logic -- TRUE, FALSE or UNKNOWN -- not just T/F.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sgmunson SSC Guru Points: 110640 More actions · Answer 11

ScottPletcher - Wednesday, March 14, 2018 11:56 AM
sgmunson - Wednesday, March 14, 2018 11:41 AM
ScottPletcher - Tuesday, March 13, 2018 2:44 PM
sgmunson - Tuesday, March 13, 2018 2:32 PM
ScottPletcher - Tuesday, March 13, 2018 2:03 PM
sgmunson - Tuesday, March 13, 2018 12:12 PM
ScottPletcher - Tuesday, March 13, 2018 11:49 AM
sgmunson - Tuesday, March 13, 2018 11:40 AM
ScottPletcher - Tuesday, March 13, 2018 11:30 AM
sqlfriends - Tuesday, March 13, 2018 11:05 AM
Thanks,
oh it returns not true, then 0 record. Thanks,
So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)
Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')
Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
"A comparison to NULL always returns false." Uh, no, the whole point of my post is that it does NOT return "false" (or "not true", as the OP put it). If it did return "false", then NOT (<NULL comparison>) would, by definition, return "true", but of course it does not.
Btw, I very clearly stated "a comparison to NULL" myself, as any function that's designed to deal with NULLs may be able to yield a true or false result. But a comparison to NULL does not and cannot, and I think that's an important concept for posters here to understand,even though it can be a painful step for them. Frankly NULLs are a royal pain to deal with, but better to have some mechanism for dealing with missing values other than old-time "magic" values, which were even worse.
I should have clarified that the net result of any comparison involving a NULL in a WHERE clause, will give you the equivalent of a false comparison, resulting in 0 rows affected. A simple way to demonstrate it:
SELECT 1 WHERE NOT (NULL = 1)
The clarification is that it's the entire comparison that nets out false, because the NULL value cascades upward, and only an IS NULL or ISNULL() can interfere with that cascade. Thanks for keeping me honest, Scott
I'm still not comfortable with "false" because I believe it overstates the result, as I've noted.
WHERE conditions must be true to allow a row to be SELECTed. Not just "not false', but known true. CHECK conditions, otoh, must simply be not false (I suspect there's more accurate wording but that's the best I can do in a rush). The difference is most noticeable with, of course, NULLs.
For example, run the code below, and verify that the row with NULL will get INSERTed into the table despite the CHECK condition. Many people aren't aware of these quirks of SQL / nulls / relational language.
if object_id('tempdb.dbo.#t1') is not null drop table #t1; create table #t1 (c1 int null check(c1 >= 5)) insert into #t1 values(null) --WORKS OK! insert into #t1 values(10) insert into #t1 values(4) --CHECK condition rejects, as expected select 'no WHERE', * from #t1; select 'with WHERE', * from #t1 where c1 >= 5; drop table #t1;
The only reason I conclude that it nets out to false is because a query with a comparison to a NULL value still runs, and produces an empty recordset. If you had to insist that the NULL propagates all the way to the query, then in theory, said query should not run at all, as there's no way to know what the result should be. The WHERE clause forces a conclusion to be drawn; net false in the case of a NULL comparison; but a conclusion none the less. I know that may seem like semantics, but as with so many things, the English language is not particularly good at being able to convey concepts with mere written words. Understanding the vagaries of NULL requires a bit more... as well demonstrated by your check constraint example.
Again, not quite. As I noted, the rule is that WHERE conditions must be true. Since a NULL/unknown result is not proven true, the WHERE fails and the row is not selected, but that doesn't require a "false" result. We just have to keep that distinction in mind when we deal with WHERE and NULL and CHECK and NULL. Checks don't have to be true, they just can't be proven false.
You're kinda proving my point. The WHERE clause is a binary decision tree. If a NULL result appears in a comparison, that comparison may be unknown, but as it can't be true, and a binary decision is required, that decision is effectively false. Just applying NOT to a NULL comparison doesn't change the NULL value at all, because the evaluation for a binary decision isn't being made at that level. It's made at the top level, after evaluating all the specified conditions. If a NULL comparison is part of an AND, then it's going to result in a false binary decision, but if it's part of an OR, it may not matter, but if all the other OR conditions are false, the NULL comparison will bubble up to a false evaluation.
I think telling people to think of it as "false" will cause them future problems when dealing with NULLs. It's not, strictly speaking, a false result. WHERE may treat it as "false" for projecting a given row or not, if you want to look at it that way, but not really. If it were, a "false" CHECK condition would fail and prevent the row from being INSERTed, but it doesn't.
NULLs result in tri-value logic -- TRUE, FALSE or UNKNOWN -- not just T/F.

That;s fine as far as it goes, but the WHERE clause eventually has to make a binary decision: pass the row or not. It can't use UNKNOWN as true, so it effectively evaluates false. That is a separate evaluation entirely from the one that compares some value to NULL, which evaluates to NULL. If that NULL propagates all the way back to the top level because the WHERE clause is either solely composed of a comparison to NULL, or has such in combination only with ANDs, then it nets out to a false evaluation. They are still separate evaluations. One at the comparison level, and the binary decision of the WHERE clause as a whole at the row level.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 12

sgmunson - Wednesday, March 14, 2018 12:18 PM
ScottPletcher - Wednesday, March 14, 2018 11:56 AM
sgmunson - Wednesday, March 14, 2018 11:41 AM
ScottPletcher - Tuesday, March 13, 2018 2:44 PM
sgmunson - Tuesday, March 13, 2018 2:32 PM
ScottPletcher - Tuesday, March 13, 2018 2:03 PM
sgmunson - Tuesday, March 13, 2018 12:12 PM
ScottPletcher - Tuesday, March 13, 2018 11:49 AM
sgmunson - Tuesday, March 13, 2018 11:40 AM
ScottPletcher - Tuesday, March 13, 2018 11:30 AM
sqlfriends - Tuesday, March 13, 2018 11:05 AM
Thanks,
oh it returns not true, then 0 record. Thanks,
So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)
Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')
Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
"A comparison to NULL always returns false." Uh, no, the whole point of my post is that it does NOT return "false" (or "not true", as the OP put it). If it did return "false", then NOT (<NULL comparison>) would, by definition, return "true", but of course it does not.
Btw, I very clearly stated "a comparison to NULL" myself, as any function that's designed to deal with NULLs may be able to yield a true or false result. But a comparison to NULL does not and cannot, and I think that's an important concept for posters here to understand,even though it can be a painful step for them. Frankly NULLs are a royal pain to deal with, but better to have some mechanism for dealing with missing values other than old-time "magic" values, which were even worse.
I should have clarified that the net result of any comparison involving a NULL in a WHERE clause, will give you the equivalent of a false comparison, resulting in 0 rows affected. A simple way to demonstrate it:
SELECT 1 WHERE NOT (NULL = 1)
The clarification is that it's the entire comparison that nets out false, because the NULL value cascades upward, and only an IS NULL or ISNULL() can interfere with that cascade. Thanks for keeping me honest, Scott
I'm still not comfortable with "false" because I believe it overstates the result, as I've noted.
WHERE conditions must be true to allow a row to be SELECTed. Not just "not false', but known true. CHECK conditions, otoh, must simply be not false (I suspect there's more accurate wording but that's the best I can do in a rush). The difference is most noticeable with, of course, NULLs.
For example, run the code below, and verify that the row with NULL will get INSERTed into the table despite the CHECK condition. Many people aren't aware of these quirks of SQL / nulls / relational language.
if object_id('tempdb.dbo.#t1') is not null drop table #t1; create table #t1 (c1 int null check(c1 >= 5)) insert into #t1 values(null) --WORKS OK! insert into #t1 values(10) insert into #t1 values(4) --CHECK condition rejects, as expected select 'no WHERE', * from #t1; select 'with WHERE', * from #t1 where c1 >= 5; drop table #t1;
The only reason I conclude that it nets out to false is because a query with a comparison to a NULL value still runs, and produces an empty recordset. If you had to insist that the NULL propagates all the way to the query, then in theory, said query should not run at all, as there's no way to know what the result should be. The WHERE clause forces a conclusion to be drawn; net false in the case of a NULL comparison; but a conclusion none the less. I know that may seem like semantics, but as with so many things, the English language is not particularly good at being able to convey concepts with mere written words. Understanding the vagaries of NULL requires a bit more... as well demonstrated by your check constraint example.
Again, not quite. As I noted, the rule is that WHERE conditions must be true. Since a NULL/unknown result is not proven true, the WHERE fails and the row is not selected, but that doesn't require a "false" result. We just have to keep that distinction in mind when we deal with WHERE and NULL and CHECK and NULL. Checks don't have to be true, they just can't be proven false.
You're kinda proving my point. The WHERE clause is a binary decision tree. If a NULL result appears in a comparison, that comparison may be unknown, but as it can't be true, and a binary decision is required, that decision is effectively false. Just applying NOT to a NULL comparison doesn't change the NULL value at all, because the evaluation for a binary decision isn't being made at that level. It's made at the top level, after evaluating all the specified conditions. If a NULL comparison is part of an AND, then it's going to result in a false binary decision, but if it's part of an OR, it may not matter, but if all the other OR conditions are false, the NULL comparison will bubble up to a false evaluation.
I think telling people to think of it as "false" will cause them future problems when dealing with NULLs. It's not, strictly speaking, a false result. WHERE may treat it as "false" for projecting a given row or not, if you want to look at it that way, but not really. If it were, a "false" CHECK condition would fail and prevent the row from being INSERTed, but it doesn't.
NULLs result in tri-value logic -- TRUE, FALSE or UNKNOWN -- not just T/F.
That;s fine as far as it goes, but the WHERE clause eventually has to make a binary decision: pass the row or not. It can't use UNKNOWN as true, so it effectively evaluates false. That is a separate evaluation entirely from the one that compares some value to NULL, which evaluates to NULL. If that NULL propagates all the way back to the top level because the WHERE clause is either solely composed of a comparison to NULL, or has such in combination only with ANDs, then it nets out to a false evaluation. They are still separate evaluations. One at the comparison level, and the binary decision of the WHERE clause as a whole at the row level.

Yes specifically in the case of a where clause comparing to NULL happens to function the same as false, sometimes if it's at the end of the evaluation chain. However statements like NOT (1 = NULL) do not return true. I would just leave it at saying NULL with ANSI_NULLS on have some very specific rules that are in some cases very counter intuitive and not super consistent depending on use so if you are working with NULL columns know them.

MMartin1 One Orange Chip Points: 27799 More actions · Answer 13

select case when NOT( 1 = null ) then 1 else 0 end select case when ( 1 = null ) then 1 else 0 end

Both return the same value, 0. You cannot compare a value to a null. It is an illegal operation. Sql server cannot return a response. Not because it is a known false, but because it is a unknown ... == > Neither are TRUE.
I think we may be overly complicating this. To me something being FALSE means it has been determined, not the same as NULL (undetermined).

----------------------------------------------------

sgmunson SSC Guru Points: 110640 More actions · Answer 14

MMartin1 - Wednesday, March 14, 2018 10:35 PM
select case when NOT( 1 = null ) then 1 else 0 end select case when ( 1 = null ) then 1 else 0 end
Both return the same value, 0. You cannot compare a value to a null. It is an illegal operation. Sql server cannot return a response. Not because it is a known false, but because it is a unknown ... == > Neither are TRUE.
I think we may be overly complicating this. To me something being FALSE means it has been determined, not the same as NULL (undetermined).

And you are looking solely at a CASE statement evaluation, which does allow NULL to propagate all the way up, but as it has an ELSE clause, finally resolves the NULL. It's similar, but different to the case of the WHERE clause, which requires a binary decision at the end of all the evaluations. Your CASE statement example simply reproduces the binary decision by implementing an ELSE.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

dmbaker SSCertifiable Points: 5139 More actions · Answer 15

MMartin1 - Wednesday, March 14, 2018 10:35 PM
You cannot compare a value to a null. It is an illegal operation.

Of course you can compare a value to NULL. It's not illegal at all. Dumb, maybe, and the result might be unexpected if you don't know the rules, but it's certainly not illegal. And you could always set ANSI_NULLS to make it work -- though I think that generally is kinda dumb too (especially since Microsoft is warning that they are deprecating it). Better to learn/know the rules and abide by them, though, I think.