Query problem... 1

[Neil Toulouse]

Hi guys!

I have a bit of a weird problem which is making me think there is something worng with the database itself, but don't really know where to look!

Basically there are two tables, and the query I am running is to only show values from Table 1 where there is no relating record in Table 2:

Table 1
-------
PK1      1
Name     FatSlug

PK1      2
Name     Neil

PK1      3
Name     John


Table 2
-------
PK2      10
PK1      1
Address  The Road

PK2      11
PK1      3
Address  The Street

So the query I am running is:

SELECT * FROM Table1 WHERE PK1 NOT IN(SELECT PK1 FROM Table2)

This should therefore return the 'Neil' record from Table1 (shouldn't it??), but instead it returns nothing.

I have checked and the record is definately not in Table 2, so I would expect a result.

To test this further I tried a similar query on another customer's data and it works as expected, ie the record is returned.

Going back to the original dataset, I have removed and re-created all the indexes, and have run the DBCC CHECKDB which shows no errors.

Can anyone advise where I can go from here to sort this out?

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[Neil Toulouse]

Just to add, I just deleted all but the first 100 records in Table 2, and now the query is working and I am getting a result back.

Does this help to point to the root of the problem?

Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[MarkSweetland]

It works for me. I'd start by double checking your data to ensure the datatype on the PK columns are the same (i.e. int vs. char)

set nocount on 

declare @Table1 TABLE
(
	PK1 int ,
	Name varchar(10)
)
insert into @table1(PK1, name)
select 1, 'FatSlug'
union all 
select 2, 'Neil'
union all 
select 3, 'John'

declare @Table2 TABLE
(
	PK2 int,
	PK1 int,
	Address varchar(10)
)
insert into @table2(PK2, PK1, Address)
select 10, 1, 'The Road'
union all 
select 11, 3, 'The Street'

SELECT * FROM @Table1 WHERE PK1 NOT IN(SELECT PK1 FROM @Table2)

SELECT * FROM @Table1 a
WHERE  NOT exists
	(SELECT 1 FROM @Table2 b where a.PK1 = b.pk1)

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[gmmastros]

The problem is that you have a row in Table2 where PK1 is NULL. Weird huh? Take a look at this:

Declare @Table1 Table(PK1 Int, Name VarChar(20))
-------
Insert Into @Table1 Values(1,'FatSlug')
Insert Into @Table1 Values(2,'Neil')
Insert Into @Table1 Values(3,'John')

Declare @Table2 Table(PK2 Int, PK1 Int, Address VarChar(50))

Insert Into @Table2 Values(10,1,'The Road')
Insert Into @Table2 Values(11,3,'The Street')

SELECT * 
FROM   @Table1 
WHERE  PK1 NOT IN(SELECT PK1 FROM @Table2)

Copy/paste the code above in to a query window and run it. It returns the expected row (the Neil row).

Now copy/paste this:

Declare @Table1 Table(PK1 Int, Name VarChar(20))
-------
Insert Into @Table1 Values(1,'FatSlug')
Insert Into @Table1 Values(2,'Neil')
Insert Into @Table1 Values(3,'John')

Declare @Table2 Table(PK2 Int, PK1 Int, Address VarChar(50))

Insert Into @Table2 Values(10,1,'The Road')
Insert Into @Table2 Values(11,3,'The Street')
[!]Insert Into @Table2 Values(12,NULL, NULL)[/!]

SELECT * 
FROM   @Table1 
WHERE  PK1 NOT IN(SELECT PK1 FROM @Table2)

This time you don't get any results because of the NULL.

If you modify the query to use EXISTS instead of IN, you'll get the data you expect.

Declare @Table1 Table(PK1 Int, Name VarChar(20))
-------
Insert Into @Table1 Values(1,'FatSlug')
Insert Into @Table1 Values(2,'Neil')
Insert Into @Table1 Values(3,'John')

Declare @Table2 Table(PK2 Int, PK1 Int, Address VarChar(50))

Insert Into @Table2 Values(10,1,'The Road')
Insert Into @Table2 Values(11,3,'The Street')
Insert Into @Table2 Values(12,NULL, NULL)

SELECT * 
FROM   @Table1 As T1
WHERE  [!]NOT Exists(SELECT PK1 FROM @Table2 As T2 Where T1.PK1 = T2.PK1)[/!]

Alternatively, you can filter out the NULLS like this:

Declare @Table1 Table(PK1 Int, Name VarChar(20))
-------
Insert Into @Table1 Values(1,'FatSlug')
Insert Into @Table1 Values(2,'Neil')
Insert Into @Table1 Values(3,'John')

Declare @Table2 Table(PK2 Int, PK1 Int, Address VarChar(50))

Insert Into @Table2 Values(10,1,'The Road')
Insert Into @Table2 Values(11,3,'The Street')
Insert Into @Table2 Values(12,NULL, NULL)

SELECT * 
FROM   @Table1 As T1
WHERE  PK1 NOT IN(SELECT PK1 FROM @Table2 [!]Where PK1 is not null[/!])

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

Why not use left join with is null


SELECT t1.*
FROM @Table1 t1
left join @Table2 t2
on t1.pk1=t2.pk1
Where t2.pk1 is null

 

[dhulbert]

I've had a simiar problem like this It happens more often with larger files in the second select.

I find it is more accurate and safer to always us the left outer join as per PWise above.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Neil Toulouse]

Hi guys!

Many thanks for the advice! I wasn't planning to go down the outer join route as I don't need any of the data from the second table, and non of the keys should be NULL anyway, so obviously we have a bug in our system somewhere.

Anyways, many thanks I can now investigate further

Neil

I like work. It fascinates me. I can sit and look at it for hours...