SQL - NOT IN explained

Question

I am working in a project which needs top performance in SQL results, and was looking to optimize a query, but after some trial and error I am having some trouble with IN.

-- THIS RETURNS NO RESULTS AT ALL.
SELECT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY  
     where TICKETID = 't6UJ9A002MJC'                                         

-- THIS RETURNS 4 RESULTS.
SELECT sysdba.C_TICKETPROVIDERS.* FROM sysdba.C_TICKETPROVIDERS 
     where sysdba.C_TICKETPROVIDERS.TICKETID = 't6UJ9A002MJC'

-- THIS RETURNS NO RESULTS AT ALL.
SELECT sysdba.C_TICKETPROVIDERS.* FROM sysdba.C_TICKETPROVIDERS 
     where sysdba.C_TICKETPROVIDERS.TICKETID NOT IN 
        (SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY) 

Shouldn't the last query result at least the same 4 results as the previous one, since there are no matching results???

Answer 1

Do the sysdba.History.TicketID and sysdba.C_TICKETPROVIDERS.TICKETID match in terms of datatype and length?

I've also found group by's to be quicker than distinct.

Lastly check the collation types for the database, it might be that the collation is case sensitive and for some reason one is uppercased and the other isnt?

Can you provide the table schemas?

Answer 2

"SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY" returns null, hence the last query doesnt return any rows. Cant compare against null result like that. Use "NOT EXISTS".

Answer 3

The piece NOT IN (NULL) in your WHERE clausole returns false.

Try with:

SELECT sysdba.C_TICKETPROVIDERS.* FROM sysdba.C_TICKETPROVIDERS 
     where NOT EXISTS 
         (SELECT TOP 1 1 
          FROM sysdba.HISTORY
          WHERE sysdba.HISTORY.TICKETID  = sysdba.C_TICKETPROVIDERS.TICKETID) 

• The subquery seeks for at least one record.
  
  • If it finds it, returns 1, and the NOT EXISTS clausole fails, as expected.
  • If it doesn't find it, returns NULL, and the NOT EXISTS will succeed.

Answer 4

Just as a lark, try this:

SELECT sysdba.C_TICKETPROVIDERS.* FROM sysdba.C_TICKETPROVIDERS  
     where sysdba.C_TICKETPROVIDERS.TICKETID NOT IN  
        (SELECT DISTINCT sysdba.HISTORY.TICKETID 
         FROM sysdba.HISTORY 
         Union
         Select 'xxxxxxxxxxxxxxxx') 

If this works, yr problem is that on your platform, Not In will not function properly against an EMpty set...

Answer 5

The query (SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY) return NULL.

You have:

ticked id 1 is NOT IN (NULL)? FALSE

ticked id 2 is NOT IN (NULL)? FALSE

ticked id 3 is NOT IN (NULL)? FALSE

....and so on....

No record can match your where condition NOT IN. So you get NO RESULTS AT ALL.

Try with this query:

SELECT sysdba.C_TICKETPROVIDERS.* FROM sysdba.C_TICKETPROVIDERS 
     where NOT EXIST  
        (SELECT TOP 1 sysdba.HISTORY.TICKETID FROM sysdba.HISTORY
         WHERE  HISTORY.TICKETID=sysdba.C_TICKETPROVIDERS.TICKETID)