Invalid Identifier ORA-00904 Error on Outer Join-CodePudding

I'm attempting a query in Oracle SQL 19c that uses an outer join:

create table R(
a int,
b int
);

create table S(
b int,
c int,
d int
);

insert into R values (1,2);
insert into R values (3,4);
insert into R values (5,6);
insert into S values (2,4,6);
insert into S values (4,6,8);
insert into S values (4,7,9);

SELECT R.a, R.b, S.b, S.c, S.d
FROM R OUTER JOIN S
ON (R.a > S.b AND R.b = S.c)

The query throws ORA-00904: "R"."B": invalid identifier and from what I gather, the error is thrown on the second R.B (i.e., in the ON clause). I understand this has something to do with scoping rules but I do not understand what is going on in the above query as there is no nesting.

CodePudding user response：

The problem - unintuitively - is that you are missing a LEFT (or RIGHT, or FULL) keyword before the OUTER JOIN. You need to say what type of outer join it is:

SELECT R.a, R.b, S.b, S.c, S.d
FROM R LEFT OUTER JOIN S
ON (R.a > S.b AND R.b = S.c);

You seem to be getting that error because without one of those keywords, the parser is interpreting the OUTER as an alias for the R table - which means that R.b would not be correct, but OUTER.b would be. But, that would then be an inner join...

fiddle