Does Not IN automatically reduce Count

[Amin]

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

 

[KARL DEWEY]

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.

 

[Ken Sheridan]

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