Answer

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

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?

    Requires Free Membership to View

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 first published in November 2013

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Expert Discussion

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest