Subquery Issue

Database
Enthusiast

Subquery Issue

Hello Friends

Can anybody educate on the proper subquery functionality.I have a subquery which goes into Product Join in Explain plan.
I have remodelled it and there is no more Product join on it..but want to double sure if the logic is same.Also I wolud like to know the reason for Product Join in the 1st query.

Here are both queries:

Query resulting in Product join:
Note -> T1 and T3 are same table

Select T1. aa,T2.bb
from databasename.tablename T1
inner join
databasename.tablename1 T2
on T1.yy = T2.xx
WHERE T1.DDDDD= 1
AND T1.zz = (
SELECT MAXIMUM ( T3.zz)
FROM databasename.tablename T3
WHERE T1.aa = T3.aa
)

Query with better performance and without Producrt join:

Select T1. aa,T2.bb
from databasename.tablename T1
inner join
databasename.tablename1 T2
on T1.yy = T2.xx
WHERE
AND (T1.aa,T1.zz) IN (
SELECT T3.aa
MAXIMUM ( T3.zz)
FROM databasename.tablename T3
WHERE T3.DDDDD= 1
GROUP BY 1
)
3 REPLIES
Teradata Employee

Re: Subquery Issue

In first query we have three tables and there is only one join condition in the subquery WHERE T1.aa = T3.aa. The subquery written is a correlated subquery so inner query will be executed for each and every row retrieved in the outer query. So in this case if we have the join condition included T1.yy = T2.xx in our inner query it will not pop up with product join.

In second case we have a subquery which will return an entire result set to the outer query so no join is required for T3.

Thanks
Anand(ADC)
Junior Contributor

Re: Subquery Issue

The condition "DDDDD= 1" is in different places:
q1: in the outer block
q2: in the subquery

This might be the reason for different plans (and might return different answer sets, too).

The task seems to be "give me the row with the latest/greatest value, this is usually done with an OLAP function:
Select T1. aa,T2.bb
from databasename.tablename T1
inner join
databasename.tablename1 T2
on T1.yy = T2.xx
QUALIFY
RANK() OVER (PARTITION BY T1.aa ORDER BY T1.zz DESC) = 1

-- or
QUALIFY T1.zz = MAX(T1.zz) OVER (PARTITION BY T1.aa)

And you might move the QUALIFY on T1 into a Derived Table so it's processed before the join.

Dieter
Dieter
Teradata Employee

Re: Subquery Issue

windowed analytical funstions are great, but they put troubles during optimization phase, because they introduce a disruption on relational theory and then its more dificult to optimize the overall SQL especially if its not possible to optimizer to put the the OLAP function is the last step.
btw for getting tha latest/greatest value, I prefere to use correlated subquery and agregation:

Select
T1.aa
, T2.bb
from
databasename.tablename T1
inner join
databasename.tablename1 T2
on T1.yy = T2.xx
WHERE
T1.DDDDD= 1
AND exists (
SELECT 1
FROM databasename.tablename T3
WHERE T1.aa = T3.aa
group by T3.aa
Having T1.zz = MAX( T3.zz )
)