Close

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

Posted by: AJ Welch

Like virtually all relational databases, Oracle allows queries to be generated that combine or JOIN rows from two or more tables to create the final result set. While there are numerous types of joins that can be performed, the most common are the INNER JOIN and the OUTER JOIN.

In this tutorial, we’ll briefly explore the difference between the INNER and OUTER JOIN and then examine the shorthand method Oracle provides for performing OUTER JOINS specifically using the + operator symbol.


What is an Inner Join?


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:

SELECT * FROM library.languages;

id   name
1   English
2   French
3   German
4   Mandarin
5   Spanish
6   Arabic
7   Japanese
8   Russian
9   Greek
10   Italian

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:

SELECT * FROM
  books
ORDER BY
  id
FETCH FIRST 10 ROWS ONLY;
id title    author    year_published    language_id
1   In Search of Lost Time  Marcel Proust   1913    2
2   Ulysses James Joyce 1922    1
3   Don Quixote Miguel de Cervantes 1605    5
4   Moby Dick   Herman Melville 1851    1
5   Hamlet  William Shakespeare 1601 (null)
6   War and Peace   Leo Tolstoy 1869    8
7   The Odyssey Homer   -700    9
8   The Great Gatsby    F. Scott Fitzgerald 1925    1
9   The Divine Comedy   Dante Alighieri     1472    10
10  Madame Bovary   Gustave Flaubert    1857    2

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.

SELECT
  b.id,
  b.title,
  b.author,
  b.year_published,
  l.name language
FROM
  books b
INNER JOIN
  library.languages l
ON
  b.language_id = l.id
ORDER BY
  b.id
FETCH FIRST 10 ROWS ONLY;
id title    author    year_published    language
1   In Search of Lost Time  Marcel Proust   1913    French
2   Ulysses James Joyce 1922    English
3   Don Quixote Miguel de Cervantes 1605    Spanish
4   Moby Dick   Herman Melville 1851    English
6   War and Peace   Leo Tolstoy 1869    Russian
7   The Odyssey Homer   -700    Greek
8   The Great Gatsby    F. Scott Fitzgerald 1925    English
9   The Divine Comedy   Dante Alighieri     1472    Italian
10  Madame Bovary   Gustave Flaubert    1857    French
11  The Brothers Karamazov  Fyodor Dostoyevsky  1880    Russian

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.

What is an Outer 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:

books b INNER JOIN library.languages l
…to this:

books b LEFT OUTER JOIN library.languages l

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

SELECT
  b.id,
  b.title,
  b.author,
  b.year_published,
  l.name language
FROM
  books b
LEFT OUTER JOIN
  library.languages l
ON
  b.language_id = l.id
ORDER BY
  b.id
FETCH FIRST 10 ROWS ONLY;
id title    author    year_published    language
1   In Search of Lost Time  Marcel Proust   1913    French
2   Ulysses James Joyce 1922    English
3   Don Quixote Miguel de Cervantes 1605    Spanish
4   Moby Dick   Herman Melville 1851    English
5   Hamlet  William Shakespeare 1601  (null)
6   War and Peace   Leo Tolstoy 1869    Russian
7   The Odyssey Homer   -700    Greek
8   The Great Gatsby    F. Scott Fitzgerald 1925    English
9   The Divine Comedy   Dante Alighieri     1472    Italian
10  Madame Bovary   Gustave Flaubert    1857    French

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.

Performing Outer Joins using the (+) symbol

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:

SELECT
  b.id,
  b.title,
  b.author,
  b.year_published,
  l.name language
FROM
  books b,
  library.languages l
WHERE
  l.id (+)= b.language_id
ORDER BY
  b.id
FETCH FIRST 10 ROWS ONLY;

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:

FROM
  books b,
  library.languages l
WHERE
  l.id (+)= b.language_id
…is effectively identical to this:

FROM
  library.languages l
RIGHT OUTER JOIN
  books b
ON
  b.language_id = l.id