SQL - NOT IN explained

Question

I am working on a project that needs maximum performance in SQL results, and was looking for query optimization, but after some trial and error, I am having some problems 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) 

If the last query result at least does not match the previous one, since there are no corresponding results ???

Answer 1

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

Try:

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) 

• A subquery is looking for at least one record.
  • If he finds it, returns 1, and clausol NOT EXISTSwill fail, as expected.
  • , NULL, NOT EXISTS .

Answer 2

"SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY" null, . . "NOT EXISTS".

Answer 3

, :

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

, yr , Not In EMpty...

Answer 4

(SELECT DISTINCT sysdba.HISTORY.TICKETID FROM sysdba.HISTORY) NULL.

:

ticked id 1 IN (NULL)? FALSE

ticked id 2 NOT IN (NULL)? FALSE

ticked id 3 IN (NULL)? FALSE

.... ....

NOT IN. , .

:

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)

Answer 5

Do sysdba.History.TicketID and sysdba.C_TICKETPROVIDERS.TICKETID do in terms of data type and length?

I also found the group faster than simple.

Finally, check the sort types for the database, is it possible that the sort is case sensitive, and for some reason one of them is uppercase and the other is not?

Can you provide table schemas?