Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
APPLY
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
APPLY (specifically, CROSS APPLY or OUTER APPLY) is the SQL Server specific syntax for the SQL standard LATERAL derived table syntax.
An example:
SELECT *
FROM
AUTHOR
-- All previous objects (i.e. AUTHOR)
-- are now in scope for the following subquery
CROSS APPLY (
SELECT count(*)
FROM BOOK
WHERE BOOK.AUTHOR_ID = AUTHOR.ID -- AUTHOR is in scope
);
DSL.using(configuration)
.select()
.from(
AUTHOR
.crossApply(
select(count()
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
)
)
.fetch();
This is most useful for:
- TOP N per category queries, which are harder to implement otherwise
- Local column variables
- Calling table valued functions on a row-by-row basis
Dialect support
This example using jOOQ:
selectFrom(AUTHOR.crossApply(selectCount().from(BOOK).where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))))
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, DB2, Databricks, Firebird, Hana, Postgres, Snowflake, Trino, YugabyteDB
SELECT
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
AUTHOR.DATE_OF_BIRTH,
AUTHOR.YEAR_OF_BIRTH,
AUTHOR.DISTINGUISHED,
alias_124651337.count
FROM AUTHOR
CROSS JOIN LATERAL (
SELECT count(*)
FROM BOOK
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
) alias_124651337
Oracle, SQLDataWarehouse, SQLServer, Sybase
SELECT
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
AUTHOR.DATE_OF_BIRTH,
AUTHOR.YEAR_OF_BIRTH,
AUTHOR.DISTINGUISHED,
alias_124651337.count
FROM AUTHOR
CROSS APPLY (
SELECT count(*)
FROM BOOK
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
) alias_124651337
ASE, Access, Aurora MySQL, BigQuery, ClickHouse, DuckDB, Exasol, H2, HSQLDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLite, Spanner, Teradata, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!