Left and right joins using the plus (+) sign in Oracle

An INNER JOIN in a relational database is simply the joining of two or more tables in which the result will only contain data which satisfied all join conditions.

For example, here we have a basic library schema with two tables: books and languages. The languages table is just a list of possible language names and a unique language id:

Meanwhile, our books table has a language_id row which for most, but not all, books simply contains the language_id associated with the original published language of the book:

In many cases, we may wish to perform an INNER JOIN of the books and languages tables so rather than viewing the meaningless language_id value of each book, we can actually see the language name instead.

What’s critical to note here is that our result set was slightly different in the above two queries. In the first, we simply listed the first 10 books, but in the INNER JOIN query we’re only returning results which meet all conditions from both tables. For this reason, the record of Hamlet (which has a language_id value of null or empty) is ignored and not returned in the result of our INNER JOIN.

Instead of exclusively returning results which satisfy all join conditions of an INNER JOIN, an OUTER JOIN returns not only results which satisfy all conditions, but also returns rows from one table which did not satisfy the condition. The table that is chosen for this “bypass” of conditional requirements is determined by the directionality or “side” of the join, typically referred to as LEFT or RIGHT outer joins.

When defining a side to your OUTER JOIN, you are specifying which table will always return its row even if the opposing table on the other side of the join has missing or null values as part of the joining condition.

Therefore, if we perform the same basic JOIN as above to retrieve books and language names, we know that our books table should always return data, so our JOIN side should “point toward” our books table, thereby making the languages table the OUTER table we’re attaching to it.

To accomplish this, we simply change:

Thus, the entire query and result set looks almost identical to the INNER JOIN except that minor alteration:

As expected, by using a LEFT OUTER JOIN instead of the previous INNER JOIN, we’re getting the best of both worlds: We’re not skipping any books records (such as Hamlet) simply because the language_id value is null for that record, yet for all records where language_id exists, we get the nicely formatted language name obtained from our languages table.

As indicated in the official documentation, Oracle provides a special outer join operator (the + symbol) that is shorthand for performing OUTER JOINS.

In practice, the + symbol is placed directly in the conditional statement and on the side of the optional table (the one which is allowed to contain empty or null values within the conditional).

Therefore, we can once again rewrite our above LEFT OUTER JOIN statement using the + operator like so:

The results are the same as the standard LEFT OUTER JOIN example above, so we won’t include them here. However, there’s one critical aspect to notice about the syntax using the + operator for OUTER JOINS.

The + operator must be on the left side of the conditional (left of the equals = sign). Therefore, in this case, because we want to ensure that our languages table is the optional table that can return null values during this comparison, we swapped the order of the tables in this conditional, so languages is on the left (and is optional) while books is on the right.

Lastly, because of this reordering of the table sides in the conditional when using the + operator, it’s important to realize that the above is simply shorthand for a RIGHT OUTER JOIN. This means that this snippet of the query: