No Join Predicate

• Judy Why

  Hall of Fame

  Points: 3656

  More actions

  December 23, 2008 at 8:42 am

  #128765

  Hello everyone,

  One developer came to my desk to show me one very simple query, like below,

  SELECT EmailAddr_, List_ FROM dbo.members_ m where m.EmailAddr_ not in (SELECT email from temp)

  Table "Members_" has records 520,000. non-clustered index on EmailAddr_ ;

  Table "temp" has records 1074, only one column and no index.Temp has null value.

  His query returns 0 result. And it should return at least 500,000 records.

  There is Warning sign on “Nexted Loops”, inside say “no join predicate”

  The execution plan:

  |--Hash Match(Right Anti Semi Join, HASH:([temp].)=([m].[EmailAddr_]), RESIDUAL:([m]. [EmailAddr_]=[temp].))

  |--Table Scan(OBJECT:([lyris].[dbo].[temp]))

  |--Nested Loops(Left Anti Semi Join)

  |--Clustered Index Scan(OBJECT:([lyris].[dbo].[members_].[IX_members_List_EmailLC] AS [m]))

  |--Row Count Spool

  |--Table Scan(OBJECT:([lyris].[dbo].[temp]), WHERE:([temp].=NULL))

  New Query 1: resturn right results

  SELECT EmailAddr_, List_ FROM dbo.members_ m where m.EmailAddr_ not in (SELECT email from temp where email is not null )

  |--Hash Match(Right Anti Semi Join, HASH:([temp].)=([m].[EmailAddr_]), RESIDUAL:([m].[EmailAddr_]=[temp].))

  |--Table Scan(OBJECT:([lyris].[dbo].[temp]), WHERE:([temp].<>NULL))

  |--Nested Loops(Left Anti Semi Join)

  |--Clustered Index Scan(OBJECT:([lyris].[dbo].[members_].[IX_members_List_EmailLC] AS [m]))

  |--Row Count Spool

  |--Table Scan(OBJECT:([lyris].[dbo].[temp]), WHERE:([temp].=NULL AND [temp].<>NULL))

  New Query 2: return right results

  SELECT m.EmailAddr_, List_ FROM dbo.members_ m where not exists (SELECT t.email from temp t where m.EmailAddr_=t.email)

  |--Hash Match(Right Anti Semi Join, HASH:([t].)=([m].[EmailAddr_]), RESIDUAL:([m].[EmailAddr_]=[t].))

  |--Table Scan(OBJECT:([lyris].[dbo].[temp] AS [t]))

  |--Clustered Index Scan(OBJECT:([lyris].[dbo].[members_].[IX_members_List_EmailLC] AS [m]))

  My Questions is

  Why “No Join Predicate” happened when table contains “Null” Value?

• Lori-138565

  Ten Centuries

  Points: 1340

  More actions

  December 23, 2008 at 9:19 am

  #916851

  I've run into this before. Here's my understanding (and I'm sure that I'll be corrected if I'm wrong): If ANSI_NULLS is set ON, NULL is some unknown value, so the predicate x <> NULL evaluates to false. If ANSI_NULLS is set OFF, the predicate x<>NULL will evaluate to true and return those NULL rows in your query.

  Here's a link that explains the use of ANSI_NULLS: http://msdn.microsoft.com/en-us/library/ms188048(SQL.90).aspx

  Hope this helps

  ---

  Thanks,

  Lori