2

Lets say I have 100 items in the Products table that have Status= 'New' when I run: 

select * from Products where Status='New' and Product_GUID COLLATE DATABSE_DEFAULT IN (
SELECT Product_GUID COLLATE DATABASE_DEFAULT FROM [OracleLinkedServer]..[MAINDB].[VIEW_PRODUCTS]
)

I get 50 items in the result.

But when I run NOT IN like this

select * from Products where Status='New' and Product_GUID COLLATE DATABSE_DEFAULT NOT IN (
SELECT Product_GUID COLLATE DATABASE_DEFAULT FROM [OracleLinkedServer]..[MAINDB].[VIEW_PRODUCTS]
)

I get no items back, what I am expecting to get is the items that were not returned in the previous IN query, where am I going wrong here? Is the collation an issue?

Also the first query takes a while to return as it is going out to a linked server, but the second returns immediately as if it makes no attempt to get data from the linked server. The linked server is Oracle. Appreciate any help.

mbwasi
  • 3,612
  • 3
  • 30
  • 36
  • Are you *sure* there are products that should be returned by the query? Have you looked at the execution plan of the query? – Luaan Aug 18 '15 at 08:42

1 Answers1

1

Probably one of the following.

  1. The subquery returns a NULL
  2. [OracleLinkedServer]..[MAINDB].[VIEW_PRODUCTS] does not contain a column called Product_GUID so this is resolved from the outer scope.

(My guess is the second from your description)

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • There were NULLS in the subquery, added WHERE Product_GUID IS NOT NULL and its working fine. Thanks – mbwasi Aug 18 '15 at 10:06