Oracle JOIN USING +子查询:ora-00904字符串:无效的标识符 [英] Oracle JOIN USING + Subquery : ora-00904 string: invalid identifier
问题描述
我的查询中有一个小语法问题(简体):
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)
通过使用"using"关键字,oracle不允许在列名前面使用表标识符(例如:t1.pk1,只能使用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)
如果我写:
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 ?
当然,我想我可以用另一种方式编写此查询并避免存在该查询,或者我不能使用"using".
Of course, i suppose i could write this query another way and avoid the exists, or i could NOT use "using".
但是在where子句中是否可以将"join/using"与子查询结合使用?
But is it possible to have "join / using" combined with a subquery in the where clause ?
使用Oracle 10gR2
Edit : using Oracle 10gR2
推荐答案
有趣的问题!在仍然使用USING的情况下,我能管理的最好的方法是:
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)
这篇关于Oracle JOIN USING +子查询:ora-00904字符串:无效的标识符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!