primary index in IN clause or exists clause >?

Database
Teradata Employee

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

Tags (1)
5 REPLIES
Senior Supporter

Re: primary index in IN clause or exists clause >?

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. 

Enthusiast

Re: primary index in IN clause or exists clause >?

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.

Teradata Employee

Re: primary index in IN clause or exists clause >?

Thanks guys.

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

Thanks

Senior Supporter

Re: primary index in IN clause or exists clause >?

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>
Teradata Employee

Re: primary index in IN clause or exists clause >?

Yes,thanks for the help:)