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
APPLY
Applies to ✅ 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, 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, Derby, DuckDB, Exasol, H2, HSQLDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLite, Teradata, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!