When using (+), can you only use this once in a statement? For example when I run this query it gives an error: "a predicate may reference only one outer-joined table"
SELECT C.COURSE_NO, C.DESCRIPTION, S.SECTION_ID,
S.COURSE_NO, S.START_DATE_TIME
FROM COURSE C, SECTION S
WHERE C.COURSE_NO (+) = S.COURSE_NO (+);
Not "once in a statement", but "one in a relation".
Depending on are you using (+) can denotes a LEFT or RIGHT join. If you use in both sides you're trying somenthing like "left and right join", that is impossible.
You can use C.COURSE_NO (+) = S.COURSE_NO or C.COURSE_NO = S.COURSE_NO (+)
And you can use more than one in a complete statement, like this:
table1.id (+) = table2.id
and
table3.id = table4.id(+)
Related questions:
Oracle what does do in a where clause
Difference between Oracle's plus (+) notation and ansi JOIN notation?
The now ancient pre-ANSI standards Oracle syntax for an outer join doesn't support other join types like the full outer join you are trying to do with (+) on both sides.
It is supported to not break old code. You should not be using it for new code - use the ANSI SQL standard JOIN syntax.
SELECT C.COURSE_NO, C.DESCRIPTION, S.SECTION_ID, S.COURSE_NO, S.START_DATE_TIME
FROM COURSE C
FULL OUTER JOIN SECTION S ON C.COURSE_NO = S.COURSE_NO;
It sounds like you want to do a full outer join. That would look like this in standard ANSI:
SELECT C.COURSE_NO, C.DESCRIPTION, S.SECTION_ID,
S.COURSE_NO, S.START_DATE_TIME
FROM COURSE C
FULL OUTER JOIN SECTION S
ON C.COURSE_NO = S.COURSE_NO;
