Using jOOQ 3.19.14 with the PostgreSQL dialect, I had something like this:

dsl.selectFrom(MYRECORD)
    .where(some_condition)
    .andNotExists(
        select()
        .from(MYRECORD.as("other"))
        .where(some_other_condition)

Then I thought I could change it to a more concise equivalent:

dsl.selectFrom(MYRECORD)
    .where(some_condition)
    .andNotExists(
        selectFrom(MYRECORD.as("other"))
        .where(some_other_condition)

but I found that the generated SQL query changed from

... and not exists (select 1 from myrecord as other where some_other_condition) ...

to a much wordier statement:

... and not exists (select other.column1, other.column2, ..., other.columnN from myrecord as other where some_other_condition) ...

Of course, in the end the execution plan and performance for both of those queries is the same, but why does the generated SQL statement change so drastically between what I thought would be equivalent jOOQ expressions? Is this difference documented somewhere in the jOOQ guides?

Using jOOQ 3.19.14 with the PostgreSQL dialect, I had something like this:

dsl.selectFrom(MYRECORD)
    .where(some_condition)
    .andNotExists(
        select()
        .from(MYRECORD.as("other"))
        .where(some_other_condition)

Then I thought I could change it to a more concise equivalent:

dsl.selectFrom(MYRECORD)
    .where(some_condition)
    .andNotExists(
        selectFrom(MYRECORD.as("other"))
        .where(some_other_condition)

but I found that the generated SQL query changed from

... and not exists (select 1 from myrecord as other where some_other_condition) ...

to a much wordier statement:

... and not exists (select other.column1, other.column2, ..., other.columnN from myrecord as other where some_other_condition) ...

Of course, in the end the execution plan and performance for both of those queries is the same, but why does the generated SQL statement change so drastically between what I thought would be equivalent jOOQ expressions? Is this difference documented somewhere in the jOOQ guides?

• java
• jooq

Share Improve this question Follow edited Jan 31 at 16:26 Klitos Kyriacou asked Jan 31 at 11:42 Klitos KyriacouKlitos Kyriacou 11.8k22 gold badges4747 silver badges8383 bronze badges 2

• Assuming MYRECORD is a generated table, there shouldn't bea ny difference between the two statements, they're equivalent. You probably omitted some detail of your query. – Lukas Eder Commented Feb 3 at 21:17
• @LukasEder thanks for the reply. Yes, MYRECORD is a generated class. I will try to create a minimal reproducible example when I get some time. – Klitos Kyriacou Commented Feb 4 at 9:18

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

The basic docs on selecting: JOOQ docs on SELECT statement indicates:

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 to your Table's associated Record.

Bolded highlight is my contribution.

selectFrom is made for JPA converts and the exceedingly simplified 'I just want to select objects and object types match table defs exactly'. select() lets you write SQL in java. Very different concepts.