0

Please have a look at this SQL fiddle with this select statement:

select T1.VAL, ttemp.*
from T1, 
  (select rownum, t.* from (select * from T2 where t2.val = t1.val ) t) ttemp
where t1.val = ttemp.val
and t1.val > 2

Here I want to join the outer most table with inner-level-2 table and so on replacing t2.val > 2 with t2.val = t1.val results in

ORA-00904: "T1"."VAL": invalid identifier

I believe not adding the where or join clause for select * from T2 will result in full table scan. Won't it?

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
bjan
  • 2,000
  • 7
  • 32
  • 64
  • Maybe you should try it :) Get the query plan printed out and you can be sure (do make sure there's plenty of data inside, many RDBMs don't use indices etc. if there's too little data). On MSSQL, I believe it wouldn't, but since you're on Oracle, I can't tell. Also, stop using implicit joins (`from A, B where A.Id = B.Id`). It's a technique that's been obsolete for over twenty years now :) `left join B on B.Id = A.Id` is so much more maintainable :) – Luaan Mar 28 '14 at 07:28
  • Your query seems rather odd, and I think would benefit from using Common Table Expressions or simply rethinking the logic. I think your logic can be simplified quite a lot – Allan S. Hansen Mar 28 '14 at 07:30
  • You can't refer to sibling table in your subquery. Maybe it is possible to achieve what you want using `row_number over partition by` analytic function. – Hamlet Hakobyan Mar 28 '14 at 07:36
  • You have two data sets you select from: T1 and ttemp. You can combine them using join criteria in an ON clause or the WHERE clause. But you can not use T1 values in the ttemp query, because T1 is not known to ttemp. They are separate data sets before you join them. You will have to re-write your statement completely. What do you want to achieve? Your query looks somewhat complicated and strange. – Thorsten Kettner Mar 28 '14 at 07:37

1 Answers1

0

Maybe you can use this:

SELECT rownum, t1.val, t2.NAME
FROM T1 t1
LEFT JOIN T2 t2 ON (t1.VAL = t2.VAL)
WHERE t1.VAL > 2
villanueva.ricardo
  • 195
  • 1
  • 10