sub query not returning expected results

Question

Baffling query results or lack of...

I am trying to return a record set by excluding records from the set that may already exist in a second table.

SELECT        trID
FROM            ttTrct
WHERE        (trID NOT IN
             (SELECT rteVehID
              FROM  rtHist
              WHERE  (rteSchedDT = '11/19/2012 12:00:00 AM')))


ORDER BY tractorID

actual where clause is...

WHERE  (rteSchedDT = CAST(@targetDate AS DATETIME))

But have been also trying with static date value to avoid any issues there.

Three records exist in rtHist

 rteVehID    rteSchedDT
 NULL        11/12/2012 12:00:00 AM
 NULL        11/19/2012 12:00:00 AM
 1645        11/19/2012 12:00:00 AM

Five records exist in ttTrct

I am expecting my result set to include 4 records and not 1645. But I only get NULL. Also, If the third record in the rtHist table were also NULL instead of 1645, I also get no results. Of course the static date will eventually be a variable.

Any ideas on what I am missing? thanks in advance. SSEE 2008 R2

Taryn · Accepted Answer · 2013-01-03T18:59:02.547

1

You can use NOT EXISTS:

SELECT trID
FROM  ttTrct t
WHERE not exists (SELECT rteVehID
                   FROM  rtHist h
                   WHERE  rteSchedDT = '11/19/2012 12:00:00 AM'
                     and t.trID = h.rteVehID)
ORDER BY tractorID

See SQL Fiddle with Demo

Here is a great article about the differences between NOT IN and EXISTS and LEFT JOIN:

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

edited Jan 03 '13 at 18:59

answered Jan 03 '13 at 18:49

Taryn

242,637
56
362
405

Absolutely Awsome!!! Thank you!!!! For some reason I can not mark this as answer. I will try again shortly. – htm11h Jan 03 '13 at 18:58

score 0 · Answer 2 · answered Jan 03 '13 at 18:54

I've had similar situations where nulls slip in to the subquery and mess things up. Try

...
WHERE  (rteSchedDT = '11/19/2012 12:00:00 AM'
        and rteScheDT is not null)))
...

I suspect this isn't the problem here, but it's worth a shot.

also, why

(rteSchedDT = CAST(@targetDate AS DATETIME))

? Just make @targetDate a datetime variable.

score 0 · Answer 3 · edited May 23 '17 at 11:48

The cause is that the NOT IN clause expands to a series of AND statements. Since comparing any value to NULL is NULL and AND NULL always results in NULL, your where clause is never TRUE. It's always NULL.

Try:

SELECT   trID
FROM     ttTrct
WHERE    (trID NOT IN
         (SELECT rteVehID
         FROM  rtHist
         WHERE  (rteSchedDT = '11/19/2012 12:00:00 AM')
             AND rteVehID IS NOT NULL))

Alternately you could just use a join, which will very likely have an identical or superior execution plan:

SELECT     t.trID
FROM       ttTrct t
INNER JOIN rtHist r
        ON t.trID = r.rtHist
WHERE      r.rteSchedDT = '11/19/2012 12:00:00 AM'

Source

sub query not returning expected results

3 Answers3