Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Logical statement table confusion

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 Null
True True False Null
False False False False
Null Null False Null


I'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 anyTable
where 'c' in ('a','b',null)

or

Select *
From anyTable
where 'c' not in ('a','b',null)


In the first, it is not true that 'c' is in a,b,null
In the second,is is not false that 'c' is in a,b,null
so neither returns a result

Jim


Everyday I learn something that somebody else already knew
Go to Top of Page

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.
Go to Top of Page

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)?


Jim


Everyday I learn something that somebody else already knew
Go to Top of Page

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.
Go to Top of Page

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.

Jim

Disclaimer: 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
Go to Top of Page

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.
Go to Top of Page

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_logic

Hope that helps. Once you get it, it'll be second nature.
Go to Top of Page

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.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-01-02 : 10:06:23
Just starting out? Try this link: http://www.w3schools.com/sql/default.asp








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

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.
Go to Top of Page

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 -- True
1 = 2 -- False
1 <> 1 -- False
1 <> 2 -- True
'' = 1 -- False

Value vs. Null (always false)
1 = NULL -- False
1 <> NULL -- False
'' <> NULL -- False as empty does not, not equal NULL
'' = NULL -- False as empty does not equal NULL

NULL vs. NULL (always False)
NULL = NULL -- False
NULL <> NULL -- False

In 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 = NULL
NULL - 1 = NULL
1 * NULL = NULL
1 / NULL = NULL
NULL / 1 = NULL
NULL + NULL = NULL

A 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 -- False
1 IS NOT NULL -- True
NULL IS NULL -- True
NULL IS NOT NULL -- False

In 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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -