Author |
Topic |
chutestrate
Starting Member
17 Posts |
Posted - 2012-12-31 : 20:38:48
|
How can the false AND null statement return a false value? I don't see any difference between this statement and the true AND null statement returning an unknown result.AND True False NullTrue True False NullFalse False False FalseNull Null False NullI'm trying to teach myself sql and this concept is confusing. |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-01 : 13:04:25
|
It doesn't really return "False" as much as it returns "Not True".Select *From anyTablewhere 'c' in ('a','b',null)orSelect *From anyTablewhere 'c' not in ('a','b',null)In the first, it is not true that 'c' is in a,b,nullIn the second,is is not false that 'c' is in a,b,null so neither returns a resultJimEveryday I learn something that somebody else already knew |
|
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-01-01 : 17:03:50
|
Jim I don' t follow your explanation at all. Any chance you could explain it without the a's and b's? I'm still very new to this so the jargon is very greek to me. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-01 : 17:31:21
|
That's going to be tough to do. I really like Itzik Ben-Gan's t-SQL Fundamentals, he gets in to this better than I can. But null always is "unknown". Does c = null? Unknown. Does c not= null? Unknown. But SQL Server has to figure out "yes" or "no" based on the parameters it was given. Try my query against any of your tables and ask yourself, "Why is this the result?". Is it true without a doubt that "C" is in (a,b, somevalue that no one knows, that value could be "c", who knows?). Are you sure that "c" is not in (a,b,somevalue that no one knows)? JimEveryday I learn something that somebody else already knew |
|
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-01-01 : 19:47:48
|
I really appreciate the attempt, but this means nothing to me at my low level of understanding. I know you are trying to help. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-01 : 20:20:21
|
I know that nulls can be hard to comprehend, that's why a lot of database architects just won't allow them in their tables, ever -- they're indeterminate. The best thing to do is just really play around with them. Nulls are not true or false, they are always unknown. Try this. I'm thinking of a number between 1 and null, I'll give you a million dollars if you can ask me a set of questions that determines what number I'm thinking of, if I am even thinking of a number at all, I may not have determined of it yet.JimDisclaimer: I do not have 1 million dollars, if you figure this out, you get no money from me!Everyday I learn something that somebody else already knew |
|
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-01-01 : 22:29:19
|
Maybe I'm just too hung up on this concept. But is there any reason why null AND false would return a different result than true AND null? I understand your examples as written, but they aren't clearing up my confusion of the table results. |
|
|
erikhaselhofer
Starting Member
30 Posts |
Posted - 2013-01-02 : 03:11:43
|
Here are some samples that may help. My apologies for using another database, it was easier to copy. I assure you that they both work exactly the same way.I figured that rather than explaining it, that a bunch of examples might be better. So here goes:mysql> select * from junk;+------+| col1 |+------+| SQL |+------+1 row in set (0.00 sec)mysql> select * from junk where 1 = 1;+------+| col1 |+------+| SQL |+------+1 row in set (0.00 sec)mysql> select * from junk where 1 = 2;Empty set (0.00 sec)mysql> select * from junk where 1 = null;Empty set (0.00 sec)mysql> select * from junk where 1 <> null;Empty set (0.00 sec)mysql> select * from junk where null = null;Empty set (0.00 sec)mysql> select * from junk where null <> null;Empty set (0.00 sec)mysql> select * from junk where null is null;+------+| col1 |+------+| SQL |+------+1 row in set (0.00 sec)mysql> select * from junk where 1 is not null;+------+| col1 |+------+| SQL |+------+1 row in set (0.00 sec)A NULL is an example of Three-valued logic (true, false and unknown) and in this case NULL designates unknown. So in a way you can test a field for the value you are looking for (TRUE), the value you aren't looking for (FALSE) and no value at all (NULL). AND, you can also test a field to see if it's known (NOT NULL) or not known (NULL).And here I'll stop. I tried a couple of times to go further but it kept getting weird so I'll let Wikipedia do it instead.http://en.wikipedia.org/wiki/Ternary_logicHope that helps. Once you get it, it'll be second nature. |
|
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-01-02 : 07:52:48
|
Eric I appreciate the attempt, but I have no idea what I'm looking at...yet. I'm just starting out, and this is concept is where the course has left me hanging. Any chance you could explain? I will try to use the examples, but beyond my skill at this early stage. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-01-02 : 13:10:57
|
Thank you for the link. I've been using that site to supplement my reading material when I can. I didn't find the exercises for this particular question very helpful. It is a good resource though. |
|
|
erikhaselhofer
Starting Member
30 Posts |
Posted - 2013-01-02 : 13:40:44
|
I guess I was hoping the SQL would explain itself by when, and when it didn't, return a result. Let me try again, in my best attempt at keeping it simple, with a lot of words.A field in SQL can consist of two things, a value, or an unknown. A value is something like a letter, number, combination of those, an image, even a space, and even, just to make it confusing, an empty field. An unknown, designated with a NULL, means that the field has no value that we can know. NULL is a marker for that kind of field. The reason an empty field is not a NULL is because we know that the field is empty. When comparing values and the NULL designation there are three possible combinations: a value vs a value, a value vs a NULL and a NULL vs a NULL. Lets look at each of these.Value vs. Value (Can return true or false)1 = 1 -- True1 = 2 -- False1 <> 1 -- False1 <> 2 -- True'' = 1 -- FalseValue vs. Null (always false)1 = NULL -- False1 <> NULL -- False'' <> NULL -- False as empty does not, not equal NULL'' = NULL -- False as empty does not equal NULLNULL vs. NULL (always False)NULL = NULL -- FalseNULL <> NULL -- FalseIn other words, comparing an unknown, to anything else whether it be known, or unknown, will always have a result of unknown. If we try to add a NULL, to either a value or another unknown, the result will also always be unknown. For example:1 + NULL = NULLNULL - 1 = NULL1 * NULL = NULL1 / NULL = NULLNULL / 1 = NULLNULL + NULL = NULLA NULL, because it's unknowable, is always unknowable even if you add a known value to it. So how do you deal with a NULL in data? By Testing if a field "IS NULL" (unknown), or "IS NOT NULL" (known/value).1 IS NULL -- False1 IS NOT NULL -- TrueNULL IS NULL -- TrueNULL IS NOT NULL -- FalseIn a way, you have two things going on with the data: known values, which work one way, and unknowns, which work another way and are tested for in a different way.OK, that is as much as I've got for today. Hopefully I didn't confuse you even more. |
|
|
chutestrate
Starting Member
17 Posts |
Posted - 2013-01-02 : 19:19:27
|
Thank you. Some it is making sense so I guess I'm learning. The terminology is the most difficult part right now. You can't really talk about sql without it, and I get completely overwhelmed with all of the different meanings. Thank you for the information. I need to digest it, and work with the symbols you posted. I'm a bit lost with your equations, but I should be ok with looking up what they mean. I've read about them, but have not actively used them. What the heck does 1 / Null=Null mean, lol. This is all good. |
|
|
erikhaselhofer
Starting Member
30 Posts |
Posted - 2013-01-03 : 13:36:23
|
quote: Originally posted by chutestrate Thank you. Some it is making sense so I guess I'm learning. The terminology is the most difficult part right now. You can't really talk about sql without it, and I get completely overwhelmed with all of the different meanings.
SQL is a fascinating language because there is always an answer, assuming of course, that the data allows for it. When I first started programming it was on flat files (I'm not as old as that sounds) and you would read each line, process it, then go to the next line and so on. When I got what you could do with sets and SQL I was amazed by it. It's such a powerful language and basic SQL really doesn't have a lot of syntax.quote: What the heck does 1 / Null=Null mean, lol. This is all good.
1 divided by NULL |
|
|
|