Hi,

Here in below mentioned query inner join between table1 and table2 returns 0 records hence join with table3 should come out within less than second time, irrespective of whether table3 contains millions records or trillian recors but that is not happening. can anyone please help me to know how this inner join works internally betwen table1 and table3.

SELECT *
FROM
table1 a
INNER JOIN
table2 b
ON a.payment_period_id = NAME_CONST('in_payment_period_id',80)
AND a.is_locked=0
AND a.payment_term_id IS NOT NULL
AND a.payment_term_id = b.payment_term_id
INNER JOIN
table3 c
ON a.ps_ad_id = c.ps_ad_id

Please see this second query here it will come out fast as subquery result set is being compared with table3.

SELECT *
FROM
(select a.ps_ad_id from
table1 a
INNER JOIN
table2 b
ON a.payment_period_id = NAME_CONST('in_payment_period_id',80)
AND a.is_locked=0
AND a.payment_term_id IS NOT NULL
AND a.payment_term_id = b.payment_term_id ) a
INNER JOIN
table3 c
ON a.ps_ad_id = c.ps_ad_id

Thanks in advance for your help.
Amit Shah.