Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10

The SELECT statement

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

When you don't just perform CRUD (i.e. SELECT * FROM your_table WHERE ID = ?), you're usually generating new record types using custom projections. With jOOQ, this is as intuitive, as if using SQL directly. A more or less complete example of the "standard" SQL syntax, plus some extensions, is provided by a query like this:

SELECT from a complex table expression

-- get all authors' first and last names, and the number
-- of books they've written in German, if they have written
-- more than five books in German in the last three years
-- (from 2011), and sort those authors by last names
-- limiting results to the second and third row, locking
-- the rows for a subsequent update... whew!

  SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
    FROM AUTHOR
    JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
   WHERE BOOK.LANGUAGE = 'DE'
     AND BOOK.PUBLISHED_IN > 2008
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
  HAVING COUNT(*) > 5
ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
   LIMIT 2
  OFFSET 1
     FOR UPDATE

// And with jOOQ...



DSLContext create = DSL.using(connection, dialect);

create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
      .from(AUTHOR)
      .join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .where(BOOK.LANGUAGE.eq("DE"))
      .and(BOOK.PUBLISHED_IN.gt(2008))
      .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .having(count().gt(5))
      .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
      .limit(2)
      .offset(1)
      .forUpdate()
      .fetch();

Details about the various clauses of this query will be provided in subsequent sections.

SELECT from single tables

A very similar, but limited API is available, if you want to select from single tables in order to retrieve TableRecords or even UpdatableRecords. The decision, which type of select to create is already made at the very first step, when you create the SELECT statement with the DSL or DSLContext types:

public <R extends Record> SelectWhereStep<R> selectFrom(Table<R> table);

As you can see, there is no way to further restrict/project the selected fields. This just selects all known TableFields in the supplied Table, and it also binds <R extends Record> to your Table's associated Record. An example of such a Query would then be:

BookRecord book = create.selectFrom(BOOK)
                        .where(BOOK.LANGUAGE.eq("DE"))
                        .orderBy(BOOK.TITLE)
                        .fetchAny();

The "reduced" SELECT API is limited in the way that it skips DSL access to any of these clauses:

In most parts of this manual, it is assumed that you do not use the "reduced" SELECT API. For more information about the simple SELECT API, see the manual's section about fetching strongly or weakly typed records.

3.5.3.1.: SELECT clause
3.5.3.1.1.: Projection type safety
3.5.3.1.2.: SelectField
3.5.3.1.3.: Tables as SelectField
3.5.3.1.4.: SELECT *
3.5.3.1.5.: SELECT * EXCEPT (...)
3.5.3.1.6.: SELECT DISTINCT
3.5.3.1.7.: SELECT DISTINCT ON
3.5.3.1.8.: Convenience methods
3.5.3.2.: FROM clause
3.5.3.3.: JOIN operator
3.5.3.4.: Implicit path JOIN
3.5.3.5.: Implicit to-many path JOIN (new)
3.5.3.6.: Explicit path JOIN (new)
3.5.3.7.: Implicit path correlation (new)
3.5.3.8.: WHERE clause
3.5.3.9.: CONNECT BY clause
3.5.3.10.: GROUP BY clause
3.5.3.10.1.: GROUP BY columns
3.5.3.10.2.: GROUP BY column index
3.5.3.10.3.: GROUP BY tables
3.5.3.10.4.: GROUP BY ROLLUP
3.5.3.10.5.: GROUP BY CUBE
3.5.3.10.6.: GROUP BY GROUPING SETS
3.5.3.10.7.: GROUP BY empty grouping set
3.5.3.11.: HAVING clause
3.5.3.12.: WINDOW clauseWINDOW clause
3.5.3.13.: QUALIFY clause
3.5.3.14.: ORDER BY clause
3.5.3.14.1.: Ordering by field index
3.5.3.14.2.: Ordering and NULLS
3.5.3.14.3.: Ordering using CASE expressions
3.5.3.14.4.: Oracle's ORDER SIBLINGS BY clause
3.5.3.15.: LIMIT .. OFFSET clause
3.5.3.16.: WITH TIES clause
3.5.3.17.: SEEK clause
3.5.3.18.: FOR clause
3.5.3.19.: FOR UPDATE clause
3.5.3.20.: Set operations
3.5.3.20.1.: Type safety
3.5.3.20.2.: Projection rowtype
3.5.3.20.3.: Differences to standard SQL
3.5.3.20.4.: UNION
3.5.3.20.5.: INTERSECT
3.5.3.20.6.: EXCEPT
3.5.3.21.: Lexical and logical SELECT clause order

previous : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

Tell us a little bit about yourself and join our newsletter (examples)

The SELECT statement

SELECT from a complex table expression

SELECT from single tables

Table of contents

References to this page

Feedback

Community

Support

Legal

Documentation