null = null does not match !! - SQL Server Forums

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

null = null does not match !!

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-22 : 15:24:17

Hello,

I do not understand the following result. When I execute the following query I do not get any result. But there is in each table a row that fist the the WHERE criteria :

SELECT *
FROM dbo.Article_Element , dbo.Item
WHERE dbo.Item.Artcoar=dbo.Article_Element.ARTCOAR And
dbo.Item.Artpere = dbo.Article_Element.ARTPERE And
dbo.Item.Artdesi=dbo.Article_Element.ARTDESI

The thing is that dbo.Item.Artpere and dbo.Article_Element.ARTPERE are null. If I take this toff (dbo.Item.Artpere = dbo.Article_Element.ARTPERE And) then I get the result.

How could it be that null = null is not matching?

Thanks a lot for any feedback!

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-22 : 15:30:00

Because that's how NULLs work.

If you want to display the NULLs, then use OUTER JOINs.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-22 : 16:21:28

Hello Tara,

thanks for your reply. I did what you told :

UPDATE I
SET I.Element_ID = Article_Element.Element_ID
FROM Item as I FULL OUTER JOIN
Article_Element ON Article_Element.ARTCOAR = I.Artcoar AND Article_Element.ARTPERE = I.Artpere AND Article_Element.ARTDESI = I.Artdesi

But still the same, the row from Item and the row from Article_Element are not joined if the field ARTPERE in both tables is equal to null.

:-(

any idea?

thanks for further help.

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

hey001us
Posting Yak Master

185 Posts

Posted - 2007-05-22 : 23:52:29

I did not test this SQL since don’t have any sample data.
Please test first
UPDATE I
SET I.Element_ID = Article_Element.Element_ID
FROM Item as I FULL
OUTER JOIN Article_Element ON Article_Element.ARTCOAR = I.Artcoar
AND (Article_Element.ARTPERE = I.Artpere OR (I.Artpere IS NULL AND Article_Element.ARTCOAR IS NULL))
AND Article_Element.ARTDESI = I.Artdesi

hey

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-22 : 23:59:31

try this..

SELECT *
FROM dbo.Article_Element , dbo.Item
WHERE dbo.Item.Artcoar=dbo.Article_Element.ARTCOAR And
coalesce(dbo.Item.Artpere,'') = coalesce(dbo.Article_Element.ARTPERE,'') And
dbo.Item.Artdesi=dbo.Article_Element.ARTDESI

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-23 : 08:34:15

hello pbguy,

your solution works perfectly !
Could you explain why null is not = null ??
Why coalesce is needed ?

Thanks a lot for your help.

Regards,
Fabianus

PS Hello hey, thanks to you, too, but your solution did not work in my case.

my favorit hoster is ASPnix : www.aspnix.com !

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-23 : 08:39:59

I took this from SQL server help file....u will understand why two nulls are not equal..

Null Values
A value of NULL indicates the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

Null values usually indicate data that is unknown, not applicable, or to be added at a later time. For example, a customer's middle initial may not be known at the time the customer places an order.

Here is some information about nulls:

To test for null values in a query use IS NULL or IS NOT NULL in the WHERE clause.

When query results are viewed in SQL Query Analyzer, null values are shown as (null) in the result set.

Null values can be inserted into a column by explicitly stating NULL in an INSERT or UPDATE statement, or by leaving a column out of an INSERT statement, or when adding a new column to an existing table using the ALTER TABLE statement.

Null values cannot be used for information required to distinguish one row in a table from another row in a table (for example, foreign or primary keys).
In program code, you can check for null values so that certain calculations are performed only on rows with valid (or not NULL) data. For example, a report can print the social security column only if there is data that is not NULL in the column. Eliminating null values when performing calculations can be important because certain calculations (such as an average) can be inaccurate if NULL columns are included.

If it is possible that null values may be stored in your data, it is a good idea to create queries and data-modification statements that either eliminate NULLs or transform NULLs into some other value (if you do not want null values appearing in your data).

Important To minimize maintenance and possible effects on existing queries or reports, it is recommended that you minimize the use of null values. Plan your queries and data-modification statements so that null values have minimal effect.

When null values are present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE. This need for three-valued logic is a source of many application errors. These tables outline the effect of introducing null comparisons.

--Ahamed S

fabianus76
Posting Yak Master

191 Posts

Posted - 2007-05-23 : 10:08:33

Dear Ahamed, this is great information. Thanks a lot !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources