WHERE EXISTS vs. LEFT OUTER JOIN vs. IN(..) Statement

[DiscoStuart]

I executed the following three SQL statements. The first two produced identical results, but the last one returned no results, can somebody please tell me why?

select b.plslid
from dbz.tblMyrAllCompleted a inner join dbz.tblallprimusplac b
on a.jobid=b.jobid
where not exists (select z.plslid from dba.tblImpPrimusTemp z where b.plslid=z.plslid);

select b.plslid
from dbz.tblMyrAllCompleted a inner join dbz.tblallprimusplac b
on a.jobid=b.jobid
left outer join dba.tblImpPrimusTemp c
on b.plslid=c.plslid
where c.plslid is null;

select b.plslid
from dbz.tblMyrAllCompleted a inner join dbz.tblallprimusplac b
on a.jobid=b.jobid
where b.plslid not in (select z.plslid from dba.tblImpPrimusTemp z);

 

[saikrish]

Hi DiscoStuart,
Hey can you provide the table structure and if possible the sample data on which you ran your query?

 

[DiscoStuart]

Hi,

Here are the table structures:

create table dbz.tblMyrAllCompleted(jobid int);

create table dbz.tblAllPrimusPlac(
PlslID int,
Category char(30),
Department char(30),
ordid int,
jobid int,
costcentre int);

create table dba.tblImpPrimusTemp(
PlslID int,
invoice_no int,
ItemName char(30),
Hrs numeric(6,2),
Item_Total numeric(8,2),
GST numeric(6,2),
PRT numeric(6,2),
Invoice_Total numeric(8,2),
PeriodEndDate timestamp);

And some sample data:

dbz.tblMyrAllCompleted-
64668
65357
65399

dbz.tblAllPrimusPlac
96797,'Permanent','Customer Service',47922,64668,301
96796,'Permanent','Customer Service',47922,64668,301
96795,'Permanent','Customer Service',47922,64668,301
96302,'Temporary','Internet',48430,65357,301
96301,'Temporary','Internet',48430,65357,301
96300,'Temporary','Internet',48430,65357,301
96388,'Temporary','HR',48463,65399,301


dba.tblImpPrimusTemp
96388,211988,'Normal',40.00,2553.60,255.36,204.93,5822.85,'2002-09-29 00:00:00.000'
96388,211988,'Normal',40.00,2553.60,255.36,204.93,5822.85,'2002-09-29 00:00:00.000'
96300,212005,'Normal',28.00,640.64,64.06,36.12,1026.41,'2002-09-29 00:00:00.000'
96300,212005,'Night',10.25,259.63,25.96,36.12,1026.41,'2002-09-29 00:00:00.000'
96300,210608,'Night',11.75,297.63,29.76,34.89,991.48,'2002-09-22 00:00:00.000'
96302,212003,'Normal',31.75,726.44,72.64,32.20,914.87,'2002-09-29 00:00:00.000'
96301,212004,'Weekend',7.00,217.14,21.71,33.70,957.48,'2002-09-29 00:00:00.000'
96301,210607,'Night',4.00,101.32,10.13,25.64,728.54,'2002-09-22 00:00:00.000'
96301,212004,'Normal',25.00,572.00,57.20,33.70,957.48,'2002-09-29 00:00:00.000'

 

[DiscoStuart]

I think I have figured out the cause of this problem. It's because there was a NULL value for the 'PlslID' in table dba.tblImpPrimusTemp. Once I removed this record the third query gave me the same results as the first two queries.

It must have something to do with the way NULLs are treated in an IN clause.

Thanks anyway.