The jOOQ User Manual : SQL building : SQL Statements (DML) : The SELECT statement : SELECT clause

previous : next

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
SELECT BOOK.ID, BOOK.TITLE
SELECT BOOK.ID, TRIM(BOOK.TITLE)

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

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

-- Simple SELECTs
SELECT COUNT(*)
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 COUNT(*)
SELECT 0 -- Not a bind variable
SELECT ? -- A bind variable

// Select commonly used values
Result<?> result1 = create.select(count()).fetch();
Result<?> result2 = create.select(inline(0)).fetch();
Result<?> result3 = create.select(val(1)).fetch();

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

The SELECT DISTINCT clause

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

SELECT DISTINCT BOOK.TITLE

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

SELECT *

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.

Whenever jOOQ generates an asterisk (explicitly, or because jOOQ doesn't know the exact projection), the column order, and the column set are defined by the database server, not jOOQ. If you're using generated code, this may lead to problems as there might be a different column order than expected, as well as too many or too few columns might be projected.

// Explicitly selects all columns available from BOOK - No asterisk
create.select().from(BOOK).fetch();

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

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

// Renders a SELECT * statement - Explicit unqualified asterisk
create.select(asterisk()).from(BOOK).fetch();

// Renders a SELECT BOOK.* statement - Explicit qualified asterisk
create.select(BOOK.asterisk()).from(BOOK).fetch();
create.select(BOOK.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 = create.select(BOOK.ID, BOOK.TITLE);
Select<Record2<Integer, String>> s2 = create.select(BOOK.ID, 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 User Manual : SQL building : SQL Statements (DML) : The SELECT statement : SELECT clause

previous : next