The jOOQ User Manual SQL building Table expressions Joined tables OUTER JOIN

previous : next

OUTER JOIN

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

OUTER JOIN allows for producing some additional rows when an INNER JOIN does not match. There are 3 types of OUTER JOIN:

• LEFT JOIN or LEFT OUTER JOIN: Always produce all rows from the left side of the join, and only matched rows from the right side of the join
• RIGHT JOIN or RIGHT OUTER JOIN: Always produce all rows from the right side of the join, and only matched rows from the left side of the join
• FULL JOIN or FULL OUTER JOIN: Always produce all rows from both left and right side of the join

The OUTER keyword is optional both in SQL and in jOOQ, and does not affect the query semantics at all.

This is best explained by example.

LEFT JOIN

LEFT JOIN is the most popular among the OUTER JOIN types.

The following query produces all authors, and possibly, their books:

SELECT
  AUTHOR.FIRST_NAME,
  AUTHOR.LAST_NAME,
  BOOK.TITLE
FROM AUTHOR
LEFT JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
 

create.select(
         AUTHOR.FIRST_NAME,
         AUTHOR.LAST_NAME,
         BOOK.TITLE)
      .from(AUTHOR)
      .leftJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .fetch();

The result might look like this:

+------------+-----------+--------------+
| FIRST_NAME | LAST_NAME | TITLE        |
+------------+-----------+--------------+
| George     | Orwell    | 1984         |
| George     | Orwell    | Animal Farm  |
| Paulo      | Coelho    | O Alquimista |
| Paulo      | Coelho    | Brida        | <-- Above rows are also produced by INNER JOIN
| Jane       | Austen    |              | <-- This row is only produced by LEFT JOIN or FULL JOIN
+------------+-----------+--------------+

As can be seen, all rows from the left side of the join (authors) are produced, including the ones that do not have any matches on the right side of the join (books). We don't have any books for Jane Austen yet, but Jane Austen is in the result set. She wouldn't be if this were an INNER JOIN.

RIGHT JOIN

RIGHT JOIN is just the inverse of a LEFT JOIN, and is hardly ever used.

The following query produces all books, and possibly, their authors:

SELECT
  AUTHOR.FIRST_NAME,
  AUTHOR.LAST_NAME,
  BOOK.TITLE
FROM AUTHOR
RIGHT JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
 

create.select(
         AUTHOR.FIRST_NAME,
         AUTHOR.LAST_NAME,
         BOOK.TITLE)
      .from(AUTHOR)
      .rightJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .fetch();

The result might look like this:

+------------+-----------+--------------------+
| FIRST_NAME | LAST_NAME | TITLE              |
+------------+-----------+--------------------+
| George     | Orwell    | 1984               |
| George     | Orwell    | Animal Farm        |
| Paulo      | Coelho    | O Alquimista       |
| Paulo      | Coelho    | Brida              | <-- Above rows are also produced by INNER JOIN
|            |           | The Arabian Nights | <-- This row is only produced by RIGHT JOIN or FULL JOIN
+------------+-----------+--------------------+

As can be seen, all rows from the right side of the join (books) are produced, including the ones that do not have any matches on the left side of the join (authors). The Arabian Night does not have a specific author, but it is still in the result set. It wouldn't be if this were an INNER JOIN.

Not that a RIGHT JOIN is just an inversed LEFT JOIN, and you would be much more likely to write the same query like this, with no semantic difference:

SELECT
  AUTHOR.FIRST_NAME,
  AUTHOR.LAST_NAME,
  BOOK.TITLE
FROM BOOK
LEFT JOIN AUTHOR ON BOOK.AUTHOR_ID = AUTHOR.ID
 

create.select(
         AUTHOR.FIRST_NAME,
         AUTHOR.LAST_NAME,
         BOOK.TITLE)
      .from(BOOK)
      .leftJoin(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .fetch();

There are complex join trees where a RIGHT JOIN may make things simpler, but in most cases, it only complicates readability and maintainability of your query.

FULL JOIN

FULL JOIN is an occasionally useful way to join two tables when no rows from either table should be omitted. This can be useful e.g. to compare two data sets.

The following query produces all authors and all books:

SELECT
  AUTHOR.FIRST_NAME,
  AUTHOR.LAST_NAME,
  BOOK.TITLE
FROM AUTHOR
FULL JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
 

create.select(
         AUTHOR.FIRST_NAME,
         AUTHOR.LAST_NAME,
         BOOK.TITLE)
      .from(AUTHOR)
      .fullJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .fetch();

The result might look like this:

+------------+-----------+--------------------+
| FIRST_NAME | LAST_NAME | TITLE              |
+------------+-----------+--------------------+
| George     | Orwell    | 1984               |
| George     | Orwell    | Animal Farm        |
| Paulo      | Coelho    | O Alquimista       |
| Paulo      | Coelho    | Brida              | <-- Above rows are also produced by INNER JOIN
| Jane       | Austen    |                    | <-- This row is only produced by LEFT JOIN or FULL JOIN
|            |           | The Arabian Nights | <-- This row is only produced by RIGHT JOIN or FULL JOIN
+------------+-----------+--------------------+

As can be seen, all rows from the left side of the join (authors) as well as from the right side of the join (books) are produced, including the ones that do not have any matches on the respective other side of the join.

Dialect support

This example using jOOQ:

select(BOOK.ID, AUTHOR.ID).from(BOOK.leftJoin(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))

Translates to the following dialect specific expressions:

All dialects

SELECT BOOK.ID, AUTHOR.ID
FROM BOOK
  LEFT OUTER JOIN AUTHOR
    ON BOOK.AUTHOR_ID = AUTHOR.ID

The jOOQ User Manual SQL building Table expressions Joined tables OUTER JOIN

previous : next