Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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
orLEFT 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
orRIGHT 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
orFULL 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
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!