Chapter 6: Tackling Different Kinds of JOINs
The SQL JOIN clause represents one of the most used SQL features. From the well-known INNER and OUTER JOIN clauses, the fictional Semi and Anti Join, to the fancy LATERAL join, this chapter is a comprehensive set of examples meant to help you practice a wide range of JOIN clauses via the jOOQ DSL API.
The topics of this chapter include the following:
- Practicing the most popular types of JOINs (CROSS, INNER, and OUTER)
- The SQL USING and jOOQ onKey() shortcuts
- Practicing more types of JOINs (Implicit, Self, NATURAL, STRAIGHT, Semi, Anti, and LATERAL)
Let's get started!
Technical requirements
The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter06.
Practicing the most popular types of JOINs
By most popular types of JOIN statements we are referring to CROSS JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. Let's tackle each of them via the jOOQ DSL API, starting with the most basic type of JOIN.
CROSS JOIN
CROSS JOIN is the most basic type of JOIN that gets materialized in a Cartesian product. Having two tables, A and B, the CROSS JOIN operation between them is represented as A x B, and practically, it means the combination of every row from A with every row from B.
In jOOQ, CROSS JOIN can be rendered by enlisting the tables in the FROM clause (non-ANSI JOIN syntax) or via the crossJoin() method that renders the CROSS JOIN keywords (ANSI JOIN syntax). Here is the first case – let's CROSS JOIN the OFFICE and DEPARTMENT tables:
ctx.select().from(OFFICE, DEPARTMENT).fetch();
Since this query doesn't expose explicitly or clearly, its intention of using CROSS JOIN is not as friendly as the following one, which uses the jOOQ crossJoin() method:
ctx.select().from(OFFICE).crossJoin(DEPARTMENT).fetch();
Using the crossJoin() method renders the CROSS JOIN keywords (ANSI JOIN syntax), which clearly communicate our intentions and remove any potential confusion:
SELECT `classicmodels`.`office`.`office_code`,
`classicmodels`.`office`.`city`,
...
`classicmodels`.`department`.`department_id`,
`classicmodels`.`department`.`name`,
...
FROM `classicmodels`.`office`
CROSS JOIN `classicmodels`.`department`
Since some offices have NULL values for CITY and/or COUNTRY columns, we can easily exclude them from the OFFICE x DEPARTMENT via a predicate. Moreover, just for fun, we may prefer to concatenate the results as city, country: department (for example, San Francisco, USA: Advertising):
ctx.select(concat(OFFICE.CITY, inline(", "), OFFICE.COUNTRY, inline(": "), DEPARTMENT.NAME).as("offices")).from(OFFICE).crossJoin(DEPARTMENT)
.where(row(OFFICE.CITY, OFFICE.COUNTRY).isNotNull())
.fetch();
Basically, once we've added a predicate, this becomes INNER JOIN, as discussed in the following section. More examples are available in the bundled code as CrossJoin.
INNER JOIN
INNER JOIN (or simply JOIN) represents a Cartesian product filtered by some predicate commonly placed in the ON clause. So, with the A and B tables, INNERJOIN returns the rows of A x B that validate the specified predicate.
In jOOQ, we render INNER JOIN via innerJoin() (or simply join(), if omitting INNER is supported by your database vendor) and the on() methods. Here is an example that applies INNER JOIN between EMPLOYEE and OFFICE to fetch employee names and the cities of their offices:
ctx.select(EMPLOYEE.FIRST_NAME,
EMPLOYEE.LAST_NAME, OFFICE.CITY)
.from(EMPLOYEE)
.innerJoin(OFFICE)
.on(EMPLOYEE.OFFICE_CODE.eq(OFFICE.OFFICE_CODE))
.fetch();
The rendered SQL for the MySQL dialect is as follows:
SELECT `classicmodels`.`employee`.`first_name`,
`classicmodels`.`employee`.`last_name`,
`classicmodels`.`office`.`city`
FROM `classicmodels`.`employee`
JOIN `classicmodels`.`office` ON
`classicmodels`.`employee`.'office_code'
= `classicmodels`.`office`.`office_code`
By default, jOOQ doesn't render the optional INNER keyword. But, you can alter this default via the withRenderOptionalInnerKeyword() setting and the argument RenderOptionalKeyword.ON.
In jOOQ, chaining multiple JOINs is quite easy. For example, fetching the managers and their offices requires two INNER JOIN clauses, since between MANAGER and OFFICE, we have a many-to-many relationship mapped by the MANAGER_HAS_OFFICE junction table:
ctx.select()
.from(MANAGER)
.innerJoin(OFFICE_HAS_MANAGER)
.on(MANAGER.MANAGER_ID
.eq(OFFICE_HAS_MANAGER.MANAGERS_MANAGER_ID))
.innerJoin(OFFICE)
.on(OFFICE.OFFICE_CODE
.eq(OFFICE_HAS_MANAGER.OFFICES_OFFICE_CODE))
.fetch();
In these examples, we called the jOOQ join method on org.jooq.SelectFromStep and the rendered SQL for PostgreSQL dialect is:
…
FROM
"public"."manager"
JOIN "public"."office_has_manager"
ON "public"."manager"."manager_id" =
"public"."office_has_manager"."managers_manager_id"
JOIN "public"."office"
ON "public"."office"."office_code" =
"public"."office_has_manager"."offices_office_code"
But, for convenience, we can call the join method directly after the FROM clause on org.jooq.Table. In such case, we obtain a nested fluent code as below (feel free to use the approach that you find most convenient):
ctx.select()
.from(MANAGER
.innerJoin(OFFICE_HAS_MANAGER
.innerJoin(OFFICE)
.on(OFFICE.OFFICE_CODE.eq(
OFFICE_HAS_MANAGER.OFFICES_OFFICE_CODE)))
.on(MANAGER.MANAGER_ID.eq(
OFFICE_HAS_MANAGER.MANAGERS_MANAGER_ID)))
.fetch();
The rendered SQL for the PostgreSQL dialect is as follows:
…
FROM
"public"."manager"
JOIN
(
"public"."office_has_manager"
JOIN "public"."office"
ON "public"."office"."office_code" =
"public"."office_has_manager"."offices_office_code"
) ON "public"."manager"."manager_id" =
"public"."office_has_manager"."managers_manager_id"
Next, let's talk about OUTER JOIN.
OUTER JOIN
While INNER JOIN returns only the combinations that pass the ON predicate, OUTER JOIN will also fetch rows that have no match on the left-hand side (LEFT [OUTER] JOIN) or right-hand side (RIGHT [OUTER] JOIN) of the join operation. Of course, we have to mention here FULL [OUTER] JOIN as well. This fetches all rows from both sides of the join operation.
The jOOQ API renders OUTER JOIN via leftOuterJoin(), rightOuterJoin(), and fullOuterJoin(). Since the OUTER keyword is optional, we can omit it via the analogs, leftJoin(), rightJoin(), and fullJoin().
For example, let's fetch all employees (on the left-hand side) and their sales (on the right-hand side). By using LEFT [OUTER] JOIN, we retain all employees, even if they have no sales:
ctx.select(EMPLOYEE.FIRST_NAME,
EMPLOYEE.LAST_NAME, SALE.SALE_)
.from(EMPLOYEE)
.leftOuterJoin(SALE)
.on(EMPLOYEE.EMPLOYEE_NUMBER.eq(SALE.EMPLOYEE_NUMBER))
.fetch();
If we want to retain only the employees that have no sales, then we can rely on an exclusive LEFT [OUTER] JOIN by adding a WHERE clause that excludes all matches:
ctx.select(EMPLOYEE.FIRST_NAME,
EMPLOYEE.LAST_NAME, SALE.SALE_)
.from(EMPLOYEE)
.leftOuterJoin(SALE)
.on(EMPLOYEE.EMPLOYEE_NUMBER.eq(SALE.EMPLOYEE_NUMBER))
.where(SALE.EMPLOYEE_NUMBER.isNull())
.fetch();
The rendered SQL for the SQL Server dialect is as follows:
SELECT
[classicmodels].[dbo].[employee].[first_name],
[classicmodels].[dbo].[employee].[last_name],
[classicmodels].[dbo].[sale].[sale]
FROM
[classicmodels].[dbo].[employee]
LEFT OUTER JOIN
[classicmodels].[dbo].[sale]
ON [classicmodels].[dbo].[employee].[employee_number] =
[classicmodels].[dbo].[sale].[employee_number]
WHERE [classicmodels].[dbo].[sale].[employee_number] IS NULL
If you prefer to use the Oracle (+) symbol shorthand for performing OUTER JOIN then check this example of an LEFT [OUTER] JOIN:
ctx.select(EMPLOYEE.FIRST_NAME,
EMPLOYEE.LAST_NAME, SALE.SALE_)
.from(EMPLOYEE, SALE)
.where(SALE.EMPLOYEE_NUMBER.plus()
.eq(EMPLOYEE.EMPLOYEE_NUMBER))
.fetch();
And, the Oracle SQL is:
SELECT
"CLASSICMODELS"."EMPLOYEE"."FIRST_NAME",
"CLASSICMODELS"."EMPLOYEE"."LAST_NAME",
"CLASSICMODELS"."SALE"."SALE"
FROM
"CLASSICMODELS"."EMPLOYEE",
"CLASSICMODELS"."SALE"
WHERE
"CLASSICMODELS"."SALE"."EMPLOYEE_NUMBER"(+) =
"CLASSICMODELS"."EMPLOYEE"."EMPLOYEE_NUMBER"
By default, jOOQ render the optional OUTER keyword for both, leftOuterJoin() and leftJoin(). Alter this default via the withRenderOptionalOuterKeyword() setting and the argument RenderOptionalKeyword.ON.
In the bundled code, you can practice more examples, including RIGHT/FULL [OUTER] JOIN. For MySQL, which doesn't support FULL [OUTER] JOIN, we wrote some emulation code based on the UNION clause.
Important Note
A special case of OUTER JOIN is represented by Oracle's partitioned OUTER JOIN.
PARTITIONED OUTER JOIN
A special case of OUTER JOIN is represented by the Oracle's partitioned OUTER JOIN. Such a join represents an extension of the classical OUTER JOIN syntax and is applied to each logical partition defined via an expression in the PARTITION BY clause. A partitioned OUTER JOIN returns a UNION of the outer joins of each of the partitions in the partitioned table (logically partitions) with the table on the other side of the join.
Partitioned outer joins are specific to Oracle and they allow us to do the same "densifying" (fill gaps in sparse data) of data using a quite convenient syntax and an efficient Execution Plan.
A classical scenario where the Oracle's partitioned OUTER JOIN can be used sounds like this: write a query returning the sales of every employee (Sales Representative) in every fiscal year while taking into account that some employees had no sales in some years - fill gaps in sparse data with 0. For instance, if we try to see the sales of all employees (Sales Representative) grouped by fiscal year via a trivial JOIN then we obtain some gaps in data as in the following figure:
Figure 6.1 – Fill gaps in sparse data
In figure (a) is what we can easily get from a trivial JOIN, while in figure (b) is what we plan to get. So, we want to see all the Sales Representative even if they don’t have sales in certain years. This is a job for Oracle partitioned OUTER JOIN where the logical partition is FISCAL_YEAR:
ctx.select(SALE.FISCAL_YEAR,
EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME,
sum(nvl(SALE.SALE_, 0.0d)).as("SALES")) .from(EMPLOYEE)
.leftOuterJoin(SALE).partitionBy(SALE.FISCAL_YEAR)
.on(EMPLOYEE.EMPLOYEE_NUMBER.eq(SALE.EMPLOYEE_NUMBER))
.where(EMPLOYEE.JOB_TITLE.eq("Sales Rep")) .groupBy(SALE.FISCAL_YEAR,
EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME)
.orderBy(1, 2)
.fetch();
Of course, you can express/emulate this query without partitioned OUTER JOIN, but for this you have to check out the application PartitionedOuterJoin.
The SQL USING and jOOQ onKey() shortcuts
So far, we've covered the typical JOINs that are commonly used in daily work. Before we continue with more types of JOINs, let's introduce two convenient shortcuts that are useful for expressing more concise JOINs.
SQL JOIN … USING
In certain cases, the SQL JOIN … USING clause can be a convenient alternative to the classical JOIN … ON clause. Instead of specifying a condition in the JOIN … ON clause, we enlist the JOIN … USING clause in the set of fields (columns) whose names are common to both tables – the left-hand side table and right-hand side table of a JOIN operation. In jOOQ, the USING clause is rendered via the using() method, as shown in the following example. The EMPLOYEE_NUMBER column mentioned in using() is the primary key of the EMPLOYEE table and the foreign key of the SALE table:
ctx.select(EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME,
SALE.SALE_)
.from(EMPLOYEE)
.innerJoin(SALE)
.using(EMPLOYEE.EMPLOYEE_NUMBER)
.fetch();
So, using(EMPLOYEE.EMPLOYEE_NUMBER) is a less verbose representation of on(EMPLOYEE.EMPLOYEE_NUMBER.eq(SALE.EMPLOYEE_NUMBER), and the rendered SQL for the MySQL dialect is as follows:
SELECT `classicmodels`.`employee`.`first_name`,
`classicmodels`.`employee`.`last_name`,
`classicmodels`.`sale`.`sale`
FROM `classicmodels`.`employee`
JOIN `classicmodels`.`sale` USING (`employee_number`)
But we can use any other field(s). Here is the USING clause for a composite primary key:
...using(PRODUCTLINE.PRODUCT_LINE, PRODUCTLINE.CODE)
Alternatively, this is a USING clause for two fields that are not primary/foreign keys:
.using(OFFICE.CITY, OFFICE.COUNTRY)
Note that using() without arguments will render ON TRUE, so no filter is applied to the join operation. Practice the complete examples via the JoinUsing bundled application. Next, let's introduce a very handy tool from jOOQ named onKey().
However, as I said, USING fits only for certain cases. Lukas Eder enforces this statement: "The USING clause leads to a bit more difficult to maintain queries when queries get complex, so it's generally not recommended. It's less type-safe (in jOOQ). When you rename a column, your jOOQ code might still compile. It wouldn't if you had been using ON. When you add a column that accidentally matches a column referenced from USING, you might get unintended consequences in unrelated queries. Example, A JOIN B USING (X) JOIN C USING (Y). This assumes A(X), B(X, Y), C(Y). So, what happens if you add A(Y)? A runtime exception, because Y is now ambiguous. Or, even worse: What happens if you add A(Y) but remove B(Y)? No runtime exception, but possibly (and quietly) wrong query. Moreover, in Oracle, columns referenced from USING can no longer be qualified in the query. In conclusion, USING can be useful for quick and dirty ad-hoc querying, just like NATURAL. But I wouldn't use it in production queries. Especially, because implicit joins work much better in jOOQ.
The essence here is always the fact (and this is frequently misunderstood) that joins are *binary* operators between two tables. For instance, A JOIN B USING (X) JOIN C USING (Y) is just short for (A JOIN B USING (X)) JOIN C USING (Y), so C is joined to (A JOIN B USING (X)) not to B alone. This is also the case for onKey()."
jOOQ onKey()
Whenever we join a well-known foreign key relationship, we can rely on the jOOQ onKey() method. Since this is quite easy to understand for a simple foreign key, let's pick up a composite foreign key containing two fields. Check out the following ON clause:
ctx.select(...)
.from(PAYMENT)
.innerJoin(BANK_TRANSACTION)
.on(PAYMENT.CUSTOMER_NUMBER.eq(
BANK_TRANSACTION.CUSTOMER_NUMBER)
.and(PAYMENT.CHECK_NUMBER.eq(
BANK_TRANSACTION.CHECK_NUMBER)))
The (CUSTOMER_NUMBER, CHECK_NUMBER) represents a composite foreign key in the BANK_TRANSACTION table. jOOQ allows us to replace this verbose ON clause with the onKey() method without arguments, as follows:
ctx.select(...)
.from(PAYMENT)
.innerJoin(BANK_TRANSACTION)
.onKey()
.fetch();
Really cool, isn't it? jOOQ infers the ON condition on our behalf, and the rendered SQL for MySQL is as follows:
SELECT ...
FROM `classicmodels`.`payment`
JOIN `classicmodels`.`bank_transaction`
ON (`classicmodels`.`bank_transaction`.`customer_number`
= `classicmodels`.`payment`.`customer_number`
AND `classicmodels`.`bank_transaction`.`check_number`
= `classicmodels`.`payment`.`check_number`)
In case of ambiguity caused by multiple keys' potential matches, we can also rely on foreign keys' field references via onKey(TableField<?,?>... tfs), or the generated foreign keys' references via onKey(ForeignKey<?,?> fk). For instance, in order to avoid the DataAccessException: Key ambiguous between tables X and Y exception, while joining table X with table Y via onKey(), we can explicitly indicate the foreign key that should be used as follows (here, via the SQL Server generated foreign key reference, jooq.generated.Keys.PRODUCTLINEDETAIL_PRODUCTLINE_FK):
ctx.select(…)
.from(PRODUCTLINE)
.innerJoin(PRODUCTLINEDETAIL)
.onKey(PRODUCTLINEDETAIL_PRODUCTLINE_FK)
.fetch();
This time, the rendered SQL is as follows:
SELECT ...
FROM [classicmodels].[dbo].[productline]
JOIN
[classicmodels].[dbo].[productlinedetail]
ON
([classicmodels].[dbo].[productlinedetail].[product_line] =
[classicmodels].[dbo].[productline].[product_line]
AND
[classicmodels].[dbo].[productlinedetail].[code] =
[classicmodels].[dbo].[productline].[code])
But despite its appeal, this method can lead into issues. As Lukas Eder shared here: "The onKey() method is not type-safe, and can break in subtle ways, when tables are modified."
More examples are available in the application named JoinOnKey. For now, let's continue with more types of JOINs.
Practicing more types of JOINs
Next, let's cover more JOINs, such as Implicit/Self Joins, NATURAL JOIN, STRAIGHT JOIN, Semi/Anti Joins, and LATERAL Joins. Let's continue with Implicit/Self Joins.
Implicit and Self Join
Implicit and Self Joins can be easily expressed in jOOQ via type-safe navigation methods produced by the jOOQ generator in classes that mirror the database tables. Let's dissect this aspect of Implicit Joins.
Implicit Join
As an example, an explicit join that fetches a parent table's column from a given child table can be expressed as an Implicit Join. Here is the explicit join:
SELECT o.office_code, e.first_name, e.last_name
FROM employee AS e
JOIN office AS o ON e.office_code = o.office_code
Here is the less verbose Implicit Join version:
SELECT e.office.office_code, e.first_name, e.last_name
FROM employee AS e
If we check the generated Java-based schema, then we notice that the jooq.generated.tables.Employee class mirroring the EMPLOYEE table contains a method named office() especially for expressing this syntax. Here is the previous Implicit Join, written via the jOOQ DSL API:
ctx.select(EMPLOYEE.office().OFFICE_CODE,
EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME)
.from(EMPLOYEE)
.fetch();
Here is another example that chains several navigation methods to express an Implicit Join, starting from the ORDERDETAIL table:
ctx.select(
ORDERDETAIL.order().customer().employee().OFFICE_CODE,
ORDERDETAIL.order().customer().CUSTOMER_NAME,
ORDERDETAIL.order().SHIPPED_DATE,
ORDERDETAIL.order().STATUS,
ORDERDETAIL.QUANTITY_ORDERED, ORDERDETAIL.PRICE_EACH)
.from(ORDERDETAIL)
.orderBy(ORDERDETAIL.order().customer().CUSTOMER_NAME)
.fetch();
The names of these navigation methods correspond to the parent table name. Here is another example of writing an Implicit Join in a m:n relationship. If we think to an m:n relationship from the relationship table then we see two to-one relationships that we exploit as follows (between MANAGER and OFFICE there is a many-to-many relationship):
ctx.select(OFFICE_HAS_MANAGER.manager().fields())
.from(OFFICE_HAS_MANAGER)
.where(OFFICE_HAS_MANAGER.office().OFFICE_CODE.eq("6")).fetch();
Notice that the Implicit Joins covered in this section are foreign key path-based. Most probably, you are also familiar with Implicit Joins where you enlist all the tables you want to fetch data from in the FROM clause followed by the WHERE clause having conditions based on primary/foreign keys values for filtering the result. Here is an example of jOOQ code for such an Implicit Join:
ctx.select(OFFICE.OFFICE_CODE,
EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME)
.from(OFFICE, EMPLOYEE)
.where(OFFICE.OFFICE_CODE.eq(EMPLOYEE.OFFICE_CODE))
.orderBy(OFFICE.OFFICE_CODE)
.fetch();
Note
Nevertheless, note that these kind of Implicit Joins are quite prone to human mistakes, and it is better to rely on the ANSI JOIN syntax by explicitly using the JOIN keyword. Let me take advantage of this context to say that whenever you have old code that should be updated to an ANSI JOIN, you can rely on jOOQ. Besides the jOOQ DSL API, you can check out https://www.jooq.org/translate, and for a quick and neat guide, read this article: https://blog.jooq.org/2020/11/17/automatically-transform-oracle-style-implicit-joins-to-ansi-join-using-jooq/.
In the absence of explicit foreign keys in the schema for whatever reasons (including the tables are actually views), users of the commercial editions can specify synthetic foreign keys to the Code Generator as you can see in Chapter 11, jOOQ keys.
Please, consider the jOOQ manual and https://github.com/jOOQ/jOOQ/issues/12037 for covering the limitations of Implicit Joins support. Leaving the context of Implicit Joins, the jOOQ navigation methods are useful for expressing Self Joins as well.
Self Join
Whenever a table is joined with itself, we can rely on Self Joins. Writing a Self Join is done via a navigation method that has the same name as the table itself. For example, here is a Self Join that fetches a result set containing the name of each employee and the name of their boss (EMPLOYEE.REPORTS_TO):
ctx.select(concat(EMPLOYEE.FIRST_NAME, inline(" "), EMPLOYEE.LAST_NAME).as("employee"), concat(EMPLOYEE.employee().FIRST_NAME, inline(" "), EMPLOYEE.employee().LAST_NAME).as("reports_to")).from(EMPLOYEE)
.fetch();
In the bundled code, ImplicitAndSelfJoin, you can practice more examples with implicit and Self Joins.
NATURAL JOIN
Earlier, we used the JOIN … USING syntax by enlisting the fields whose names are common to both tables (the left and right tables of a join operation) and should be rendered in the condition of the ON clause. Alternatively, we can rely on NATURAL JOIN, which doesn't require any JOIN criteria. This leads to a minimalist syntax but also makes our query a sword with two edges.
Basically, NATURAL JOIN automatically identifies all the columns that share the same name from both joined tables and use them to define the JOIN criteria. This can be quite useful when the primary/foreign keys columns share the same names, as in the following example:
ctx.select().from(EMPLOYEE)
.naturalJoin(SALE)
.fetch();
The jOOQ API for NATURAL JOIN relies on the naturalJoin() method. Next to this method, we have the methods corresponding to LEFT/RIGHT/FULL NATURAL OUTER JOIN as naturalLeftOuterJoin(), naturalRightOuterJoin(), and naturalFullOuterJoin(). Also, you may like to read the article at https://blog.jooq.org/2020/08/05/use-natural-full-join-to-compare-two-tables-in-sql/ about using NATURAL FULL JOIN to compare two tables. You can see all these at work in the bundled code.
For our example, the rendered SQL for the PostgreSQL dialect is as follows:
SELECT "public"."employee"."employee_number", ...
"public"."sale"."sale_id", ...
FROM "public"."employee"
NATURAL JOIN "public"."sale"
The EMPLOYEE and SALE tables share a single column name, EMPLOYEE_NUMBER – the primary key in EMPLOYEE and the foreign key in SALE. This column is used behind the scenes by NATURAL JOIN for filtering the result, which is the expected behavior.
But, remember that NATURAL JOIN picks up all columns that share the same name, not only the primary/foreign key columns, therefore this JOIN may produce undesirable results. For instance, if we join the PAYMENT and BANK_TRANSACTION tables, then NATURAL JOIN will use the common composite key (CUSTOMER_NUMBER, CHECK_NUMBER) but will also use the CACHING_DATE column. If this is not our intention, then NATURAL JOIN is not the proper choice. Expecting that only the (CUSTOMER_NUMBER, CHECK_NUMBER) is used is a wrong assumption, and it is recommended to rely on the ON clause or the jOOQ onKey() method:
ctx.select()
.from(PAYMENT.innerJoin(BANK_TRANSACTION).onKey())
.fetch();
On the other hand, if we expect that only the CACHING_DATE column will be used (which is hard to believe), then the USING clause can be a good alternative:
ctx.select()
.from(PAYMENT.innerJoin(BANK_TRANSACTION)
.using(PAYMENT.CACHING_DATE))
.fetch();
The USING clause is useful if we need any custom combination of columns that share the same name. On the other hand, NATURAL JOIN is considerably more prone to issues, since any schema changes that lead to a new matching column name will cause NATURAL JOIN to combine that new column as well.
It's also worth keeping in mind that Oracle doesn't accept that the columns used by NATURAL JOIN for filtering the result have qualifiers (ORA-25155 – column used in NATURAL join cannot have qualifiers). In this context, using the jOOQ Java-based schema with default settings comes with some issues. For instance, the expression ctx.select().from(EMPLOYEE).naturalJoin(SALE)… results in ORA-25155, since, by default, jOOQ qualifies the columns rendered in SELECT, including the common EMPLOYEE_NUMBER column, which is used by NATURAL JOIN. A quick workaround consists of explicitly rendering * via asterisk() instead of the columns list:
ctx.select(asterisk())
.from(PRODUCT)
.naturalJoin(TOP3PRODUCT)
.fetch();
Or, we can avoid using Java-based schema and write this:
ctx.select()
.from(table("EMPLOYEE")) .naturalJoin(table("SALE")).fetch()
Unqualified references to a common column are considered to belong to the left-hand side table if the join is INNER/LEFT OUTER JOIN, or to the right-hand side table if it is RIGHT OUTER JOIN.
Alternatively, the Oracle NATURAL JOIN is the same as the Oracle proprietary Equi Join with a join condition (an Equi Join relies on a join condition containing an equality operator).
As usual, you can practice all these examples and more in the bundled code. The application is named NaturalJoin. Next, let's tackle STRAIGHT JOIN.
STRAIGHT JOIN
Right from the start, we have to mention that STRAIGHT JOIN is specific to MySQL.
Basically, STRAIGHT JOIN instructs MySQL to always read the left-hand side table before the right-hand side table of JOIN. In this context, STRAIGHT JOIN may be useful to affect the execution plan chosen by MySQL for a certain JOIN. Whenever we consider that the query optimizer has put the JOIN tables in the wrong order, we can affect this order via STRAIGHT JOIN.
For instance, let's assume that the PRODUCT table has 5,000 rows, the ORDERDETAIL table has 200,000,000 rows, the ORDER table has 3,000 rows, and we have a join, as follows:
ctx.select(PRODUCT.PRODUCT_ID, ORDER.ORDER_ID)
.from(PRODUCT)
.innerJoin(ORDERDETAIL).on(
ORDERDETAIL.PRODUCT_ID.eq(PRODUCT.PRODUCT_ID))
.innerJoin(ORDER).on(
ORDER.ORDER_ID.eq(ORDERDETAIL.ORDER_ID))
.fetch();
Now, MySQL may or may not take into account the size of the intersection between ORDER.ORDER_ID and ORDERDETAIL.ORDER_ID versus PRODUCT.PRODUCT_ID and ORDERDETAIL.PRODUCT_ID. If the join between ORDERDETAIL and ORDER returns just as many rows as ORDERDETAIL, then this is not an optimal choice. And if starting the join with PRODUCT will filter down ORDERDETAIL to as many rows as PRODUCT, then this will be an optimal choice. This behavior can be enforced via the jOOQ straightJoin() method, which renders a STRAIGHT JOIN statement, as follows:
ctx.select(PRODUCT.PRODUCT_ID, ORDER.ORDER_ID)
.from(PRODUCT)
.straightJoin(ORDERDETAIL).on(
ORDERDETAIL.PRODUCT_ID.eq(PRODUCT.PRODUCT_ID))
.innerJoin(ORDER).on(
ORDER.ORDER_ID.eq(ORDERDETAIL.ORDER_ID))
.fetch();
In Oracle, the order of JOINs can be altered via /*+LEADING(a, b)*/ hint. In jOOQ this kind of hints can be passed via hint():
ctx.select(PRODUCT.PRODUCT_ID, ORDER.ORDER_ID)
.hint("/*+LEADING(CLASSICMODELS.ORDERDETAIL CLASSICMODELS.PRODUCT)*/")
… // joins come here
In SQL Server this can be accomplished via OPTION (FORCE ORDER):
ctx.select(PRODUCT.PRODUCT_ID, ORDER.ORDER_ID)
… // joins come here
.option("OPTION (FORCE ORDER)") .fetch();
Nevertheless, as Lukas Eder shared here: "MySQL's problems should have been made significantly less severe since they added hash join support. In any case, I think a disclaimer about premature optimization using hints could be added. With reasonable optimizers, hints should almost never be necessary anymore."
You can see the rendered SQL by running the StraightJoin application available for MySQL. Next, let's cover Semi and Anti Joins.
Semi and Anti Joins
Semi and Anti Joins are two of the relational algebra operators that don't have a direct correspondent in SQL syntax. Apart from the case of using Cloudera Impala, which provides a native syntax for Semi/Anti Joins, we have to rely on workarounds. In this context, Semi Join can be emulated via EXISTS/IN and Anti Join via NOT EXISTS/NOT IN predicates.
Since Semi/Anti Joins can be emulated via (NOT) EXISTS/(NOT) IN predicates, it means that we don't really join the right-hand side. In the case of a Semi Join, we just fetch the rows from the first table (left-hand side table) where there are matches found in the second table (right-hand side table), while in the case of Anti Join, we do exactly the opposite of the Semi Join; we just fetch the rows from the first table (the left-hand side table) where there are no matches found in the second table (the right-hand side table).
For instance, let's fetch the names of all EMPLOYEE that have CUSTOMER. Accomplishing this via a Semi Join emulated via the EXISTS predicate can be done in SQL as follows:
SELECT employee.first_name, employee.last_name FROM employee
WHERE EXISTS
(SELECT 1 FROM customer
WHERE employee.employee_number
= customer.sales_rep_employee_number);
In the bundled code, you can see how to express this SQL via the jOOQ DSL API. In addition, you can practice this use case emulated via the IN predicate. For now, let's use the jOOQ approach, which fills up the gap in expressiveness and enforces the clear intention of using a Semi Join via the leftSemiJoin() method. This jOOQ method saves us a lot of headaches – having neat code that is always emulated correctly in different SQL dialects and no brain-teasing in handling complex cases such as nesting EXISTS/IN predicates will make you fall in love with this method:
ctx.select(EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME)
.from(EMPLOYEE)
.leftSemiJoin(CUSTOMER)
.on(EMPLOYEE.EMPLOYEE_NUMBER.eq(
CUSTOMER.SALES_REP_EMPLOYEE_NUMBER))
.fetch();
This is just awesome! Check out the bundled code, SemiAndAntiJoin, to see more examples about chaining and/or nesting Semi Joins via the jOOQ DSL API. Every time, check out the rendered SQL and give a big thanks to jOOQ for it!
Next, let's focus on Anti Join. The Anti Join is the opposite of the Semi Join and is emulated via the NOT EXISTS/NOT IN predicates. For example, let's write an SQL representing an Anti Join to fetch the names of all EMPLOYEE that don't have CUSTOMER via NOT EXISTS:
SELECT employee.first_name, employee.last_name FROM employee
WHERE NOT (EXISTS
(SELECT 1
FROM customer
WHERE employee.employee_number
= customer.sales_rep_employee_number))
In the bundled code, you can see how to express this SQL via the jOOQ DSL API and the same example based on the NOT IN predicate. Nevertheless, I strongly encourage you to avoid NOT IN and opt for NOT EXISTS.
Important note
Most probably, you already know this, but just as a quick reminder, let's mention that the EXISTS and IN predicates are equivalent, but the NOT EXISTS and NOT IN predicates are not because the NULL values (if any) lead to undesirable results. For more details, please read this short but essential article: https://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in-and-null-values/.
Alternatively, and even better, use the jOOQ Anti Join represented by the leftAntiJoin() method:
ctx.select(EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME)
.from(EMPLOYEE)
.leftAntiJoin(CUSTOMER)
.on(EMPLOYEE.EMPLOYEE_NUMBER.eq(
CUSTOMER.SALES_REP_EMPLOYEE_NUMBER))
.fetch();
Check out the rendered SQL and more examples in the application named SemiAndAntiJoin.
A typical problem solved by Anti Joins refers to relational division or simply division. This is another operator of relational algebra without a direct correspondent in SQL syntax. In short, division is the inverse of the CROSS JOIN operation.
For instance, let's consider the ORDERDETAIL and TOP3PRODUCT tables. While CROSS JOIN gives us the Cartesian product as ORDERDETAIL x TOP3PRODUCT, the division gives us ORDERDETAIL ÷ TOP3PRODUCT or TOP3PRODUCT ÷ ORDERDETAIL. Let's assume that we want the IDs of all orders that contain at least three products contained in TOP3PRODUCT. This kind of task is a division and is commonly solved via two nested Anti Joins. The jOOQ code that solves this problem is as follows:
ctx.select()
.from(ctx.selectDistinct(ORDERDETAIL.ORDER_ID.as("OID")) .from(ORDERDETAIL).asTable("T1").leftAntiJoin(TOP3PRODUCT
.leftAntiJoin(ORDERDETAIL)
.on(field("T", "OID")).eq(ORDERDETAIL.ORDER_ID).and(TOP3PRODUCT.PRODUCT_ID
.eq(ORDERDETAIL.PRODUCT_ID))))
.on(trueCondition()))
.fetch();
This is cool and much less verbose than writing the same thing via NOT EXISTS. But that's not all! jOOQ comes with an even more elegant solution that can be used to express divisions. This solution uses the divideBy() and returning() methods to express a division in a concise, expressive, and very intuitive way. Check out the following code that can replace the previous code:
ctx.select().from(ORDERDETAIL
.divideBy(TOP3PRODUCT)
.on(field(TOP3PRODUCT.PRODUCT_ID).eq(
ORDERDETAIL.PRODUCT_ID))
.returning(ORDERDETAIL.ORDER_ID))
.fetch();
Check out this example and another one about finding the orders that contain at least the products of a given order in the BootAntiJoinDivision application.
As Lukas Eder pointed out here: "If you want to see how x is the inverse of ÷, you can choose two different tables, for instance A x B = C and C ÷ B = A".
Next, let’s cover the LATERAL/APPLY Join.
LATERAL/APPLY Join
The last topic covered in this chapter refers to the LATERAL/APPLY Join. This is part of standard SQL and is quite similar to a correlated subquery that allows us to return more than one row and/or column or to the Java Stream.flatMap(). Mainly, a lateral inner subquery sits on the right-hand side of JOIN (INNER, OUTER, and so on), and it can be materialized as a classical subquery, a derived table, a function call, an array unnesting, and so on. Its power consists of the fact that it can refer to (or laterally access) tables/columns from the left-hand side to determine which rows to retain. A LATERAL Join iterates through each row on the left-hand side, evaluating the inner subquery (the right-hand side) for each row, like a typical for-each loop. The rows returned by the inner subquery are retained to the result of the join with the outer query. The LATERAL keyword is essential because, without it, each subquery is evaluated separately (independently) and can't access columns from the left-hand side (from the FROM clause).
For example, selecting all OFFICE that has DEPARTMENT can be done via the LATERAL Join:
ctx.select()
.from(OFFICE, lateral(select().from(DEPARTMENT)
.where(OFFICE.OFFICE_CODE.eq(
DEPARTMENT.OFFICE_CODE))).as("t")).fetch()
As you can see, the jOOQ DSL API provides the lateral() method for shaping LATERAL Joins. The SQL rendered for the MySQL dialect is as follows:
SELECT `classicmodels`.`office`.`office_code`,...
`t`.`department_id`,
...
FROM `classicmodels`.`office`,
LATERAL
(SELECT `classicmodels`.`department`.`department_id`,...
FROM `classicmodels`.`department`
WHERE `classicmodels`.`office`.`office_code`
= `classicmodels`.`department`.`office_code`) AS `t`
Without an explicit JOIN, you would expect that CROSS JOIN (INNER JOIN ON true / INNER JOIN IN 1=1) is automatically inferred. Writing the previous query via LEFT OUTER JOIN LATERAL requires a dummy ON true / ON 1=1 clause, as follows:
ctx.select()
.from(OFFICE)
.leftOuterJoin(lateral(select().from(DEPARTMENT)
.where(OFFICE.OFFICE_CODE
.eq(DEPARTMENT.OFFICE_CODE))).as("t")).on(trueCondition())
.fetch();
A LATERAL Join has several use cases where it fits like a glove. For instance, it can be used for lateral unnesting of the array columns, for finding TOP-N per Foo (joining TOP-N query to a normal table), and it works nicely in combination with the so-called table-valued functions.
Unnesting the array columns
If you are an Oracle or PostgreSQL fan, then you know about their support for nested arrays (or nested collections). In PostgreSQL, we can declare a column of type array exactly as any other type but suffixed with square brackets – [] (for example, text[]). Since Oracle recognizes only nominal array types, we have to create them first via CREATE TYPE. I will not insist on this pure SQL aspect, since our goal is to jump into jOOQ DSL API usage.
So, let's consider the DEPARTMENT table, which has an array column named TOPIC. For each department, we have a list of topics (area of activities), and more departments may have interleaved topics. For instance, for the Sale department, we have four topics – 'commerce', 'trade', 'sellout', and 'transaction'.
Now, let's assume that we want to fetch the departments that have in common the 'commerce' and 'business' topics. For this, we can write a LATERAL Join via the jOOQ DSL API using the lateral() method, and we unnest the array (transform the array into a useable/queryable table) via the unnest() method, as follows:
ctx.select()
.from(DEPARTMENT, lateral(select(field(name("t", "topic"))) .from(unnest(DEPARTMENT.TOPIC).as("t", "topic")) .where(field(name("t", "topic")) .in("commerce", "business"))).as("r")).fetch();
For the PostgreSQL dialect, the rendered SQL is as follows:
SELECT
"public"."department"."department_id",
...
"public"."department"."accrued_liabilities",
"r"."topic"
FROM
"public"."department",
LATERAL (SELECT
"t"."topic"
FROM
unnest("public"."department"."topic") AS "t" ("topic") WHERE
"t"."topic" IN (?, ?)) AS "r"
Note that MySQL and SQL Server don't have support for array (collection) columns, but we can still declare anonymously typed arrays that can be unnested via the same jOOQ unnest() method. Next, let's talk about solving TOP-N per Foo tasks.
Solving TOP-N per Foo
While solving TOP-N problems over the entire dataset can be quite challenging, solving TOP-N per Foo problems can be really hard to digest. Fortunately, the LATERAL Join fits perfectly for these kinds of problems. For instance, fetching TOP-3 sales per employee can be expressed in jOOQ as follows:
ctx.select(EMPLOYEE.EMPLOYEE_NUMBER, EMPLOYEE.FIRST_NAME,
EMPLOYEE.LAST_NAME, field(name("t", "sales"))).from(EMPLOYEE,
lateral(select(SALE.SALE_.as("sales")).from(SALE)
.where(EMPLOYEE.EMPLOYEE_NUMBER
.eq(SALE.EMPLOYEE_NUMBER))
.orderBy(SALE.SALE_.desc())
.limit(3).asTable("t"))).orderBy(EMPLOYEE.EMPLOYEE_NUMBER)
.fetch();
The fact that the LATERAL Join allows us to access the EMPLOYEE.EMPLOYEE_NUMBER field/column does all the magic! The rendered SQL for MySQL dialect is as follows:
SELECT
`classicmodels`.`employee`.`employee_number`,
`classicmodels`.`employee`.`first_name`,
`classicmodels`.`employee`.`last_name`,
`t`.`sales`
FROM `classicmodels`.`employee`,
LATERAL (SELECT `classicmodels`.`sale`.`sale` as `sales`
FROM `classicmodels`.`sale`
WHERE `classicmodels`.`employee`.`employee_number`
= `classicmodels`.`sale`.`employee_number`
ORDER BY `classicmodels`.`sale`.`sale` desc limit ?)
as `t`
ORDER BY `classicmodels`.`employee`.`employee_number`
If we think of the derived table obtained via the inner SELECT as a table-valued function that has the employee number as an argument, then, in Oracle, we can write this:
CREATE TYPE "TABLE_RES_OBJ" AS OBJECT (SALES FLOAT);
CREATE TYPE "TABLE_RES" AS TABLE OF TABLE_RES_OBJ;
CREATE OR REPLACE NONEDITIONABLE FUNCTION
"TOP_THREE_SALES_PER_EMPLOYEE" ("employee_nr" IN NUMBER) RETURN TABLE_RES IS
"table_result" TABLE_RES;
BEGIN
SELECT
TABLE_RES_OBJ("SALE"."SALE") "sales"BULK COLLECT
INTO "table_result"
FROM
"SALE"
WHERE
"employee_nr" = "SALE"."EMPLOYEE_NUMBER"
ORDER BY
"SALE"."SALE" DESC
FETCH NEXT 3 ROWS ONLY;
RETURN "table_result";
END;
Next, we can use a LATERAL Join to call this function. The jOOQ code is as follows:
ctx.select(EMPLOYEE.EMPLOYEE_NUMBER, EMPLOYEE.FIRST_NAME,
EMPLOYEE.LAST_NAME, field(name("T", "SALES"))).from(EMPLOYEE, lateral(select().from(
TOP_THREE_SALES_PER_EMPLOYEE
.call(EMPLOYEE.EMPLOYEE_NUMBER)).asTable("T"))).orderBy(EMPLOYEE.EMPLOYEE_NUMBER)
.fetch();
The rendered SQL for Oracle is as follows:
SELECT
"CLASSICMODELS"."EMPLOYEE"."EMPLOYEE_NUMBER",
"CLASSICMODELS"."EMPLOYEE"."FIRST_NAME",
"CLASSICMODELS"."EMPLOYEE"."LAST_NAME",
"T"."SALES"
FROM "CLASSICMODELS"."EMPLOYEE",
LATERAL (SELECT
"TOP_THREE_SALES_PER_EMPLOYEE"."SALES"
FROM
table("CLASSICMODELS"."TOP_THREE_SALES_PER_EMPLOYEE" ("CLASSICMODELS"."EMPLOYEE"."EMPLOYEE_NUMBER")) "TOP_THREE_SALES_PER_EMPLOYEE") "T"
ORDER BY "CLASSICMODELS"."EMPLOYEE"."EMPLOYEE_NUMBER"
The TOP_THREE_SALES_PER_EMPLOYEE static field was generated by the jOOQ generator and is basically an ordinary table placed in the jooq.generated.tables package under the name TopThreeSalesPerEmployee. It can be used in the FROM clause of SELECT like any other table. Nevertheless, note that we have access to a method named call(), which is used for calling (with arguments) this table-valued function. However, while most databases treat table-valued functions as ordinary tables, in Oracle, it is quite common to treat them as standalone routines. In this context, jOOQ has a flag setting that allows us to indicate whether table-valued functions should be treated as ordinary tables (true) or as plain routines (false). Depending on this setting, jOOQ places the generated code in tables-section or the routines-section. This setting is set to true in all supported databases except Oracle. To enable this, we have to set the following:
Maven: <tableValuedFunctions>true</tableValuedFunctions>
Gradle: database { tableValuedFunctions = true }Or, programmatic:
...withDatabase(new Database()
.withTableValuedFunctions(true)
While the LATERAL keyword (which, by the way, is a pretty confusing word) can be used in MySQL, PostgreSQL, and Oracle, it cannot be used in SQL Server. Actually, SQL Server and Oracle have support for CROSS APPLY and OUTER APPLY via the APPLY keyword.
CROSS APPLY and OUTER APPLY
Specific to T-SQL, CROSS APPLY, and OUTER APPLY use the more suggestive APPLY keyword, which suggests that we apply a function to each table row. Mainly, CROSS APPLY is the same thing as CROSS JOIN LATERAL, and OUTER APPLY is the same thing as LEFT OUTER JOIN LATERAL. This is exactly how jOOQ will emulate CROSS/OUTER APPLY when they are not supported (for example, in PostgreSQL).
I guess everyone agrees with Lukas Eder's statement: "I find APPLY much more intuitive, especially when cross applying a table valued function. T CROSS APPLY F (T.X) means we're applying F to each row in T and create the cross product between T and the result of F. On the other hand, LATERAL is so weird, syntactically, especially this stupid requirement of writing ON TRUE all the time."
At the beginning of this section, we wrote a LATERAL Join to select all OFFICE that has DEPARTMENT. Writing the same thing but using CROSS APPLY can be done via the jOOQ crossApply() method, like this:
ctx.select()
.from(OFFICE).crossApply(select()
.from(DEPARTMENT)
.where(OFFICE.OFFICE_CODE
.eq(DEPARTMENT.OFFICE_CODE)).asTable("t")).fetch();
The render SQL for SQL Server is as follows:
SELECT [classicmodels].[dbo].[office].[office_code], ...
[t].[department_id], ...
FROM [classicmodels].[dbo].[office] CROSS APPLY
(SELECT [classicmodels].[dbo].[department].[department_id],
...
FROM [classicmodels].[dbo].[department]
WHERE [classicmodels].[dbo].[office].[office_code]
= [classicmodels].[dbo].[department].[office_code] ) [t]
Writing the previous query via LEFT OUTER JOIN LATERAL requires a dummy ON true/1=1 clause, but using OUTER APPLY via the jOOQ outerApply() method eliminates this little inconvenience:
ctx.select()
.from(OFFICE)
.outerApply(select()
.from(DEPARTMENT)
.where(OFFICE.OFFICE_CODE
.eq(DEPARTMENT.OFFICE_CODE)).asTable("t")).fetch();
And the SQL rendered for the SQL Server dialect is as follows:
SELECT [classicmodels].[dbo].[office].[office_code], ...
[t].[department_id], ...
FROM [classicmodels].[dbo].[office] OUTER APPLY
(SELECT [classicmodels].[dbo].[department].[department_id],
...
FROM [classicmodels].[dbo].[department]
WHERE [classicmodels].[dbo].[office].[office_code]
= [classicmodels].[dbo].[department].[office_code] ) [t]
Done! In the bundled code, you can practice examples of cross/outerApply() and table-valued functions as well.
Important note
In the examples of this chapter, we have used fooJoin(TableLike<?> table)and cross/outerApply(TableLike<?> table), but the jOOQ API also contains other flavors, such as fooJoin(String sql), cross/outerApply(String sql), fooJoin(SQL sql), cross/outerApply(SQL sql), fooJoin(String sql, Object... bindings), cross/outerApply(String sql, Object... bindings), fooJoin(String sql, QueryPart... parts), and cross/outerApply(String sql, QueryPart... parts). All of them are available in the jOOQ documentation and are marked with @PlainSQL. This annotation points out methods/types that allow us to produce a QueryPart that renders "plain SQL" inside of an AST, which are covered in Chapter 16, Tackling Aliases and SQL Templating.
All the examples (and more) from this chapter can be found in the LateralJoin application. Take your time to practice each example.
Summary
In this chapter, we have covered a comprehensive list of SQL JOINs and how they can be expressed via the jOOQ DSL API. We started with the well-known INNER/OUTER/CROSS JOIN, continued with Implicit Joins, Self Joins, NATURAL and STRAIGHT JOINs, and ended with Semi/Anti Joins, CROSS APPLY, OUTER APPLY, and LATERAL Joins. Also, among others, we covered the USING clause and the amazing jOOQ onKey() method.
In the next chapter, we tackle the jOOQ types, converters, and bindings.