DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: General
       *****************************************************
       #Post#: 4--------------------------------------------------
       NOT IN with TOP
       By: srinivasma_exceldbp Date: January 31, 2014, 5:14 am
       ---------------------------------------------------------
       /*
       --** NOT IN WITH TOP
       NOT IN should not be used . NOT IN with TOP in sub query, output
       result set is indeterminate
       since TOP may pick any row without ORDER BY
       This is repro of the bug found in pegasus
       Developer is using NOT IN with TOP  which provides wrong results
       Despite informing the nature of the error , this has been closed
       by dev team, informing syntax NOT IN with TOP  is correct
       insert into  @a values ( 1,'abcd',2)
       insert into  @a values ( 2,'efgh',3)
       insert into  @a values ( 3,'ijkl',2)
       insert into  @a values ( 4,'mnop',2)
       insert into  @b values (10, 1,'pqrs')
       insert into  @b values (20, 1,'1tuvw')
       insert into  @b values (30, null,'3pqrs')
       insert into  @b values (40, 4,'4wvrs')
       insert into  @b values (40, 5,'5xyz')
       --** Returns existing rows even it is present in sub query table
       select a.* from @a a where typeid = 2  and keyid not in ( select
       top 1 keyid from @b)
       -- Correct way to write the query
       select a.* from @a a where typeid = 2  and not exists (select
       top 1 1 from @b b where a.keyid = b.keyid )
       *****************************************************