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

Feedback

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

The jOOQ Logo