Does 'where <>' detect a null field

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

Does 'where <>' detect a null field

Author

Topic

gshacte
Starting Member

17 Posts

Posted - 2010-03-24 : 16:30:18

I have a query that does a left outer join. It is basically working, but I don't think the 'where' clause is actually testing its condition (<>)when the columns in the secondary row in the join are set to null by the join when no match is found for a primary row. Do I have to explicity also test for 'NULL' in the where clause?

**
FROM ABCD.dbo.DispTech LEFT OUTER JOIN dbo.sheet

on ABCD.dbo.DispTech.ServiceMan = dbo.sheet.empid

where do the <> test, ie, compare NULL to whatever is in sheet.stime?

(sheet.stime <> ABCD.dbo.DispTech.DispDate)
***

If in the above, ABCD.dbo.DispTech.DispDate is NULL, will the 'where' evalaute NULL <> the contents of sheet.stime?

Gerry

Malik_Alamin
Starting Member

3 Posts

Posted - 2010-03-24 : 16:55:42

Hi Gerry,

Null represents the unknown and accordingly columnname can’t equal Null. However if want to check for null then include where ColumnName Is Null in the where clause or ColumnName is not Null (Depending on your check). Hopes this helps.

Malik

Malik Al-Amin
Senior DBA & ETL Developer
Malamin@FortitudeConsulting.Biz
www.FortitudeConsulting.Biz

“All things are possible with fortitude”
Add me on LinkedIn at: http://www.linkedin.com/pub/malik-al-amin/9/266/223

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 17:10:44

NULL doesn't match anything - so:

A = B will not match if B is null

but

A <> B will not match NULL either - so A <> B is NOT the exact opposite of A = B

Same for A < B - that does not match B where it is NULL and
A > B does not match B where B is NULL either

What I think you want is:


(sheet.stime <> ABCD.dbo.DispTech.DispDate OR ABCD.dbo.DispTech.DispDate IS NULL)

or possibly


(sheet.stime <> ABCD.dbo.DispTech.DispDate 
 OR (sheet.stime IS NOT NULL AND ABCD.dbo.DispTech.DispDate IS NULL)
)

gshacte
Starting Member

17 Posts

Posted - 2010-03-24 : 20:18:05

Thank you. I see how to do it now. But just for the sake of understanding, what is the result of using a comparison operator such as <> when one of the fields is NULL. Is the operation just ignored by sql because a null field has no value? That is, when comparing to a NULL field like this, it is basically a NOP?

Gerry

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 04:07:51

"what is the result of using a comparison operator such as <> when one of the fields is NULL"

Always False (well, technically UNKNOWN - so never True)

Note that a test for A = B is still false is both A and B are NULL

I wouldn't have called it a NOP as I think that depends on the flow of your logic statements

Easy to set up some tests, you can use a statement like


SELECT 1
WHERE	(1 = NULL OR 1=1)

and just fiddle with the WHERE clause to try various things


SELECT 1
WHERE	NULL IN (0, NULL)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-25 : 04:19:55

If you need to handle NULL's for comparisons it's quite common to use the functions ISNULL or COALESCE. The following will work:

ISNULL(a, '') = ISNULL(b, '')
COALESCE(a, '') = COALESCE(b, '')

But as a general rule you should be careful about adding functions around column names...they will prevent any index usage on the column.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:40:27

To add to all the above, NULL cannot be used in comparison using comparison operators under default conditions which is based on your ANSI NULL settings. If you are making ANSI NULL settings OFF then NULL will be regarded as a value and can be used with =,<> etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gshacte
Starting Member

17 Posts

Posted - 2010-03-25 : 15:46:10

Using the left outer join, if the primary table has no match, then the columns for the secondary table will be NULL for the joined row. Those nulls fields can be detected in the 'on' clause of the join with a test for NULL. If there is a 'where' clause, can the test for NULL be done there instead? I was told that the 'where' will not see the NULL fields of the unmatched record. That doesn't sound right to me. I suppose I can test this out by just comparing results.

Gerry

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 16:26:27

You cannot make that test for the NULL in the ON clause of the JOIN, you can only do it in the WHERE (well, as far as I know!)


SELECT Col1, Col2, ...
FROM ABCD.dbo.DispTech
    LEFT OUTER JOIN dbo.sheet 
        ON dbo.sheet.empid = ABCD.dbo.DispTech.ServiceMan
WHERE dbo.sheet.empid IS NULL -- Select rows from ABCD.dbo.DispTech where there is no corresponding row in dbo.sheet

gshacte
Starting Member

17 Posts

Posted - 2010-03-25 : 21:02:34

Your reply got me to thinking. When one of my conditions for the join was simply empid (on the joined table) being null, I was saying that for each row of the joining table, when you compare it to a row of the joined table, if you find that the joined table's row has a null empid, then join it. That is not what I wanted to do. My test for empid = NULL belonged in the Where clause. It is there that I wanted to test for a null empid, the result of the failure of the join to find a match. Thanks very much for you support with this.

Gerry

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-25 : 21:10:33

quote:
Originally posted by Kristen

You cannot make that test for the NULL in the ON clause of the JOIN, you can only do it in the WHERE (well, as far as I know!)

Sorry Kristen, what do you mean by that ?

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-03-26 : 04:25:16

This (as far as I know) won't match rows in ABCD.dbo.DispTech where there is no corresponding row in dbo.sheet


SELECT Col1, Col2, ...
FROM ABCD.dbo.DispTech
    LEFT OUTER JOIN dbo.sheet 
        ON dbo.sheet.empid = ABCD.dbo.DispTech.ServiceMan
       AND dbo.sheet.empid IS NULL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-26 : 04:39:41

quote:
Originally posted by Kristen

This (as far as I know) won't match rows in ABCD.dbo.DispTech where there is no corresponding row in dbo.sheet

SELECT Col1, Col2, ...
FROM ABCD.dbo.DispTech
LEFT OUTER JOIN dbo.sheet
ON dbo.sheet.empid = ABCD.dbo.DispTech.ServiceMan
AND dbo.sheet.empid IS NULL

Yes It may give you unexpected result

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources