Selecting Records Not Found - 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)

Selecting Records Not Found

Author

Topic

brandonl
Yak Posting Veteran

58 Posts

Posted - 2003-12-19 : 10:18:42

I have two tables: TableA and TableB.

They each only have one column. There's duplicate names, and I'm trying to filter out the ones that exist in only one table.

I want to select records that are found in TableA but not in TableB.

I've tried doing this-but it doesn't seem to work:

SELECT Name FROM TableA WHERE Name NOT IN(SELECT NodeName FROM TableB)

Is there another way I should be doing this?

TIA.

~BrandonL

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-19 : 10:34:36

Sure it does....you could even do it a couple of ways


USE Northwind
GO

CREATE TABLE TableA ([Name] varchar(255))
CREATE TABLE TableB ([NodeName] varchar(255))
GO

INSERT INTO TableA ([Name])
SELECT 'Brett' UNION ALL
SELECT 'Tara' UNION ALL
SELECT 'Rob' UNION ALL
SELECT 'Damain' UNION ALL
SELECT 'Bill' UNION ALL
SELECT 'Nigel' UNION ALL
SELECT 'Arnold' UNION ALL
SELECT 'Jay' UNION ALL
SELECT 'Sam' UNION ALL
SELECT 'Jeff' UNION ALL
SELECT 'Valter'

INSERT INTO TableB (NodeName)
SELECT 'Brett' UNION ALL
SELECT 'Tara' UNION ALL
SELECT 'Rob' UNION ALL
SELECT 'Damain' UNION ALL
SELECT 'Bill' UNION ALL
SELECT 'Nigel' UNION ALL
SELECT 'Arnold' UNION ALL
SELECT 'Jay' UNION ALL
SELECT 'Sam' UNION ALL
SELECT 'Jeff'

    SELECT [Name] 
      FROM TableA 
     WHERE [Name] NOT IN(SELECT NodeName FROM TableB)

    SELECT [Name] 
      FROM TableA
 LEFT JOIN TableB
	ON [Name] = NodeName
     WHERE NodeName IS NULL 

    SELECT [Name] 
      FROM TableA a
     WHERE NOT EXISTS (SELECT * FROM TableB b WHERE a.[Name] = b.NodeName)
GO

DROP TABLE TableA
DROP TABLE TableB
GO

Brett

8-)

brandonl
Yak Posting Veteran

58 Posts

Posted - 2003-12-19 : 11:08:22

Thanks! I used the EXISTS and the JOIN and got the same results-not sure why the NOT IN didn't work for me

~BrandonL

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-19 : 11:09:29

Did you cut and paste my sample code?

They all should work....

Are you saying it doesn't?

Brett

8-)

brandonl
Yak Posting Veteran

58 Posts

Posted - 2003-12-19 : 11:17:36

quote:
Originally posted by X002548

Did you cut and paste my sample code?

They all should work....

Are you saying it doesn't?

Using your exact code works perfectly, but for some reason-the same code on the live data, the NOT IN only returns 0 results, where the EXISTS and JOIN return 42K records.

Same code you used, very strange.

~BrandonL

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-19 : 11:27:47

There are no miracles...

post your ddl and the SELECT with the IN...

Do a SHOW PLAN though, I think the Join will perform the best...just a guess...

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-19 : 12:07:52

This thread reminds me of a bad query day I had in recent months.

I found that

WHERE X IN (SELECT abc from ...)

Would not work if the select list had any duplicates. I don't know if I had too many margaritas, but to fix the problem, I used

WHERE X IN (SELECT DISTINCT abc from ...)

And it worked. I never bothered to chase down the root cause.

Someone tell me , is this smoke?

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-19 : 16:43:03

quote:
Originally posted by SamC

Someone tell me , is this smoke?

Sammy boy...smokin the ganja....

I love it when my monitor just starts to melt right in front of my eyes...

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-20 : 03:22:34

Are any of the Nodename values in TableB NULL? A NULL in TableB might cause the IN statement to return zero rows.

Owais

We make a living out of what we get, but we make a life out of what we give.

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-20 : 10:15:45

I know using DISTINCT to fix the problem I mentioned didn't make any sense. I tried to reproduce the problem for study and wasn't able to get it to break.

Must have been something else.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-20 : 11:08:02

Perhaps this might provide a clue to what I was talking about:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28542

Owais

We make a living out of what we get, but we make a life out of what we give.

brandonl
Yak Posting Veteran

58 Posts

Posted - 2003-12-20 : 13:33:53

quote:
Originally posted by mohdowais

Are any of the Nodename values in TableB NULL? A NULL in TableB might cause the IN statement to return zero rows.

Yes, some values in NodeName are null. I'll try filtering them out and see what I get.

~BrandonL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources