'Not In' Question

Question

'Not In' Question

hkflight

SSCommitted

Points: 1949
More actions
October 6, 2011 at 4:33 pm

#260121

I am trying to compare two tables and identify records in table B that are not in table A based on a guid.
I am getting to results back but I know there are guids in table B that arent in table A.
Here is some sample data
Table A
Guid
111
122
133
Table B
111
122
133
13
12
1
When I run the query:
select guid
from TableB
where TableB.guid not in
(select guid from TableA)
This query (as I understand it) should return 13, 12, 1. I am getting 0 rows returned. Could it be doing a partial match since the number '1' is contained within '133'? If so, how do I get around this?
Thank You!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

I'm going to make a wild guess... there's at least one NULL in the guid column in TableA?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hkflight SSCommitted Points: 1949 More actions · Answer 2

hkflight

SSCommitted

Points: 1949

October 6, 2011 at 4:46 pm

#1391530

Damn, I feel not good. Thank you....(im new)

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

And here's an explanation: http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Performance Guard (Shehap) Ten Centuries Points: 1297 More actions · Answer 4

As a generic commands for such cases, it should be like below:

select guid

from TableB

where TableB.guid not in

(select distinct ISNULL( guid,0) from TableA)

[font="Arial Black"]Performance Guard[/font]

[font="Courier New"]Just ask me to get 0 sec for all queries[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

We recently had a similar problem at work and, of course, everyone jumped in on the ISNULL bandwagon as a resolution... and, as some expected, the code took comparatively forever to run especially since the columns being compared where NVARCHAR(MAX) columns.

One of the Developers beat the tar out of Google searches and it brought them right back to Books Online. Lookup "EXCEPT and INTERSECT (Transact-SQL)" (without the quotes) in Books Online and learn that EXCEPT and INTERSECT treat NULLs as equals. Here's the significant quote from BOL...

When you compare rows for determining distinct values, two NULL values are considered equal.

I'll also add that it's blindingly fast compared to other IS NULL, ISNULL, or COALESCE comparisons.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Performace Guard (Shehap) (10/9/2011)
As a generic commands for such cases, it should be like below:
select guid
from TableB
where TableB.guid not in
(select distinct ISNULL( guid,0) from TableA)

No, not at all.

The distinct is unnecessary (duplicates don't matter in an IN) and the ISNULL will make that a non-SARGable predicate.

Either the query should be changed to NOT EXISTS (which doesn't care about NULLS), or it needs to be changed like this:

select b.guid

from TableB b

where b.guid not in (select a.guid from TableA a where a.guid IS NOT NULL)

Additionally, the guid columns in both should be indexed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

T. Hill SSC Journeyman Points: 82 More actions · Answer 7

Would this be slightly better than using "not in" in terms of performance?

select b.guid

from TableB b

left join TableA a ON a.guid = b.guid

where a.guid is null

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass