Strange Results from a Simple Query

Question

I have written the following query that is supposed to exclued matching rows from a NOT IN sub query.   I'm finding that not all matching rows are being supressed from the main query.   If I run the main query and store it in a temp table and then supress the results from the sub query I get the correct result.   

 

If I reformat the query to use EXCLUDE instead of a NOT IN I also get the correct results. 

 

Is SQL Server applying the predicates in correctly?

 

Here is the SQL I'm running and doesn't return extepected results

 

SELECT

email_id

FROM

Campaign.email

where

WOMEN = 'Y'

OR (

WOMEN is NULL

and

MEN is null

and

BOY is null

and

PREF_GIRL is null

and

BABY is null

)

AND

email_id not in ( SELECT

email_id

FROM

Campaign.EMAIL_XREF

)

 

This format works

 

SELECT

email_id

FROM

Campaign.email

where

WOMEN = 'Y'

OR (

WOMEN is NULL

and

MEN is null

and

BOY is null

and

PREF_GIRL is null

and

BABY is null

)

EXCEPT

SELECT

email_id

FROM

Campaign.EMAIL_XREF

 

Any ideas would be very gratefuly recieved as this is driving me mad.

 

Thanks

 

Marcus 

Answer 1

Just a guess...

 

I am thinking that EXCEPT is more forgiving in terms of leading and/or trailing spaces, whereas NOT IN is probably looking for exact matches, which would not necessarily occur with leading and/or trailing spaces in your column.  (Here I am guessing that EMAIL_ID is some sort of character string, not a numeric.)

 

Dan

Answer 2

I am pretty sure that this is a code formatting issue, leading to illogical grouping of operators, since AND is higher precedence than OR.  So all WOMEN would be returned.  Looking at your query:

SELECT  email_id
FROM    Campaign.email
where   WOMEN = 'Y'
        OR (WOMEN is NULL
            and MEN is null
            and BOY is null
            and PREF_GIRL is null
            and BABY is null)
        AND email_id not in (SELECT email_id
                             FROM   Campaign.EMAIL_XREF)

Here you want all rows where:
WOMEN = 'Y'
OR
(WOMEN is NULL and MEN is null and BOY is nulland PREF_GIRL is null  and BABY is null)
AND
email_id not in (SELECT email_id FROM   Campaign.EMAIL_XREF)

But in the second query:

--This format works

SELECT  email_id
FROM    Campaign.email
where   WOMEN = 'Y'
        OR (WOMEN is NULL
            and MEN is null
            and BOY is null
            and PREF_GIRL is null
            and BABY is null)
EXCEPT
SELECT  email_id
FROM    Campaign.EMAIL_XREF


You want
WOMEN = Y
OR
(WOMEN is NULL and MEN is null and BOY is nulland PREF_GIRL is null  and BABY is null)

Then, exclude those that are in the set:
SELECT  email_id
FROM    Campaign.EMAIL_XREF


In essense the first query says:
Condition OR Condition2 AND Condition3
the second
(Condition OR Condition2) AND Condition3

Leading to the following situation:

select case when (1=1) or (1=1) and (1=2) then 'True' else 'False' end
                 TRUE  or  TRUE and FALSE
                      TRUE or FALSE
                          TRUE

select case when ((1=1) or (1=1)) and (1=2) then 'True' else 'False' end
                 (TRUE  or  TRUE) and FALSE
                      TRUE AND FALSE
                          FALSE


Which was the scenic route to get to:

SELECT  email_id
FROM    Campaign.email
where   (WOMEN = 'Y'
                OR (WOMEN is NULL
                        and MEN is null
                        and BOY is null
                        and PREF_GIRL is null
                        and BABY is null))
        AND email_id not in (SELECT email_id
                             FROM   Campaign.EMAIL_XREF)

Answer 3

The following query will return the result as you expected,

 

Code Snippet

SELECT

email_id

FROM

Campaign.email

where

WOMEN = 'Y'

OR (

WOMEN is NULL

and

MEN is null

and

BOY is null

and

PREF_GIRL is null

and

BABY is null

)

AND

email_id not in ( SELECT

email_id

FROM

Campaign.EMAIL_XREF Where email_id is not null

)

 

 

 Note:

 When you use not in (subquery) you should ensure that the subquery resultset doesn't hold any null value.