Microsoft® SQL Server™ allows up to 256 tables to be joined in a join clause. This includes both temporary and permanent tables. There is no join limit in Oracle.
When using outer joins in Oracle, the outer join operator (+) is typically placed next to the child (foreign key) column in the join. The (+) identifies the column with fewer unique values. This always occurs unless the foreign key allows nulls, in which case the outer join operator (+) may be placed on the parent (PRIMARY KEY or UNIQUE constraint) column. You cannot place the outer join operator (+) on both sides of the equal sign (=).
It is recommended that Oracle out join clauses using the += and =+ operators be changed to use the following SQL-92 join syntax.
| Join operation | Description |
|---|---|
| CROSS JOIN | Specifies that the cross product of two tables be returned. This returns the same rows as if no WHERE clause was specified in an old-style join. This type of join is called a Cartesian join in Oracle. |
| INNER | Specifies that all inner rows be returned. Any unmatched rows are discarded. This is identical to a standard Oracle table join. |
| LEFT [OUTER] |
Specifies that all of the left table outer rows be returned, even if no column matches are found. This operates just like an Oracle outer join (+). |
| RIGHT [OUTER] |
Specifies that all of the right table outer rows be returned, even if no column matches are found. This operates just like an Oracle outer join (+). |
| FULL [OUTER] |
Specifies the row be included in the result set and its output columns that correspond to the other table be set to NULL if a row from either table does not match the selection criteria. This would be the same as placing the Oracle outer join operator on both sides of the equal sign (for example, col1(+) = col2(+)), which is not allowed. |
The code examples return listings of classes taken by all students. Outer joins are defined between the student and grade tables that allow all students to appear, even those who are not enrolled in any classes. Outer joins are also added to the class table in order to return the class names. If outer joins are not added to the class tables, those students who are not enrolled in any classes are not returned because they have null course codes (CCODE). The syntax differs between Oracle and SQL Server.
| Oracle | SQL Server |
|---|---|
| SELECT S.SSN AS SSN, FNAME, LNAME FROM STUDENT_ADMIN.STUDENT S, DEPT_ADMIN.CLASS C, STUDENT_ADMIN.GRADE G WHERE S.SSN = G.SSN(+) AND G.CCODE = C.CCODE(+) |
SELECT S.SSN AS SSN, FNAME, LNAME FROM STUDENT_ADMIN.GRADE G RIGHT OUTER JOIN STUDENT_ADMIN.STUDENT S ON G.SSN = S.SSN LEFT OUTER JOIN DEPT_ADMIN.CLASS C ON G.CCODE = C.CCODE |