All Forums Database
Sun_shine_jgd 39 posts Joined 07/13
15 Oct 2013
primary index in IN clause or exists clause >?

Hello,
 
If i have column emp_id as an index. and if u use it in 'in' clause or exists clause will i get the beiifit of PI as in = clause
foe eg:
sel *  from table where emp_id in ( select id from table_b where <condition>)
*****
or 
sel *  from table where emp_id exists( select id from table_b where <condition>)
Will this query takes PI for fetching data.
Thanks
 

ulrich 816 posts Joined 09/09
15 Oct 2013

it will depend if id is the pi of table_b and id has the same data type as emp_id.
you can veryfy be your own by reading the explain. 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Adharssh 36 posts Joined 08/13
16 Oct 2013

Hi,
"Exists' will be more efficient than "IN" Clause. Nulls will not be handled properly when we use the IN Clause.
 
Thanks & Regards,
Adharssh Hospet Srinivasa Rao.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

Sun_shine_jgd 39 posts Joined 07/13
16 Oct 2013

Thanks guys.
So Ulrich, You mean if its data type matches it will be helpful and will giv good performance.
 
Thanks
 

ulrich 816 posts Joined 09/09
16 Oct 2013

the datatype need to match and both columns need to be PI on the table. 
And again - check the explain. Is one of the tables redistributed? In case yes, no PI join.
You could also rewirte your query

sel *  
from table_a a
     join 
     table_b b
      on a.emp_id = b.id 
 where b.<condition>

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Sun_shine_jgd 39 posts Joined 07/13
16 Oct 2013

Yes,thanks for the help:)

You must sign in to leave a comment.