Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Select from from T1,T2

764712May 16 2011 — edited May 17 2011
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
This post has been answered by Frank Kulash on May 16 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 14 2011
Added on May 16 2011
7 comments
2,646 views