Hi,
Can anybody help me how to explain this select on those 2 tables, I'm stuck with line #3, marked with "xxx", is it just for JOIN, no columns select but T2 brought into Select for further predicate evaluation?
This is piece of one example, where we have to to filter only those records from first table where all sub_item_cd exists in second table, ie.
item_cd
1010
1012
But it doesn't matter for this question, just trying to digest logic of this Select. I can see that even output for lines1,2,3 produce some cortesian product that I can't explain.
Tx all
T
select t1.item_cd ----- #1
from t1, ------#2
(select distinct sub_item_cd from t2 ) t2 --------#3/* xxx */
where t1.sub_item_cd = t2.sub_item_cd(+) --------#4
group by t1.item_cd --------#5
having max(decode( t2.sub_item_cd, NULL, 1, 0 )) = 0 ---------#6
T1::::
item_cd sub_item_cd
1010 A
1010 B
1010 C
1011 A
1011 D --/* D not in T2
1012 A
T2::::
sub_item_cd
A
A
A
B
B
C
B
C