Module org.jooq
Package org.jooq

Interface SelectCorrelatedSubqueryStep<R extends Record>

All Superinterfaces:
Attachable, AttachableQueryPart, AutoCloseable, FieldLike, Fields, Flow.Publisher<R>, Iterable<R>, Publisher<R>, org.reactivestreams.Publisher<R>, Query, QueryPart, ResultQuery<R>, Select<R>, SelectFinalStep<R>, Serializable, Statement, TableLike<R>
All Known Subinterfaces:
SelectConditionStep<R>, SelectConnectByAfterStartWithConditionStep<R>, SelectConnectByConditionStep<R>, SelectConnectByStep<R>, SelectDistinctOnStep<R>, SelectForJSONCommonDirectivesStep<R>, SelectForStep<R>, SelectForUpdateOfStep<R>, SelectForUpdateStep<R>, SelectForUpdateWaitStep<R>, SelectForXMLCommonDirectivesStep<R>, SelectForXMLPathDirectivesStep<R>, SelectForXMLRawDirectivesStep<R>, SelectFromStep<R>, SelectGroupByStep<R>, SelectHavingConditionStep<R>, SelectHavingStep<R>, SelectIntoStep<R>, SelectJoinStep<R>, SelectLimitAfterOffsetStep<R>, SelectLimitPercentAfterOffsetStep<R>, SelectLimitPercentStep<R>, SelectLimitStep<R>, SelectOffsetStep<R>, SelectOnConditionStep<R>, SelectOptionalOnStep<R>, SelectOptionStep<R>, SelectOrderByStep<R>, SelectQualifyConditionStep<R>, SelectQualifyStep<R>, SelectSeekLimitStep<R>, SelectSeekStep1<R,​T1>, SelectSeekStep10<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10>, SelectSeekStep11<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11>, SelectSeekStep12<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12>, SelectSeekStep13<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12,​T13>, SelectSeekStep14<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12,​T13,​T14>, SelectSeekStep15<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12,​T13,​T14,​T15>, SelectSeekStep16<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12,​T13,​T14,​T15,​T16>, SelectSeekStep17<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12,​T13,​T14,​T15,​T16,​T17>, SelectSeekStep18<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12,​T13,​T14,​T15,​T16,​T17,​T18>, SelectSeekStep19<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12,​T13,​T14,​T15,​T16,​T17,​T18,​T19>, SelectSeekStep2<R,​T1,​T2>, SelectSeekStep20<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12,​T13,​T14,​T15,​T16,​T17,​T18,​T19,​T20>, SelectSeekStep21<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12,​T13,​T14,​T15,​T16,​T17,​T18,​T19,​T20,​T21>, SelectSeekStep22<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9,​T10,​T11,​T12,​T13,​T14,​T15,​T16,​T17,​T18,​T19,​T20,​T21,​T22>, SelectSeekStep3<R,​T1,​T2,​T3>, SelectSeekStep4<R,​T1,​T2,​T3,​T4>, SelectSeekStep5<R,​T1,​T2,​T3,​T4,​T5>, SelectSeekStep6<R,​T1,​T2,​T3,​T4,​T5,​T6>, SelectSeekStep7<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7>, SelectSeekStep8<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8>, SelectSeekStep9<R,​T1,​T2,​T3,​T4,​T5,​T6,​T7,​T8,​T9>, SelectSeekStepN<R>, SelectSelectStep<R>, SelectStartWithStep<R>, SelectUnionStep<R>, SelectWhereStep<R>, SelectWindowStep<R>, SelectWithTiesAfterOffsetStep<R>, SelectWithTiesStep<R>
public interface SelectCorrelatedSubqueryStep<R extends Record> extends SelectFinalStep<R>
This type is used for the Select's DSL API when selecting generic Record types.

Example: 

 -- 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

   SELECT T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME, COUNT(*)
     FROM T_AUTHOR
     JOIN T_BOOK ON T_AUTHOR.ID = T_BOOK.AUTHOR_ID
    WHERE T_BOOK.LANGUAGE = 'DE'
      AND T_BOOK.PUBLISHED > '2008-01-01'
 GROUP BY T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME
   HAVING COUNT(*) > 5
 ORDER BY T_AUTHOR.LAST_NAME ASC NULLS FIRST
    LIMIT 2
   OFFSET 1
      FOR UPDATE
       OF FIRST_NAME, LAST_NAME
       NO WAIT
Its equivalent in jOOQ 
 create.select(TAuthor.FIRST_NAME, TAuthor.LAST_NAME, create.count())
       .from(T_AUTHOR)
       .join(T_BOOK).on(TBook.AUTHOR_ID.equal(TAuthor.ID))
       .where(TBook.LANGUAGE.equal("DE"))
       .and(TBook.PUBLISHED.greaterThan(parseDate('2008-01-01')))
       .groupBy(TAuthor.FIRST_NAME, TAuthor.LAST_NAME)
       .having(create.count().greaterThan(5))
       .orderBy(TAuthor.LAST_NAME.asc().nullsFirst())
       .limit(2)
       .offset(1)
       .forUpdate()
       .of(TAuthor.FIRST_NAME, TAuthor.LAST_NAME)
       .noWait();
Refer to the manual for more details

Referencing XYZ*Step types directly from client code

It is usually not recommended to reference any XYZ*Step types directly from client code, or assign them to local variables. When writing dynamic SQL, creating a statement's components dynamically, and passing them to the DSL API statically is usually a better choice. See the manual's section about dynamic SQL for details: https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql.

Drawbacks of referencing the XYZ*Step types directly:

  • They're operating on mutable implementations (as of jOOQ 3.x)
  • They're less composable and not easy to get right when dynamic SQL gets complex
  • They're less readable
  • They might have binary incompatible changes between minor releases
Author:
Lukas Eder