locked
Strange Results from a Simple Query RRS feed

  • 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 

    Wednesday, April 11, 2007 10:44 PM

Answers

  • 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)

    Thursday, April 12, 2007 2:20 AM

All replies

  • 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

    Thursday, April 12, 2007 1:12 AM
  • 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)

    Thursday, April 12, 2007 2:20 AM
  • 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.

    Thursday, April 12, 2007 4:43 AM