Not exists doesnt return the expected result

All replies

• 

  

  0

  Sign in to vote

  As soon as Table2 contains at least one row, you query will return nothing, of course, because you filter if a row exists in Table2.

  ---

  Olaf Helper

  [ Blog] [ Xing] [ MVP]

  • Proposed as answer by Naomi N Thursday, March 24, 2016 3:47 PM

  Thursday, March 24, 2016 2:58 PM
• 

  

  1

  Sign in to vote

  Try:

  select  Doc from Table1
  Except
  select  Doc from Table2

  
  

  • Marked as answer by Lio1972 Thursday, March 24, 2016 3:14 PM

  Thursday, March 24, 2016 3:04 PM
• 

  

  0

  Sign in to vote

  SELECT * FROM
  
  (SELECT DISTINCT Doc FROM Table1 )
  
  ) T1 
  
  WHERE doc NOT IN (SELECT * FROM Table2 )

  
  

  ---

  Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
  Really enjoyed it? See my profile!
  My Tech Net Articles.

  Thursday, March 24, 2016 3:42 PM
• 

  

  0

  Sign in to vote

  Hi Lio1972,

  Script below using EXISTS to get the output is for your reference.

  declare @Table1 table (Doc varchar(10))
  insert into @Table1 values ('T1'), ('T2'), ('T1'), ('T2')
  
  declare @Table2 table (Doc varchar(10))
  insert into @Table2 values ('T1'), ('T3'), ('T4')
  
  SELECT * FROM (
  			SELECT DISTINCT Doc FROM @Table1
  			) T1
  WHERE NOT EXISTS (SELECT 1 FROM @Table2 T2 WHERE T2.Doc = T1.Doc)

  ---

  Sam Zha
  TechNet Community Support

  

  
  

  • Edited by Sam ZhaMicrosoft contingent staff Friday, March 25, 2016 5:42 AM
  • Proposed as answer by Sam ZhaMicrosoft contingent staff Thursday, March 31, 2016 1:55 AM
  • Marked as answer by Sam ZhaMicrosoft contingent staff Friday, April 1, 2016 3:10 AM

  Friday, March 25, 2016 5:40 AM
• 

  

  0

  Sign in to vote

  DECLARE @t1 table (Alpha char(10))
  DECLARE @t2 table (Alpha char(10))
  
  insert into @t1(Alpha)
  SELECT 'T1'
  UNION
  SELECT 'T2'
  
  insert into @t2(Alpha)
  SELECT 'T1'
  UNION
  SELECT 'T3'
  UNION
  SELECT 'T4'
  
  
  SELECT * FROM @t1
  SELECT * FROM @t2
  
  --- Answer(1)
  SELECT t1.* FROM @t1 t1
  LEFT JOIN @t2 t2 on t1.Alpha=t2.Alpha
  where t2.Alpha IS NULL
  
  --- Answer(2)
  SELECT t1.* FROM @t1 t1
  where NOT EXISTS (SELECT 1 from @t2 t2 where t2.Alpha=t1.Alpha)

  Friday, April 1, 2016 9:24 AM