michelangelus - Fotolia
Oracle LEFT JOIN vs. LEFT OUTER JOIN: What's the difference?
Get an explanation of the different types of Oracle joins, including whether there's really a difference between the LEFT JOIN and LEFT OUTER JOIN syntax in PL/SQL.
I've been doing some research on different kinds of Oracle joins, and it's unclear to me if there is a difference between LEFT JOIN and LEFT OUTER JOIN in SQL syntax. Is the word outer optional, because 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, as it seems clear that INNER goes only with JOIN to return all the rows that match in multiple tables, while OUTER goes with LEFT, RIGHT and FULL JOIN for queries that can also return unmatched rows. Are those two words used only to emphasize the nature of the join?
Oracle joins -- including the question of LEFT JOIN vs. LEFT OUTER JOIN -- can be a very confusing topic, especially for newcomers to Oracle databases. Let's define the relevant terms and explore other commonly asked questions about Oracle joins and the JOIN syntax in PL/SQL, the vendor's implementation of SQL.
What is a JOIN clause?
A JOIN clause combines rows of data from related columns in the tables that are set up in a relational database. JOIN clauses create narrower data sets that can be served up to database users as streamlined tables. Joins work by using the common values in each database table that's included in the JOIN clause to pull together the requested data.
What do Oracle joins do?
In Oracle and other relational databases, data elements are stored in multiple tables. For example, a manufacturer typically would store data on customers, products and orders in separate tables and use primary and foreign keys to document the relationships between data fields in the tables. But it's hard to track and analyze the data across different tables. Joins enable Oracle database administrators and other users to create a combined data set that's easier to use through a SELECT statement in PL/SQL.
How many different kinds of Oracle joins are there?
As in other SQL-based relational databases, Oracle supports both outer and inner joins. There are three types of outer joins -- LEFT, RIGHT and FULL. A LEFT JOIN contains all the records of the "left" table specified in the join command -- i.e., the one that's listed first -- plus any matching records in the "right" table.
A RIGHT JOIN reverses that: It returns all the records in the right table, plus the matching ones in the left table, if there are any. A FULL JOIN combines all the records in both the left and right tables, including the unmatched rows from each one.
Inner joins, on the other hand, focus solely on the commonality between two tables. When an INNER JOIN clause is used, there must be some matching data in the tables that are being joined to get results back from a query. The whole purpose of an inner join is to search those tables for data that meets the join conditions specified in the clause.
Outer joins return a set of rows that include what an inner join on the same tables would return. In addition, the inclusion of rows from one table that have no corresponding match in the other will produce NULL values in the unmatched columns. In a LEFT JOIN, for example, there will be a NULL entry in each field from the right table that doesn't match one in the left table. In a nutshell, this means the LEFT JOIN will return all of the values from the left table and the matched values from the right table, plus NULL fields where there are no matching values in the right table.
What's the difference between LEFT JOIN and LEFT OUTER JOIN?
There really is no difference between a LEFT JOIN and a LEFT OUTER JOIN. Both versions of the syntax will produce the exact same result in PL/SQL. Some people do recommend including outer in a LEFT JOIN clause so it's clear that you're creating an outer join, but that's entirely optional. The same is true of a RIGHT JOIN and a RIGHT OUTER JOIN. In the end, then, it isn't a question of a LEFT JOIN vs. LEFT OUTER JOIN. They're one and the same thing.
