'where exists clause'

[ShalakaT]

Hello all,

Two queries very similar are giving different row count. Can you please advice. TIA.

select count(*) from FBL.CELL
where exists
( select * from FBL.cell where cmpgn_cd = 'RS15001' and cell_st_dt = '2015-07-14'); Total 426308

But
select count(*) from FBL.cell where cmpgn_cd = 'RS15001' and cell_st_dt = '2015-07-14' Total = 9

I was expecting both the queries to return the same result. The first query is pulling the entire table.

Thanks,
Shalaka

 

[fredericofonseca]

off course it is pulling the whole table - the first query (outer query) has no correlation with the exist clause so as long as at least one record meets the criteria of the exists select all the outer query will get you all records of the table.

select count(*) from FBL.CELL t1
where exists 
( select * from FBL.cell t2 where t2.cmpgn_cd = 'RS15001' and t2.cell_st_dt = '2015-07-14'
and t1.cmpgn_cd  = t2.cmpgn_cd 
)

will reduce the number of records only to those where cmpgn_cd from the outer table matches those of the exists (inner query)
just an example as you didn't say what you wished to do

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886