| The jOOQ User Manual : SQL building : SQL Statements (DML) : The SELECT statement : SELECT clause | previous : next |
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 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 does not explicitly support the asterisk operator in projections. However, you can omit the projection as in these examples:
// Explicitly selects all columns available from BOOK
create.select().from(BOOK).fetch();
// Explicitly selects all columns available from BOOK and AUTHOR
create.select().from(BOOK, AUTHOR).fetch();
create.select().from(BOOK).crossJoin(AUTHOR).fetch();
// Renders a SELECT * statement, as columns are unknown to jOOQ
create.select().from(table(name("BOOK"))).fetch();
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.
Feedback
Do you have any feedback about this page? We'd love to hear it!