Table sources

This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

# Create complex and nested table sources

In the relational data model, there are many operations performed on entities, i.e. tables in order to join them together before applying predicates, renaming operations and projections. Apart from the convenience methods for joining table sources in the manual's section about the full SELECT syntax, the Table type itself provides a rich API for creating joined table sources. See an extract of the Table API:

// These are the various supported JOIN clauses. These JOIN types
// are followed by an additional ON / ON KEY / USING clause
TableOnStep join(TableLike<?> table);
TableOnStep join(String sql);
TableOnStep join(String sql, Object... bindings);

// All other JOIN types are equally overloaded with "String sql" convenience methods...
TableOnStep  leftOuterJoin(TableLike<?> table);
TableOnStep rightOuterJoin(TableLike<?> table);
TableOnStep  fullOuterJoin(TableLike<?> table);

// These JOIN types don't take any additional clause
Table<Record>             crossJoin(TableLike<?> table);
Table<Record>           naturalJoin(TableLike<?> table);
Table<Record>  naturalLeftOuterJoin(TableLike<?> table);
Table<Record> naturalRightOuterJoin(TableLike<?> table);

// Oracle and SQL Server also know PIVOT / UNPIVOT clauses for transforming a
// table into another one using a list of PIVOT values
PivotForStep pivot(Field<?>... aggregateFunctions);
PivotForStep pivot(Collection<? extends Field<?>> aggregateFunctions);

// Relational division can be applied to a table, transforming it into a
// "quotient" using an intuitive syntax
DivideByOnStep divideBy(Table<?> divisor);

The TableOnStep type contains methods for constructing the ON / ON KEY / USING clauses

// The ON clause is the most widely used JOIN condition. Provide arbitrary conditions as arguments
TableOnConditionStep on(Condition... conditions);
TableOnConditionStep on(String sql);
TableOnConditionStep on(String sql, Object... bindings);

// The USING clause is also part of the SQL standard. Use this if joined tables contain identical field names.
// The USING clause is simulated in databases that do not support it.
Table<Record> using(Field<?>... fields);
Table<Record> using(Collection<? extends Field<?>> fields);

// The ON KEY clause is a "synthetic" clause that does not exist in any SQL dialect. jOOQ usually has all
// foreign key relationship information to dynamically render "ON [ condition ... ]" clauses
TableOnConditionStep onKey() throws DataAccessException;
TableOnConditionStep onKey(TableField<?, ?>... keyFields) throws DataAccessException;
TableOnConditionStep onKey(ForeignKey<?, ?> key);

• For more details about the PIVOT clause, see the manual's section about the Oracle PIVOT syntax
• For more details about the DIVIDE BY clause, see the manual's section about the relational division syntax