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
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
USINGclause can quickly produce ambiguities as the column names between two tables in a complex join tree aren't always unique. This can even happen for queries that have worked in the past, but due to new columns being added to tables, will stop working. Use this clause with caution!
All of INNER JOIN, OUTER JOIN, SEMI JOIN, ANTI JOIN require a join predicate.
One way to supply this join predicate is the
USING clause, which allows for specifying a set of column names that are common to both tables, based on which to form a join predicate. Assuming we called our
SELECT * FROM AUTHOR JOIN BOOK USING (AUTHOR_ID)
create.select() .from(AUTHOR) .join(BOOK).using(AUTHOR.AUTHOR_ID) .fetch();
There is a certain risk of ambiguities as well in more complex join trees, but in simple cases, this can be a very convenient way to join tables if you design your schema accordingly. The is a good example where all
FOREIGN KEY columns share the referenced
PRIMARY KEY column's names.
Do you have any feedback about this page? We'd love to hear it!