sql - DB2 - IN vs. JOINS -

sql - DB2 - IN vs. JOINS -

i found info regarding in, joins , exists, , there efficiency. me question never answered, or reply not stated. examples produce same results.

here 2 examples:

select count(distinct a.id) ,b ,c a.id = b.id , b.id = c.id , 'some other conditions'; select count(distinct a.id) a.id in (select distinct b.id b ,c b.id = c.id , 'some condition') , 'some condition';

running against hundreds of millions of rows, 1 of them clear more proficient other?

i tried out in sql 100000 records , similar query without conditions , saw execution plan. output result same both. both have query cost of 50%. statistics on, 1st has 3891 physical reads on table2 , 425 on table1, while other has 4593 physical reads on table2 , 79 on table1. logical reads , read-ahead reads similar both queries. both queries work same way.

my query

select count( distinct table1.column) table1 bring together table2 on table1.column= table2.column; select count (distinct column) table1 column in (select distinct column table2);

but, when utilize select column , not count(distinct column) i.e. not aggregated, bring together have query cost of 5% compared other has 95% , reads many times less in joins.

so non-aggregated queries 'joins' more efficient 'in'.

sql db2