I've been doing some research on different kinds of Oracle joins, and it is unclear to me if there is a difference between LEFT JOIN and LEFT OUTER JOIN. Is the word outer optional, since a LEFT JOIN in Oracle is an OUTER JOIN by default? If this is the case, I don't understand the usage of "INNER" and "OUTER," since it would be clear that INNER goes only with JOIN or FULL JOIN (same if inner, right?) as resulting in all that matches in two tables. And OUTER goes with LEFT, RIGHT and FULL. Are those keywords used only to emphasize the nature of the JOIN?
Oracle joins, including the LEFT JOIN vs. LEFT OUTER JOIN, can be very confusing topics, especially for Oracle newbies. Let's define each of these and explore other commonly asked questions about Oracle joins.
According to this SearchOracle expert Q/A, there are three types of OUTER JOINS in Oracle -- LEFT, RIGHT and FULL. A LEFT OUTER JOIN contains all records of the "left" table even if it has no matches in the "right" table specified in the join. A RIGHT OUTER JOIN contains all records in the "right" table even if it has no matches in the "left" table. A FULL OUTER JOIN contains all records of both the left and right tables.
As former SearchOracle expert Karen Morton says, an OUTER JOIN extends the result of a simple join by returning all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
Community member cpflames explains that the keyword OUTER is optional, but he says it really should be mandatory. He recommends always including the word OUTER with OUTER JOINS in Oracle to remind yourself that it's an outer join.
According to cpflames, INNER JOIN means all result rows are rows that were produced by matching some condition between the two tables. An OUTER JOIN has result rows where sometimes there isn't a match, yet rows from one table or the other or both are returned without a match. There is one additional type of join besides inner and outer, and that's the CROSS JOIN.
Read more about Oracle joins here:
- Find out how a FULL OUTER JOIN was made possible in Oracle 10g.
- Expert Rudy Limeback explains how to get the same result as a FULL OUTER JOIN without actually using the FULL OUTER JOIN.
- Learn how to use a FULL OUTER JOIN in a many-to-many relationship.
- Find out how to use an OUTER JOIN when working with three or more tables.
Note: This tip is a compilation of advice from various experts on our site.
This was first published in November 2013