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

Question

Hi,

i m having a little syntax problem in my query (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)

By using the "using" keyword, oracle doesnt allow table identifier in front of the column name (eg : 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 give the expected results.

But since i am using an "exists" subquery, how can i join this subquery ?

Of course, i suppose i could write this query another way and avoid the exists, or i could NOT use "using".

But is it possible to have "join / using" combined with a subquery in the where clause ?

Edit : using Oracle 10gR2

Answer 1

You cannot use the table qualifiers with natural joins.

This query:

select 1 from table4 t4 where t4.pk1 = pk1

is being parsed as

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

and NOT EXISTS over it always returns false if there is but a single 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
        )

Answer 2

Interesting problem! The best I can manage while still 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)