Oracle JOIN USING + Subquery: ora-00904 string: invalid identifier

Question

im has a little syntax problem in my request (simplified):

select * from table1 t1 inner join table2 t2 using (pk1) inner join table3 t3 using (pk2) where not exists (select1 from table4 t4 where t4.pk1 = t1.pk1) 

Using the "using" keyword, oracle does not allow the table identifier before the column name (for example: t1.pk1, only pk1 can be used)

If I write:

 select * from table1 t1 inner join table2 t2 using (pk1) inner join table3 t3 using (pk2) where not exists (select1 from table4 t4 where t4.pk1 = pk1) 

This query will not return the expected results.

But since I use the "exist" subquery, how can I join this subquery?

Of course, I believe that I could write this query in a different way and avoid existence, or I could NOT use "use".

But is it possible to "combine / use" in conjunction with a subquery in the where clause?

Edit: using Oracle 10gR2

Answer 1

An interesting problem! The best I can use when using USING is:

 select * from ( select * from table1 t1 inner join table2 t2 using (pk1) inner join table3 t3 using (pk2) ) v where not exists (select1 from table4 t4 where t4.pk1 = v.pk1) 

Answer 2

You cannot use table classifiers with natural joins.

This request:

 select 1 from table4 t4 where t4.pk1 = pk1 

analyzed as

 select 1 from table4 t4 where t4.pk1 = t4.pk1 

and NOT EXISTS above it always returns false if there is only one record in table4 .

Just use explicit JOIN conditions:

 WITH table1 AS ( SELECT 1 AS pk1 FROM dual ), table2 AS ( SELECT 1 AS pk1, 1 AS pk2 FROM dual ), table3 AS ( SELECT 1 AS pk2 FROM dual ), table4 AS ( SELECT 2 AS pk1 FROM dual ) SELECT * FROM table1 t1 JOIN table2 t2 ON t2.pk1 = t1.pk1 JOIN table3 t3 ON t3.pk2 = t2.pk2 WHERE NOT EXISTS ( SELECT 1 FROM table4 t4 WHERE t4.pk1 = t1.pk1 )