NOT IN vs NOT EXISTS
-
I cant understand why these two statements gets different result for me.
Statement with NOT IN gets no results.
select *
from Job_Queue
where JobQueueID not in
(SELECT JobQueueID
from Payment)
Statement with NOT EXISTS get a list of results.
select *
from Job_Queue jq
where not exists
(select *
from Payment p
where jq.JobQueueID = p.JobQueueID)
Table definitions:
CREATE TABLE [dbo].[Job_Queue](
[JobQueueID] [int] IDENTITY(1,1) NOT NULL,
...
CONSTRAINT [Job_Queue_PK] PRIMARY KEY CLUSTERED
([JobQueueID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Payment](
[PaymentID] [bigint] IDENTITY(1,1) NOT NULL,
[JobQueueID] [int] NULL,
...
CONSTRAINT [Payment_PK] PRIMARY KEY CLUSTERED
([PaymentID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
-
I found out that this statement
select *
from Job_Queue
where JobQueueID not in
(SELECT JobQueueID
from Payment
where JobQueueID is not null)
will get the correct result. Is it correct that the last line is needed, seams strange to me.
-
peterswe (8/12/2013)
I found out that this statementselect *
from Job_Queue
where JobQueueID not in
(SELECT JobQueueID
from Payment
where JobQueueID is not null)
will get the correct result. Is it correct that the last line is needed, seams strange to me.
Gail's article will explain the reason - http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
-
peterswe (8/12/2013)
I found out that this statementselect *
from Job_Queue
where JobQueueID not in
(SELECT JobQueueID
from Payment
where JobQueueID is not null)
will get the correct result. Is it correct that the last line is needed, seams strange to me.
The short answer here is that yes, the line is needed. You must have NULL values in Payment.JobQueueID, so NOT IN won't match because NULL is interpreted as unknown. Using NOT EXISTS is the safer approach. I read an article on this once after encountering the same issue myself...I'll see if I can find it.
-
DOH! Sean posted the article while I was typing. I guess I don't have to look now.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply