T-SQL varchar comparison question

Question

I'm using MS Sql 2005.

Why does this give me the correct results (returns 169 rows)...

select
  * 
from 
  [import_Data] 
where 
  [import_Data].name not in 
  (
    select 
      [import_Data].name 
    from 
      [import_Data] 
      inner join [resource] on [import_Data].name = [resource].name
    where 
      [import_Data].ProviderTypeID = 4 
      and [resource].IsDeleted = 0
  )
  and [import_Data].ProviderTypeID = 4

But this doesn't (returns 0 rows)...

select 
  * 
from 
  [import_Data] 
where 
  [import_Data].name not in 
  (
    select 
      [resource].name 
    from 
      [resource] 
    where 
      IsDeleted = 0
  ) 
  and [import_Data].ProviderTypeID = 4

The only difference between the name columns is that [resource].name is varchar(500) and [import_Data].name is varchar(300).

Answer 1

My guess is that there is a null resource.name in your resource table which is throwing all the comparisons off. Why do nulls cause a problem? Per "Guru's Guid to TSQL" and I'm paraphrasing "ANSI guidelines state that an expression that compares a value for equality to NULL always returns NULL." So any null in the list throws the whole thing off.

In your first query your inner join is excluding those nulls.

So you have three options

• Don't use NOT IN, use NOT EXISTS
• Use ISNULL on resource.name to eliminate the nulls
• Change the null handling behaviour by setting ANSI_NULLS OFF.

An example of not exists using a correlated subquery (warning aircode)

SELECT *
FROM [import_Data] 
WHERE NOT EXISTS(
        select [resource].name from [resource] where IsDeleted = 0 AND [resource].name = [import_Data].name
    )
  AND [import_Data].ProviderTypeID = 4

Answer 2

My guess is that you face a difference in how the IN operator and the = operator work internally.

How about this:

select 
  * 
from 
  [import_Data] 
where 
  not exists 
  (
    select 1 
    from   [resource] 
    where  name = [import_Data].Name and IsDeleted = 0
  ) 
  and [import_Data].ProviderTypeID = 4