When is a NULL Not a NULL ?

[Altemir]

I have a Table that I am inner joining with a View. However, the inner
join is not returning records for which the joined columns both contain
a NULL. The Table column is typed as nvarchar(50). The column in the
View is populated with NULL as a literal (e.g., SELECT NULL As ColumnA,
ColumnB, ColumnC FROM TableB).

Anyone care to guess as to why two NULL's don't form a match within
this kind of inner join?

 

[Robert Morley]

That's the behaviour of NULL, by definition. NULL is considered to be
nothing at all...it's like if someone held out their empty hands and asked
you if the objects in them were the same. Well, they're not holding
anything, so the question is invalid.

In order to have them match, probably the best bet is to COALESCE the values
of each in the join. So instead of

....
TableA INNER JOIN TableB ON TableA.ID = TableB.ID
....

it would become

....
TableA INNER JOIN TableB ON COALESCE(TableA.ID, '') = COALESCE(TableB.ID,
'')
....

Note that those are two single quotes, not a double quote. You can
conceivably replace what's in the quotes with some other string, if you want
to for some reason, but since it'll only be used to make the join, there's
little reason to do so.



Rob

 

[Sylvain Lafontaine]

The use of a function on the join operator will preclude any utilisation of
index seek, so it might be better to specifically test for the null
possibility:

TableA INNER JOIN TableB ON TableA.ID = TableB.ID or (TableA.Id is null and
TableB.ID is null)

However, even this form will perform a lot less then joining two tables
without consideration for any null value; so if possible, you should
redesign your schema if you don't want the performace to take a dive.

 

[Robert Morley]

Thanks Sylvain,

I was aware of the performance hit, but wasn't thinking of the rather
obvious solution of just checking for two separate conditions.



Rob

 

[Craig Alexander Morrison]

Nulls do not match for a very good reason.

Null represents UNKNOWN it is not nothing either because once you know a
field is supposed to be empty then you KNOW it is equal to NOTHING.

Consider a Blood Bank and ER Patients, The Patient's Blood type is NULL
until it is tested, (it is not going to be nothing it will be something).
Donations to the Blood Bank have to be typed until they are the Blood Type
is NULL.

If you matched on Nulls you would give a Patient whose blood had not been
tested a transfusion from a bag that had not been typed.

NULLS represent UNKNOWN and are different from KNOWN TO BE NOTHING.

 

[Craig Alexander Morrison]

BTW If you have fields that are KNOWN TO BE NOTHING you could consider using
a value to represent NOTHING as opposed to leaving it with a NULL.

 

[Craig Alexander Morrison]

NEVER join on NULL.

 

[David Portas]

NULLS represent UNKNOWN

Not in SQL they don't! If NULL meant simply "unknown" then X = X would
be true where X was NULL. Logically and mathematically if X is unknown
then one thing we DO know for sure is that X must always equal itself.

SQL's NULL is NOT a good model for unknowns. In fact it isn't a very
good model for anything at all. Unfortunately it's difficult to avoid
NULLs in SQL but the OP should probably try harder to do just that.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Craig Alexander Morrison]

Unknown does not equal Unknown.

Null is not a value, that is a common mistake amongst SQL implementations.

NULL IS NOT A VALUE

 

[David Portas]

Unknown does not equal Unknown.

Not so in math, logic and everyday experience (X = X). My point was
simply that SQL's NULL is not a good model for the reality and logic of
something that is unknown. When you said that NULLs "represent" unknown
you are speaking only of a convention among SQL users. It's a very
unfortunate convention because it leads to so many mistakes of the kind
mentioned.

Null is not a value

Exactly so. Which is why it's inaccurate (or at least potentially
misleading) to say that "NULL represents unknown".

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Craig Alexander Morrison]

If you say Unknown = X then only that Unknown can = X not the Unknown in the
next record (it could equal Y or Z you just do not know)or the Unknown in
another field in that record or the Unknown in another field/record in the
entire database.

Sure the Unknown in a particular field/record is the same as the Unknown in
the exact same field/record in the real world at one singular point in time.

I am not sure that the Unknown Car Colour (Text, 12) would ever equal the
Unknown Registration Number (Text, 12)

Best to model and use Null as Unknown.

As to elimination of Nulls, this should be encouraged although optional FKs
remain a major source of the blighters.

Try to avoid SQL if at all possible (vbg) its handling of nulls is an
abomination.

Perhaps you are hung up on the difference between Missing Information and
the term Unknown.

Let Unknown = Missing Information.

 

[Craig Alexander Morrison]

When you said that NULLs "represent" unknown
No I am not Unknown or Missing information is a topic of debate in R and
that is where I am coming from.

To quote from me 12 years ago:

"All in all Nulls are a pain and SQL and the products only make it worse. I
would always *try* to avoid them, Nulls that is (g), with careful database
design. The way they are implemented from product to product and from
version to version could easily vary. This, markers idea, was a mistake by
Codd, IMHO, and the vendors and ANSI jumped on it and we seem to be stuck
with it for now.

The work of Date (Default Values) with Pascal (Triggered Procedures),
McGovern (Catalog and DB Design) is the latest thinking on the replacement
for Nulls so that missing information can be better represented."

 

[David Portas]

Sure the Unknown in a particular field/record is the same as the Unknown in
the exact same field/record in the real world at one singular point in time.

QED. Thus:

SELECT *
FROM tbl
WHERE tbl.x = tbl.x;

Therefore NULL does not represent Unknown.

Try to avoid SQL if at all possible (vbg) its handling of nulls is an
abomination.

Hear, hear!

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Craig Alexander Morrison]

QED

QED. Thus:

SELECT *
FROM tbl
WHERE tbl.x = tbl.x;

Therefore NULL does not represent Unknown.

Therefore SQL is wrong! (wg)

Agreed? (vbg)

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

news:[email protected]...

 

[Vadim Rapp]

CAM> Sure the Unknown in a particular field/record is the same as the
CAM> Unknown in the exact same field/record in the real world at one
CAM> singular point in time.

I think even considering if NULL is same as anything else, including another
NULL, is inaccurate. NULL represents not yet filled value, a value that does
not yet exist, empty space in the row. You don't have anything to compare.
It's not even a placeholder, it the absense of anything to consider.

Vadim Rapp

 

[Craig Alexander Morrison]

That's exactly what I said.

The quote states that only the same field in the same record at one moment
in time could ever be considered possibly equal.

Unfortunately SQL is wrong.

 

[Altemir]

Thank you all for the great replies. I think I will try to replace the
NULLS with something like "NA".

 

[Sylvain Lafontaine]

« "All in all Nulls are a pain and SQL and the products only make it worse.
I would always *try* to avoid them, Nulls that is (g), with careful database
design. The way they are implemented from product to product and from
version to version could easily vary. This, markers idea, was a mistake by
Codd, IMHO, and the vendors and ANSI jumped on it and we seem to be stuck
with it for now." »

This is a big statement and we often read something similar to this, written
in one way or another. However, there is absolutely no example, proof or
demonstration behind it to back it or to substantiate it. When you want to
write a statement about something, you must come with a least a little more
in order to support it.

I could give you a lot of examples where the use of NULL leads to a clearer
code, with less fuss and easier to read. However, I won't because this is a
case where personal experience is more important than dogmatic statements.
NULL is like any other tools, you must learn how to use it properly and if
you want don't know about it or don't want to use it, that's your problem,
not the other's.

In my case, I like to have more tools in my toolbox, not less: when the only
tool you have in your toolbox is an hammer, everything else looks like a
nail and inversely, when all you have to do is to hit a nail, every tool
begins to look like a hammer.

 

[Robert Morley]

I think what it comes down to, guys, is simply a matter of language. No
matter whether you call it "nothing", "unknown", or "NULL", we all
understand it to be the same thing...info that just ain't there. We're just
expressing it in different terms.



Rob

 

[David Portas]

« "All in all Nulls are a pain and SQL and the products only make it worse.
I would always *try* to avoid them, Nulls that is (g), with careful database
design. The way they are implemented from product to product and from
version to version could easily vary. This, markers idea, was a mistake by
Codd, IMHO, and the vendors and ANSI jumped on it and we seem to be stuck
with it for now." »

This is a big statement and we often read something similar to this, written
in one way or another. However, there is absolutely no example, proof or
demonstration behind it to back it or to substantiate it. When you wantto
write a statement about something, you must come with a least a little more
in order to support it.

It has been demonstrated ad nauseum in database literature. Date,
Darwen and Pascal for example. The proof is the principles violated and
the logic that cannot be reconciled with nulls, not just isolated
examples.

I could give you a lot of examples where the use of NULL leads to a clearer
code, with less fuss and easier to read. However, I won't because this is a
case where personal experience is more important than dogmatic statements.
NULL is like any other tools, you must learn how to use it properly and if
you want don't know about it or don't want to use it, that's your problem,
not the other's.

In my case, I like to have more tools in my toolbox, not less: when the only
tool you have in your toolbox is an hammer, everything else looks like a
nail and inversely, when all you have to do is to hit a nail, every tool
begins to look like a hammer.

The toolbox analogy implies that you have a choice. Many SQL
implementations make it unreasonably hard to avoid nulls, but that's a
deficiency of SQL, not an advantage of nulls. Not everyone gets to
choose what data model and DBMS to work with.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--