Q

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.
Related Discussions

Lauren Soucy asks:

What’s the most confusing aspect of Oracle syntax?

1  Response So Far

Join the Discussion

1 comment

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close