Unmatched record or NOT IN - 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)

Unmatched record or NOT IN

Author

Topic

Face It
Starting Member

5 Posts

Posted - 2006-03-16 : 15:55:13

Why does the first select return orphaned records and the second doesn't?

Status has a one to many relationship to FulfillDetails

SELECT S.StatusID
FROM FulfillDetails FD RIGHT OUTER JOIN
Status S ON FD.StatusID = S.StatusID
WHERE (FD.StatusID IS NULL)

SELECT S.StatusID
FROM Status S
WHERE S.StatusID NOT IN
(SELECT FulfillDetails.StatusID
FROM FulfillDetails)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-16 : 16:13:03

Read about Null related material in Books OnLine (SQL Server help)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-17 : 00:34:51

You used Right Join in first query. Read about Joins in sql server help file

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-03-17 : 07:40:07

Is there any FulfillDetails row where StatusID is null?

Face It
Starting Member

5 Posts

Posted - 2006-03-20 : 11:01:18

While the first two responses didn't help. I understand Joins and Null values (maybe I really don't in the SQL world) the third response made me look a little deeper. Because I do have null values in the FulfillDetails table.

This works for the second query to see any orphaned records.

SELECT S.StatusID
FROM Status S
WHERE S.StatusID NOT IN
(SELECT FulfillDetails.StatusID
FROM FulfillDetails
WHERE FulfillDetails.StatusID IS NOT NULL)

Still not sure why this works now but I'm sure the null values in the FulfillDetails table screws up the comparing some how.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-20 : 12:46:07

>>Still not sure why this works now but I'm sure the null values in the FulfillDetails table screws up the comparing some how.

Understanding this concept will make you a MUCH better sql developer. Here are some simple statements against 2 temp tables. When you fully understand why the results are what they are you'll be able to provide a lot more sql solutions as well as avoid some "rookie" mistakes.


--Outer JOINs and NULL comparisons

set nocount on
create table #t1 (t1id int not null primary key, t2id int null)
create table #t2 (t2id int not null primary key)

insert #t2 values (1)
insert #t2 values (2)

insert #t1 values (10, null)
insert #t1 values (20, 1)
insert #t1 values (30, 2)
insert #t1 values (40, 3)

----------------------------------------------------------------
print '
outer join'
select	t1.t1id, t1.t2id [t1.t2id], t2.t2id [t2.t2id]
from	#t1 t1
left outer join #t2 t2
		on t2.t2id = t1.t2id

print '
outer join (with: WHERE <outer join criteria> IS NULL)'
select	t1.t1id, t1.t2id [t1.t2id], t2.t2id [t2.t2id]
from	#t1 t1
left outer join #t2 t2
		on t2.t2id = t1.t2id
where	t2.t2id is null


print '
not in subquery (cannot compare NULLs)'
select	t1.t1id, t1.t2id
from	#t1 t1
where	t1.t2id not in (select t2id from #t2)


print 'inner join'
select	t1.t1id, t1.t2id [t1.t2id], t2.t2id [t2.t2id]
from	#t1 t1
inner join #t2 t2
		on t2.t2id = t1.t2id


drop table #t1
drop table #t2


set nocount off
--cannot compare null values
--must use OUTER JOIN (see above)
--or <col> IS NULL
select a
from	(
	select 1 as a union all
	select null
	) derivedTable
where	a = null
or	null = null

----------------------------------------------------------------
----------------------------------------------------------------
OUTPUT:

outer join
t1id        t1.t2id     t2.t2id     
----------- ----------- ----------- 
10          NULL        NULL
20          1           1
30          2           2
40          3           NULL


outer join (with: WHERE <outer join criteria> IS NULL)
t1id        t1.t2id     t2.t2id     
----------- ----------- ----------- 
10          NULL        NULL
40          3           NULL


not in subquery (cannot compare NULLs)
t1id        t2id        
----------- ----------- 
40          3

inner join
t1id        t1.t2id     t2.t2id     
----------- ----------- ----------- 
20          1           1
30          2           2

a           
----------- 

(0 row(s) affected)

Be One with the Optimizer
TG

Face It
Starting Member

5 Posts

Posted - 2006-03-21 : 11:16:31

Thanks TG but here is a sample of what I was running into and I am confused on why it happens. I added the id value of 4 to #t2 and I'm looking for the orphaned record from #t2 when compared to #t1.

Why doesn't the id '4' get returned in the first Select?

set nocount on
create table #t1 (t1id int not null primary key, t2id int null)
create table #t2 (t2id int not null primary key)

insert #t2 values (1)
insert #t2 values (2)
insert #t2 values (4)

insert #t1 values (10, null)
insert #t1 values (20, 1)
insert #t1 values (30, 2)
insert #t1 values (40, 3)

----------------------------------------------------------------
print '
not in subquery (cannot compare NULLs)'
select t2.t2id
from #t2 t2
where t2.t2id not in (select t2id from #t1)

print '
not in subquery with Where is not null '
select t2.t2id
from #t2 t2
where t2.t2id not in (select t2id from #t1 where t2id is not null)

print '
values that t2 is comparing against.'
select t2id from #t1

drop table #t1
drop table #t2

set nocount off

----------------------------------------------------------------
OUTPUT:

not in subquery (cannot compare NULLs)
t2id
-----------

'No records returned'

not in subquery with Where is not null
t2id
-----------
4

values that t2 is comparing against
t2id
-----------
NULL
1
2
3

Face It
Starting Member

5 Posts

Posted - 2006-03-21 : 11:36:17

I looked more into the null comparison.

When null is compared to a value it doesn't return a TRUE or FALSE it returns UNKNOWN. Therefore the id '4' doesn't get returned.

If ANSI_NULLS is off I would get id '4' returned.

BUT

Regardless of the ANSI_NULLS setting, Null values are always considered equal for the purposes of the ORDER BY, GROUP BY, and DISTINCT keywords.

I guess this is why I got thrown off. Never used a Select inside a IN statement before.

Well that and Access will return the id '4'. I have more time in Access then SQL Server.

Thanks all

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-21 : 12:41:29

>>I'm looking for the orphaned record from #t2 when compared to #t1

--this is the same old LEFT OUTER JOIN just reverse the tables (#t2 left outer join #t1)
select #t2.*
from #t2
Left join #t1
on #t1.t2id = #t2.t2id
where #t1.t2id is null

Be One with the Optimizer
TG

Face It
Starting Member

5 Posts

Posted - 2006-03-21 : 13:10:56

Yup, got that.

I was wondering why my In() didn't work.

It's because how a NULL value comparison returns UNKNOWN. Therefore never returning my orphaned records.

I needed to remove Nulls in my comparison or turn ANSI_NULLS OFF to get my results using In()

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-21 : 13:36:08

sounds like you've got it.

<col> in (1,2) behaves similar to <col>=1 AND <col>=2
<col> not in (1,2) behaves similar to <col><>1 AND <col><>2

both of these rely on comparing null values.

btw,
turning ansi_nulls off is usually not a good idea. It can screw up all kinds of expected results. I forgot where I got this but I saved it because it was interesting. before you run the code try to guess which rows will be returned:


set ansi_nulls off

go
create table T (
i int
)


insert into T values (1)
insert into T values (null)
go


declare @null int
set @null = cast(@null as int)

select 0, i, 'not (i = null)' from T where not (i = null)
union all
select 1, i, 'not (1 = i)' from T where not (1 = i)
union all
select 2, i, 'not (i <> null)' from T where not (i <> null)
union all
select 3, i, '-i = null' from T where -i = null
union all
select 4, i, 'i = -null' from T where i = -null
union all
select 5, i, 'i in (-null)' from T where i in (-null)
union all
select 6, i, 'i = @null' from T where i = @null
union all
select 7, i, '= cast(@null as int)' from T where i = cast(@null as int)
union all
select 8, i, 'i = i' from T where i = i
union all
select 9, i, 'i <> i' from T where i <> i
union all
select 10, i, 'i <> null' from T where i <> null
union all
select 11, i, '<= null' from T where i <= null
union all
select 12, i, 'null = null' from T where null = null
order by 1, 2
go

drop table T

Be One with the Optimizer
TG

pootle_flump

1064 Posts

Posted - 2006-03-21 : 14:30:54

For most purposes you can think of NULL as UNKNOWN. If you think in this way most of the things you are observing with NULLS become less counter intuitive (TGs script shows this empirically if you keep ANSI NULLS on).


1+1 = 2
1+UNKNOWN = UNKNOWN
(1=1) = True
(1=UNKNOWN) = UNKNOWN
(UNKNOWN = UNKNOWN) = UNKNOWN

HTH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-22 : 01:10:08

quote:
1+1 = 2
1+UNKNOWN = UNKNOWN
(1=1) = True
(1=UNKNOWN) = UNKNOWN
(UNKNOWN = UNKNOWN) = UNKNOWN

Now it is WELL KNOWN

Madhivanan

Failing to plan is Planning to fail

pootle_flump

1064 Posts

Posted - 2006-03-22 : 05:33:41

quote:
Originally posted by madhivanan
Now it is WELL KNOWN

Madhivanan

Failing to plan is Planning to fail

Lol - did I go on?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources