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

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!

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.

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.

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.

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 -- False1 IS NOT NULL -- TrueNULL IS NULL -- TrueNULL 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.

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.

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.