Search code examples
sqlt-sqlsql-server-2012collationlinked-server

Linked server NOT IN query confusion

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.

Solution

  • 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)