previous : next

Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10

Joined tables

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

The JOIN operators that can be used in SQL SELECT statements are the most powerful and best supported means of creating new table expressions in SQL.

This section will explain the different types of join:

CROSS JOIN: A cross product
INNER JOIN: A cross product filtering on matches
OUTER JOIN: A cross product filtering on matches, additionally producing some unmatched rows
SEMI JOIN: A check for existence of rows from one table in another table (using EXISTS or IN)
ANTI JOIN: A check for non-existence of rows from one table in another table (using NOT EXISTS or some conditions NOT IN)

... as well as the different types of forming join predicates:

ON: Expressing join predicates explicitly
ON KEY: Expressing join predicates explicitly or implicitly based on a FOREIGN KEY
USING: Expressing join predicates implicitly based on an explicit set of shared column names in both tables
NATURAL: Expressing join predicates implicitly based on an implicit set of shared column names in both tables

... and then, there are additional ways to enrich joins:

APPLY or LATERAL: Ordering the join tree from left to right, allowing the right side to access rows from the left side
PARTITION BY on OUTER JOIN: To fill the gaps in a report that uses OUTER JOIN

All of these approaches are available twice in the jOOQ API:

On the org.jooq.Table API, where they form binary operators
On the SELECT API, where they are offered as convenience in jOOQ's DSL, to tame the parentheses

Table of contents

3.10.3.1.: CROSS JOIN
3.10.3.2.: INNER JOIN
3.10.3.3.: OUTER JOIN
3.10.3.4.: SEMI JOIN
3.10.3.5.: ANTI JOIN
3.10.3.6.: ON clause
3.10.3.7.: ON KEY clause
3.10.3.8.: USING clause
3.10.3.9.: NATURAL clause
3.10.3.10.: LATERAL
3.10.3.11.: APPLY
3.10.3.12.: PARTITION BY
3.10.3.13.: JOIN hints (new)
3.10.3.13.1.: HASH JOIN (new)
3.10.3.13.2.: LOOP JOIN (new)
3.10.3.13.3.: MERGE JOIN (new)

previous : next

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!