ANSI NULLS - 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

SQL Server 2000 Forums

Transact-SQL (2000)

ANSI NULLS

Author

Topic

Funger
Starting Member

6 Posts

Posted - 2005-01-27 : 16:31:39

Hi All.
I am having problems with the following script:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

DECLARE @SystemTypeID int
DECLARE @SiteID int
DECLARE @AgencyID int
DECLARE @DistrictID int
DECLARE @TermID int
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SELECT @SystemTypeID = 19
SELECT @SiteID = 46
SELECT @AgencyID = 9
SELECT @DistrictID = 45
SELECT @TermID = 55
SELECT @StartDate = '1-1-2004'
SELECT @EndDate = '12-31-2004'

SELECT UNCOM_activity_type_id
FROM ActivityType
WHERE (SystemTypeID = @SystemTypeID) AND (DistrictID = @DistrictID)

SELECT activity_type_id, activity_type_name, @SystemTypeID AS SystemTypeID, @DistrictID AS DistrictID
FROM [tempdb].dbo.Activity_Types UNCOM_Activity_Types
WHERE (NOT (activity_type_id IN
(SELECT UNCOM_activity_type_id
FROM ActivityType
WHERE (SystemTypeID = @SystemTypeID) AND (DistrictID = @DistrictID))))

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

When I Run it I get the two result sets. The first one is all NULL values, and the second one is an empty recordset.

When in production, the second SELECT will actually be an insert statement. And I want to point out that the second SELECT is comparing data from the current database, and the tempdb.

If I set ANSI_NULLS to OFF, the it returns the data set that I am looking for, but I don't understand why, nor do I think it's the correct approach. To my understanding, ANSI_NULLS is a setting that determines how NULLS are compared using the equality operators... Except in this situation, I am comparing NULL values to integer values.

Does it have to do with the IN operation?

Can someone please tell me what is going on here? Thanks!

-Funger

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-27 : 17:03:38

With ANSI NULLS, NULL does not equal anything.
So you can't do WHERE SystemTypeID = NULL. Even NULL doesn't equal NULL.

You can compare things to NULL, i.e. WHERE SystemTypeID IS NULL, but you can't test for them being equal.

Without seeing what data your tables contain it's hard to guess exactly where your query isn't working, but I'd say that's the problem.

Damian

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-27 : 18:07:07

It looks to me like your second statement is trying to find all
the records from tempdb..Activity_Types that don't exist in ActivityType for a
given SystemTypeID and DistrictID.
Is that correct? If so, give this statement a shot:


SELECT	tempAT.activity_type_id
	,tempAT.activity_type_name
	,tempAT.SystemTypeID
	,tempAT.DistrictID
FROM 	[tempdb].dbo.Activity_Types tempAT

--Left join to accomplish the NOT IN
Left JOIN ActivityType realAT
		ON tempAT.activity_type_id = realAT.UNCOM_activity_type_id 
		AND tempAT.SystemTypeID = realAT.SystemTypeID
		AND tempAT.DistrictID = realAT.DistrictID

WHERE	--left JOINed table join column is NULL to accomplish the NOT IN
	realAT.UNCOM_activity_type_id is NULL

AND	tempAT.SystemTypeID = @SystemTypeID
AND	tempAT.DistrictID = @DistrictID

Funger
Starting Member

6 Posts

Posted - 2005-01-28 : 09:31:47

Being from the access world orgionally, I used this method a lot in the past, but the NOT IN SHOULD work too though, shouldn't it?

I'm just confused because whats basically happening is this:

Table1:
Col1
1
2
3
4

Table2:
ColA
NULL
NULL
NULL
NULL

SELECT Col1
FROM Table1
WHERE (NOT (Col1 IN
(SELECT ColA
FROM Table2)))

Why doesn't that return all of the records in Table1.Col1?

quote:
Originally posted by TG

It looks to me like your second statement is trying to find all
the records from tempdb..Activity_Types that don't exist in ActivityType for a
given SystemTypeID and DistrictID.
Is that correct? If so, give this statement a shot:

SELECT tempAT.activity_type_id
,tempAT.activity_type_name
,tempAT.SystemTypeID
,tempAT.DistrictID
FROM [tempdb].dbo.Activity_Types tempAT

--Left join to accomplish the NOT IN
Left JOIN ActivityType realAT
ON tempAT.activity_type_id = realAT.UNCOM_activity_type_id
AND tempAT.SystemTypeID = realAT.SystemTypeID
AND tempAT.DistrictID = realAT.DistrictID

WHERE --left JOINed table join column is NULL to accomplish the NOT IN
realAT.UNCOM_activity_type_id is NULL

AND tempAT.SystemTypeID = @SystemTypeID
AND tempAT.DistrictID = @DistrictID

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-28 : 10:41:17

I'll use your example to try and explain this:


set nocount on

create TAble #Table1
	(Col1  int)
create table #Table2 
	(ColA int)

insert #Table1
select 1 union
select 2 union
select 3 union
select 4 

insert #Table2
select NULL union all
select NULL union all
select NULL union all
select NULL

--this doesn't work because you're trying to compare values to NULL
SELECT Col1
FROM #Table1
WHERE (NOT (Col1 IN
(SELECT ColA
FROM #Table2)))

--This works because we are (in effect) supressing the matching records
--rather than showing the Non-Matching records
Select 	t1.*
From	#Table1 t1
Left JOIN #Table2 t2 ON t1.col1 = t2.colA
Where	t2.colA is NULL

--This NOW works because you're now you're not trying to compare anyting to NULL
SELECT Col1
FROM #Table1
WHERE (NOT (Col1 IN
(SELECT isNull(ColA,-1)
FROM #Table2)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources