Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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, 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
-- BIGQUERY, 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
-- ACCESS, ASE, AURORA_MYSQL, DERBY, DUCKDB, EXASOL, H2, HANA, HSQLDB, INFORMIX, MARIADB, MEMSQL, MYSQL, REDSHIFT, SQLITE,
-- TERADATA, VERTICA
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!