New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6

SELECT clause

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

The SELECT clause lets you project your own record types, referencing table fields, functions, arithmetic expressions, etc. The DSL type provides several methods for expressing a SELECT clause:

-- The SELECT clause
// Provide a varargs Fields list to the SELECT clause:
Select<?> s1 =, BOOK.TITLE);
Select<?> s2 =, trim(BOOK.TITLE));

Some commonly used projections can be easily created using convenience methods:

-- Simple SELECTs
SELECT 0 -- Not a bind variable
SELECT 1 -- Not a bind variable
// Select commonly used values
Result<?> result1 = create.selectCount().fetch();
Result<?> result2 = create.selectZero().fetch();
Result<?> result3 = create.selectOne().fetch();

Which are short forms for creating Column expressions from the org.jooq.impl.DSL API

-- Simple SELECTs
SELECT 0 -- Not a bind variable
SELECT ? -- A bind variable
// Select commonly used values
Result<?> result1 =;
Result<?> result2 =;
Result<?> result3 =;

See more details about functions and expressions in the manual's section about Column expressions


The DISTINCT keyword can be included in the method name, constructing a SELECT clause

Select<?> select1 = create.selectDistinct(BOOK.TITLE).fetch();


jOOQ supports the asterisk operator in projections both as a qualified asterisk (through Table.asterisk()) and as an unqualified asterisk (through DSL.asterisk()). It is also possible to omit the projection entirely, in case of which an asterisk may appear in generated SQL, if not all column names are known to jOOQ.

// Explicitly selects all columns available from BOOK - No asterisk;

// Explicitly selects all columns available from BOOK and AUTHOR - No asterisk, AUTHOR).fetch();;

// Renders a SELECT * statement, as columns are unknown to jOOQ - Implicit unqualified asterisk"BOOK"))).fetch();

// Renders a SELECT * statement - Explicit unqualified asterisk;

// Renders a SELECT BOOK.* statement - Explicit qualified asterisk;, AUTHOR.asterisk()).from(BOOK, AUTHOR).fetch();

With all of the above syntaxes, the row type (as discussed below) is unknown to jOOQ and to the Java compiler.

It is worth mentioning that in many cases, using an asterisk is a sign of an inefficient query because if not all columns are needed, too much data is transferred between client and server, plus some joins that could be eliminated otherwise, cannot.

Typesafe projections with degree up to 22

Since jOOQ 3.0, records and row value expressions up to degree 22 are now generically typesafe. This is reflected by an overloaded SELECT (and SELECT DISTINCT) API in both DSL and DSLContext. An extract from the DSL type:

// Non-typesafe select methods:
public static SelectSelectStep<Record> select(Collection<? extends Field<?>> fields);
public static SelectSelectStep<Record> select(Field<?>... fields);

// Typesafe select methods:
public static <T1>         SelectSelectStep<Record1<T1>>         select(Field<T1> field1);
public static <T1, T2>     SelectSelectStep<Record2<T1, T2>>     select(Field<T1> field1, Field<T2> field2);
public static <T1, T2, T3> SelectSelectStep<Record3<T1, T2, T3>> select(Field<T1> field1, Field<T2> field2, Field<T3> field3);
// [...]

Since the generic R type is bound to some Record[N], the associated T type information can be used in various other contexts, e.g. the IN predicate. Such a SELECT statement can be assigned typesafely:

Select<Record2<Integer, String>> s1 =, BOOK.TITLE);
Select<Record2<Integer, String>> s2 =, trim(BOOK.TITLE));

For more information about typesafe record types with degree up to 22, see the manual's section about Record1 to Record22.

The jOOQ Logo