none
Where Not

    Question

  • These both not returning same number, am i missing anything, I would like change the first query to write in other format, because i have lot of and conditions like this. I have given only one as example.

    -- Original query ; working
    SELECT COUNT(*) FROM  P
    INNER JOIN  R WITH ( NOLOCK ) 
    ON R.Id = P.Id
    where P.Pk NOT IN (
    		SELECT	p.Pk
    		WHERE	R.Name = 'S'
    		AND p.Type = 'O' )
    and R.Date = '7/16/2014'			
    		
    		
    -- Test query ; not returning same rows as above		
    SELECT COUNT(*) FROM  P
    INNER JOIN  R WITH ( NOLOCK ) ON R.Id = P.Id
    where  NOT (R.Name = 'S' AND p.Type = 'O')
    and R.Date = '7/16/2014'	

    Saturday, July 19, 2014 11:31 PM

Answers

  • Those two statements will not return the same result if either R.Name or P.Type is NULL and R.Date = '7/16/2014'

    To see why that is true, consider the case where R.Name is NULL and P.Type = 'O'.

    The first query's inner select is Select p.Pk WHERE R.Name = 'S' AND p.Type = 'O'.  That will return zero rows because R.Name is NULL.  So the NOT IN will be TRUE and since the R.Date condition is TRUE so the entire where is true and that row is counted.

    In the second case, you check if R.Name = 'S'.  Since R.Name is NULL, the result of that logical comparison is UNKNOWN (that is, neither true nor false).  You then AND that with p.Type = 'O' which is TRUE.  So you get UNKNOWN AND TRUE - the result of that is UNKNOWN.  You then do a NOT of that UNKNOWN values which is still UNKNOWN.  You then and that with R.Date = '7/16/2014', so the final result of your where condition is UNKNOWN.  So this row is not counted.

    So the first and second queries can return different results.

    I gather from your question that you want a query using the form of the second query that will return the same result as the first query.  To do that you have to handle the NULL's.  One way would be to do

    SELECT COUNT(*) FROM  P
    INNER JOIN  R ON R.Id = P.Id
    where  Not (IsNull(R.Name, 'S') <> 'S' And IsNull(p.Type, 'O') <> 'O')
    and R.Date = '7/16/2014'
    Tom

    Sunday, July 20, 2014 3:53 AM
  •  That should be because of Null values either in R.name or P.id columns...check this code...  i added "not null" condition to prevent it..check this code

    declare @P Table(id int,pk varchar(20),Type char(2))
    declare @R Table(id int,name varchar(20),date date)
    Insert into @P(ID,pk,Type)
    Values (1,'s','O'),
    (1,NULL,'O')
    
    Insert into @R
    Values (1,'q','07/16/2014'),
    (1,'s','07/16/2014'),
    (1,NULL,'07/16/2014')
    
    SELECT R.*,P.* FROM  @P P
    INNER JOIN  @R R 
    ON R.Id = P.Id
    where P.Pk not in (
    		SELECT	p.Pk
    		WHERE	R.Name = 'S'
    		AND p.Type = 'O')
    	and R.date = '07/16/2014' and R.Name is not null
    		
    SELECT R.*,P.* FROM  @P P
    INNER JOIN  @R R  ON R.Id = P.Id
    where  NOT (R.Name = 'S' AND p.Type = 'O')
    and R.date = '07/16/2014'
    


    Hope it Helps!!

    Sunday, July 20, 2014 3:14 AM
  • SELECT count(*) FROM  R 
    WHERE not exists (select * from P where R.Id = P.Id and R.Name = 'S'AND p.Type = 'O')
    and R.date = '07/16/2014' 

    Sunday, July 20, 2014 3:46 AM

All replies

  •  That should be because of Null values either in R.name or P.id columns...check this code...  i added "not null" condition to prevent it..check this code

    declare @P Table(id int,pk varchar(20),Type char(2))
    declare @R Table(id int,name varchar(20),date date)
    Insert into @P(ID,pk,Type)
    Values (1,'s','O'),
    (1,NULL,'O')
    
    Insert into @R
    Values (1,'q','07/16/2014'),
    (1,'s','07/16/2014'),
    (1,NULL,'07/16/2014')
    
    SELECT R.*,P.* FROM  @P P
    INNER JOIN  @R R 
    ON R.Id = P.Id
    where P.Pk not in (
    		SELECT	p.Pk
    		WHERE	R.Name = 'S'
    		AND p.Type = 'O')
    	and R.date = '07/16/2014' and R.Name is not null
    		
    SELECT R.*,P.* FROM  @P P
    INNER JOIN  @R R  ON R.Id = P.Id
    where  NOT (R.Name = 'S' AND p.Type = 'O')
    and R.date = '07/16/2014'
    


    Hope it Helps!!

    Sunday, July 20, 2014 3:14 AM
  • SELECT count(*) FROM  R 
    WHERE not exists (select * from P where R.Id = P.Id and R.Name = 'S'AND p.Type = 'O')
    and R.date = '07/16/2014' 

    Sunday, July 20, 2014 3:46 AM
  • Those two statements will not return the same result if either R.Name or P.Type is NULL and R.Date = '7/16/2014'

    To see why that is true, consider the case where R.Name is NULL and P.Type = 'O'.

    The first query's inner select is Select p.Pk WHERE R.Name = 'S' AND p.Type = 'O'.  That will return zero rows because R.Name is NULL.  So the NOT IN will be TRUE and since the R.Date condition is TRUE so the entire where is true and that row is counted.

    In the second case, you check if R.Name = 'S'.  Since R.Name is NULL, the result of that logical comparison is UNKNOWN (that is, neither true nor false).  You then AND that with p.Type = 'O' which is TRUE.  So you get UNKNOWN AND TRUE - the result of that is UNKNOWN.  You then do a NOT of that UNKNOWN values which is still UNKNOWN.  You then and that with R.Date = '7/16/2014', so the final result of your where condition is UNKNOWN.  So this row is not counted.

    So the first and second queries can return different results.

    I gather from your question that you want a query using the form of the second query that will return the same result as the first query.  To do that you have to handle the NULL's.  One way would be to do

    SELECT COUNT(*) FROM  P
    INNER JOIN  R ON R.Id = P.Id
    where  Not (IsNull(R.Name, 'S') <> 'S' And IsNull(p.Type, 'O') <> 'O')
    and R.Date = '7/16/2014'
    Tom

    Sunday, July 20, 2014 3:53 AM