If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Does Not IN automatically reduce Count
So here is my code:
SELECT "15 Minutes" AS Category, COUNT(*) AS [Workloads Completed] FROM [Strategy] WHERE (ENDQUEUE NOT IN("t11arz") AND (STARTQUEUE IN("Pad", "Paper")) AND (QUEUETIME 15) When I query this without the ENDQUEUE NOT IN line, the count is about a 100 higher. But (("t11arz"))is just gibberish. I don't have any inputs like it. I get a 100 less by putting in the ENDQUEUE with ANYTHING. Anyone know why? Amin |
#2
|
|||
|
|||
Does Not IN automatically reduce Count
My guess is that you have about 100 records where the field ENDQUEUE is null.
If you use a criteria on a field you either have to tell it you want to pull the nulls also or it eliminates them automatically. -- KARL DEWEY Build a little - Test a little "Amin" wrote: So here is my code: SELECT "15 Minutes" AS Category, COUNT(*) AS [Workloads Completed] FROM [Strategy] WHERE (ENDQUEUE NOT IN("t11arz") AND (STARTQUEUE IN("Pad", "Paper")) AND (QUEUETIME 15) When I query this without the ENDQUEUE NOT IN line, the count is about a 100 higher. But (("t11arz"))is just gibberish. I don't have any inputs like it. I get a 100 less by putting in the ENDQUEUE with ANYTHING. Anyone know why? Amin |
#3
|
|||
|
|||
Does Not IN automatically reduce Count
Just to elaborate slightly on Karl's reply, he's almost certainly right about
it being NULLs in the EndQueue column, but the 100 rows in question must also have a value in the StartQueue column of "Pad" or "Paper" (I'm ignoring the QueueTime 15 for the sake of simplicity). The reason for this is that NULL is not a value, but the absence of a value, an 'unknown', so when you compare NULL with anything the result is neither TRUE nor FALSE but NULL. So if StartQueue is Null and EndQueue = "Pad" in a row the expression NULL IN("t11arz") AND "Pad" IN ("Pad", "Paper") equates to NULL AND TRUE. This evaluates to NULL so the row is not returned, as only those rows where the WHERE clause evaluates to TRUE are returned. BTW the plot thickens even more if the NOT IN predicate is used against a subquery rather than a value list. In this case if ANY of the rows returned by the subquery contains a Null at the relevant column position the result is NULL. So x NOT IN (subquery) AND y IN (x,y,z) would return zero rows if any of the rows returned by the subquery contains a Null, even if no other rows contain the value of x. This is a mathematically more correct result, but not usually what's required of a query. The way to avoid this is to use the NOT EXISTS predicate against a correlated subquery instead of the NOT IN predicate. The EXISTS and NOT EXISTS predicates will also usually give better performance than IN and NOT IN. Ken Sheridan Stafford, England "Amin" wrote: So here is my code: SELECT "15 Minutes" AS Category, COUNT(*) AS [Workloads Completed] FROM [Strategy] WHERE (ENDQUEUE NOT IN("t11arz") AND (STARTQUEUE IN("Pad", "Paper")) AND (QUEUETIME 15) When I query this without the ENDQUEUE NOT IN line, the count is about a 100 higher. But (("t11arz"))is just gibberish. I don't have any inputs like it. I get a 100 less by putting in the ENDQUEUE with ANYTHING. Anyone know why? Amin |
Thread Tools | |
Display Modes | |
|
|