Manage Learn to apply best practices and optimize your operations.

Oracle LEFT JOIN vs. LEFT OUTER JOIN: What's the difference?

Find the definition for different types of Oracle joins, and read a discussion on LEFT JOIN vs. LEFT OUTER JOIN in this expert Q/A.

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.

Cpflames also explains that the keywords LEFT, RIGHT or FULL is mandatory, as is JOIN. LEFT, RIGHT and FULL OUTER JOINS are the only types of 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:

Note: This tip is a compilation of advice from various experts on our site.

This was last published in November 2013

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

What’s the most confusing aspect of Oracle syntax?
The old Oracle's outer join syntax (+) was confusing when I started using it. Fortunately in version 9i the standard syntax was implemented.
Inner joins cannot be left or right, since they include only the rows that have a match in both tables, so I don't really see the need or benefit in including the OUTER keyword when writing LEFT, RIGHT or FULL outer joins.