CODEWITHSUNDEEP
javajooq
Klitos Kyriacou
Klitos Kyriacou

Reputation: 11621

Standalone selectFrom(table) vs select().from(table)

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?

Upvotes: 1

Views: 36

Answers (1)

rzwitserloot
rzwitserloot

Reputation: 102785

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.

Upvotes: 0

Related Questions