Learn about SQL three-valued logic before it bites you
1–10 of 18 posts
Re: Learn about SQL three-valued logic before it bites you
#2The definition and use of markers for missing values belongs in the application, not the database. My 0,02€.
Re: Learn about SQL three-valued logic before it bites you
#3The definition and use of markers for missing values belongs in the application, not the database. My 0,02€.
Agree. Either that or then it's clear that NULL and empty have a different meaning, which in turn means the query should respect that.
Re: Learn about SQL three-valued logic before it bites you
#4How can it be unexpected behaviour when it is the behaviour you want!
You must not qualify a boolean question for a sgbd like Schroedinger's cat.
A boolean condition in Sql has 3 values : - true - false - unknown ( aka NULL)
For every "unknown" value, the sgbd will NOT decide for you if it's true or false, you have to decide, because it trust you to know what you're doing.
Re: Learn about SQL three-valued logic before it bites you
#5How can it be unexpected behaviour when it is the behaviour you want! You must not qualify a boolean question for a sgbd like Schroedinger's cat. A boolean condition in Sql has 3 values : - true - false - unknown ( aka NULL) For every "unknown" value, the sgbd will NOT decide for you if it's true or false, you have to decide, because it trust you to know what you're doing.
> For every "unknown" value, the sgbd will NOT decide for you if it's true or false
But it did decide. It could either return the row, or not return the row - there's no middle ground. By not returning the row, it implied that null is not not 'crazy'.
If it actually wanted not to decide, it could return an error.
Re: Learn about SQL three-valued logic before it bites you
#6When you filter out a value using !=, it's easy to forget that NULL won't come along for the ride since it's the database's way of saying 'value unknown'. To ensure NULL values don't slip through the net, you've got to explicitly fish for them with IS NULL or broaden your WHERE clause to catch everything with OR Val IS NULL. It’s SQL’s subtle reminder that NULL requires special attention.
Re: Learn about SQL three-valued logic before it bites you
#7The definition and use of markers for missing values belongs in the application, not the database. My 0,02€.
While I’m sure NULLs nary be found in the ivory tower, you should know they are pervasive in the rest of the kingdom amongst us common folk.
Re: Learn about SQL three-valued logic before it bites you
#8How can it be unexpected behaviour when it is the behaviour you want! You must not qualify a boolean question for a sgbd like Schroedinger's cat. A boolean condition in Sql has 3 values : - true - false - unknown ( aka NULL) For every "unknown" value, the sgbd will NOT decide for you if it's true or false, you have to decide, because it trust you to know what you're doing.
> For every "unknown" value, the sgbd will NOT decide for you if it's true or false But it did decide. It could either return the row, or not return the row - there's no middle ground. By not returning the row, it implied that null is not not 'crazy'. If it actually wanted not to decide, it could return an error.
Rather than make you deal with those pesky errors, we could suppress the errors and only process the records we know how to decide.
Win-win?
Re: Learn about SQL three-valued logic before it bites you
#9I don't find this behaviour crazy or unexpected at all.
NULL doesn't refer to any specific 'missing value' state, instead it represents a larger group of possible values of which we don't know the actual value.
So in a pile of values 'foo' and 'bar', NULL could mean either of them, so there is no way of knowing if a NULL row is foo or if it is bar. We can't even know if two NULLS are equal.
Re: Learn about SQL three-valued logic before it bites you
#10The definition and use of markers for missing values belongs in the application, not the database. My 0,02€.
While I’m sure NULLs nary be found in the ivory tower, you should know they are pervasive in the rest of the kingdom amongst us common folk.
The comment you're replying to acknowledges this and suggests we plan accordingly.