Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table R(
a int,
b int
);
create table S(
b int,
c int,
d int
);
insert into R values (1,2);
1 rows affected

insert into R values (3,4);
1 rows affected

insert into R values (5,6);
1 rows affected

insert into S values (2,4,6);
1 rows affected

insert into S values (4,6,8);
1 rows affected

insert into S values (4,7,9);
1 rows affected

-- original
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);
ORA-00904: "R"."B": invalid identifier

-- 'corrected' to not error, but not what you intended
SELECT OUTER.a, OUTER.b, S.b, S.c, S.d
FROM R OUTER JOIN S
ON (OUTER.a > S.b AND OUTER.b = S.c);


A
B
B
C
D


3
4
2
4
6

5
6
4
6
8


-- corrected with LEFT keyword - could also be RIGHT or OUTER
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);


A
B
B
C
D


3
4
2
4
6

5
6
4
6
8

1
2
null
null
null

A	B	B	C	D
3	4	2	4	6
5	6	4	6	8
1	2	null	null	null