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 (usingEXISTS
orIN
) -
ANTI JOIN
: A check for non-existence of rows from one table in another table (usingNOT EXISTS
or some conditionsNOT 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 aFOREIGN 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
orLATERAL
: Ordering the join tree from left to right, allowing the right side to access rows from the left side -
PARTITION BY
onOUTER JOIN
: To fill the gaps in a report that usesOUTER 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.7.3.1.
- CROSS JOIN
- 3.7.3.2.
- INNER JOIN
- 3.7.3.3.
- OUTER JOIN
- 3.7.3.4.
- SEMI JOIN
- 3.7.3.5.
- ANTI JOIN
- 3.7.3.6.
- ON clause
- 3.7.3.7.
- ON KEY clause
- 3.7.3.8.
- USING clause
- 3.7.3.9.
- NATURAL clause
- 3.7.3.10.
- LATERAL
- 3.7.3.11.
- APPLY
- 3.7.3.12.
- PARTITION BY
previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!