Using IN vs NOT IN does not return the expected results

• Blaine Hess

  Valued Member

  Points: 61

  More actions

  November 10, 2005 at 4:23 pm

  #95726

  I had a set of SQL queries forwarded to me with some strange results, and I was wondering if anyone has seen this type of behavior.  I will just let them speak for themselves:

  1)

  select count(*) from po_shipment

  2)

  select count(*) from po_shipment where container not in ('UGMU8164295','TTNU9894207','TTNU5732321','TRLU6391297','TRLU5711014',

  'TRLU5502143','TRIU5971969','TGHU8566326','TGHU4554899','TGHU4418260',

  'TCNU9688456','NYKU4566314','HDMU5413020','HDMU4204529','GVCU5168925',

  'GATU8524148','GATU8379672','GATU4089865','FSCU7021010','FSCU4422338',

  'EMCU9133315','EMCU2496047','EMCU2386081','EMCU1028720','EISU9828716',

  'EISU1548771','EISU1520120')

  3)

  select count(*) from po_shipment where container in ('UGMU8164295','TTNU9894207','TTNU5732321','TRLU6391297','TRLU5711014',

  'TRLU5502143','TRIU5971969','TGHU8566326','TGHU4554899','TGHU4418260',

  'TCNU9688456','NYKU4566314','HDMU5413020','HDMU4204529','GVCU5168925',

  'GATU8524148','GATU8379672','GATU4089865','FSCU7021010','FSCU4422338',

  'EMCU9133315','EMCU2496047','EMCU2386081','EMCU1028720','EISU9828716',

  'EISU1548771','EISU1520120')

   

  The results returned were as follows:

  5720

  5716

  0

   

  By looking at this I would have expected the results to be :

  5720

  5716

  4

   

  I have run update statistics on the table to make sure that was fine, but I am stumped at the moment.

  Any ideas???

  Thanks Very Much-Blaine Hess

  ---

  Thx-Blaine Hess
  

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  November 10, 2005 at 4:25 pm

  #603348

  are there any Null Values?

  Select * from  po_shipment where container is NULL

• Blaine Hess

  Valued Member

  Points: 61

  More actions

  November 10, 2005 at 4:36 pm

  #603352

  I am stand in awe...

  The query returned 4 records, the ones with null values...

  Thank you very much.

  Now my next question is:

  Is this behavior by design?

  ---

  Thx-Blaine Hess
  

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  November 10, 2005 at 4:50 pm

  #603353

  It's by definituion of NULL.

  NULL means "don't know".

  So, it's nether IN nor NOT IN.

  For your task it's better to use LEFT JOIN.

  _____________
  Code for TallyGenerator

• Blaine Hess

  Valued Member

  Points: 61

  More actions

  November 10, 2005 at 4:53 pm

  #603354

  That makes sense.  At least I know what has to be done.

  Thanks again.

  ---

  Thx-Blaine Hess