October 28, 2010 at 3:04 pm
Ok, so I have two simple queries, one returns rows, and the other one does not
--this returns one row
select
top 10 *
from table1 with (nolock)
where filter1 > 90 and id=11305894 and id not in
(select id from table2 with (nolock) where tabledate>='10/28/2010' )
--once the where clause is removed from the subquery there are no results returned.
select
top 10 *
from table1 with (nolock)
where filter1 > 90 and id=11305894 and id not in
(select id from table2 with (nolock) )
Both tables are fairly large at few million rows of data.
October 28, 2010 at 3:26 pm
Yep, not sure of the question here, though.
That's a NOT IN statement you're using, so it's checking to see if the id exists. When it exists, it's not included.
So, you've got that id somewhere in the table, it just doesn't match the where condition, so it pops up in the first one.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 28, 2010 at 3:38 pm
Not surprising.
With the where clause removed, the subquery will have more rows in it. Since it's a not in, more rows in the subquery can mean less rows in the final result.
As an aside, are inconsistent and potentially wrong results acceptable? See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2010 at 4:26 pm
IIRC, you should also always be careful to screen out NULL values when using IN / NOT IN (really generally you should avoid IN / NOT IN, and instead use the correct type of join, but that's a different matter).
WHERE ... [NOT] IN (SELECT col FROM ... WHERE col IS NOT NULL AND ...)
IIRC, a NULL value can play havoc with an IN / NOT IN statement.
Scott Pletcher, SQL Server MVP 2008-2010
October 28, 2010 at 4:35 pm
scott.pletcher (10/28/2010)
(really generally you should avoid IN / NOT IN, and instead use the correct type of join, but that's a different matter).
Why do you say that?
What do you feel is wrong with in/exists and their negations?
IIRC, a NULL value can play havoc with an IN / NOT IN statement.
In, no. Not In, yes. A single NULL in a NOT IN will result in the entire thing returning no rows at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2010 at 7:10 am
Luk (10/28/2010)
Ok, so I have two simple queries, one returns rows, and the other one does not--this returns one row
select
top 10 *
from table1 with (nolock)
where filter1 > 90 and id=11305894 and id not in
(select id from table2 with (nolock) where tabledate>='10/28/2010' )
--once the where clause is removed from the subquery there are no results returned.
select
top 10 *
from table1 with (nolock)
where filter1 > 90 and id=11305894 and id not in
(select id from table2 with (nolock) )
Both tables are fairly large at few million rows of data.
I should have added that id 11305894 doesn't exist in table2 at all so not using a where clause or limiting it to certain time frame should have no impact on the output, right? This particular id field is an integer data type and doesn't allow nulls.
October 29, 2010 at 10:24 am
so it does turn out it was because of the nulls which started to appear after a recent development deploy...great...
thanks for helping out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply