m14cus


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




Re: Strange Results from a Simple Query

DanR1


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






Re: Strange Results from a Simple Query

Louis Davidson

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)







Re: Strange Results from a Simple Query

Manivannan.D.Sekaran

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.