Usage of IN Clause in a Database query

Question

I have written query, I wanted to know the effect of usage of IN clause in Query.

Query I Select * from khatapayment_track Where Property_ID IN (Select Property_id from khata_header where DIV_ID=2)

Query II Select * from khatapayment_track KPT, khata_header KH Where kh.Property_id=KPT.Property_id and KH.DIV_Id=2

I would like to know 1) which one is faster 2) Any effects of using IN clause, is it advisable to use if a query has a 3 IN clause.

Can you please help me with examples

Answer 1

Query II is faster in your example as you are using subquery in Query I. but consider follwing example which returns similar o/p but query i will returns faster

select * from tableName  where id in (1,2,3,4)

is similar to

select * from tableName  where id =1 OR id =2 OR id =3 Or id =4

Answer 2

Your second query is faster, but it is better to use joins (it looks nicer and it have the same execution plan):

select
  *
from
  khatapayment_track t
    inner join khata_header h on (h.property_id = t.property_id)
where
  h.div_id = 2

Also you can use mysql profiler to compare your queries.