The T-SQL dialect has known the powerful
CROSS APPLY
and
OUTER APPLY
JOIN syntaxes for ages. The SQL:1999 standard had introduced almost equivalent “lateral derived tables”, which are finally supported with
PostgreSQL 9.3, or
Oracle 12c, which has adopted both the SQL standard
LATERAL
syntax and the T-SQL vendor-specific
CROSS APPLY
and
OUTER APPLY
syntaxes.
But what are we even talking about?
SQL features have a unique trait that few other languages have. They are obscure to those who don’t know them, as every language feature introduces a new syntax with new keywords. In this case:
APPLY
and
LATERAL
. But it really isn’t so hard to understand. All you do with a
CROSS APPLY
is a
CROSS JOIN
between two tables where the right-hand side of the join expression can reference columns from the left-hand side of the join expression. Consider the
following example by Martin Smith on Stack Overflow:
Reusing column aliases
SELECT number,
doubled_number,
doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (
SELECT 2 * CAST(number AS BIGINT)
) CA1(doubled_number)
CROSS APPLY (
SELECT doubled_number + 1
) CA2(doubled_number_plus_one)
See a SQLFiddle of the above example
In this example, we’re selecting numbers from a system table and cross apply a scalar subselect multiplying each number by two. Then to the whole table product, we cross apply another scalar subselect, adding one to the last number.
This particular example could also be implemented using subqueries in the
SELECT
clause. But as you can see in the above example,
doubled_number_plus_one
can be calculated from a previously calculated column in one go. That wouldn’t be so “simple” with subqueries.
Applying table-valued functions to each record
SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
This example may be even more useful when you want to join a table-valued function to each record of another table.
PostgreSQL’s LATERAL derived tabels
In PostgreSQL, this can be done somewhat magically by put-ting table-valued functions in the
SELECT
clause:
SELECT x, GENERATE_SERIES(0, x)
FROM (VALUES(0), (1), (2)) t(x)
See a SQLFiddle of the above example
The above yields
| X | GENERATE_SERIES |
|---|-----------------|
| 0 | 0 |
| 1 | 0 |
| 1 | 1 |
| 2 | 0 |
| 2 | 1 |
| 2 | 2 |
Alternatively, since PostgreSQL 9.3, you can use an explicit lateral derived table as such:
SELECT x, y
FROM (VALUES(0), (1), (2)) t(x),
LATERAL GENERATE_SERIES(0, t.x) u(y)
See a SQLFiddle of the above example
Yielding again
| X | Y |
|---|---|
| 0 | 0 |
| 1 | 0 |
| 1 | 1 |
| 2 | 0 |
| 2 | 1 |
| 2 | 2 |
CROSS APPLY and OUTER APPLY in jOOQ 3.3
The above clauses will also be supported in the upcoming editions of jOOQ 3.3 where you can write queries like this one here:
DSL.using(configuration)
.select()
.from(AUTHOR)
.crossApply(
select(count().as("c"))
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
.fetch();
Or lateral joins:
DSL.using(configuration)
.select()
.from(
values(row(0), row(1), row(2))
.as("t", "x"),
lateral(generateSeries(0,
fieldByName("t", "x"))
.as("u", "y")))
.fetch();
No matter if you’re using
jOOQ or native SQL, lateral derived tables or
CROSS APPLY
should definitely be part of your awesome SQL tool chain!
Like this:
Like Loading...
Postgres has a Monad!
Wow ;-) Care to explain?
can we write a custom lateral join to support with postgres9.2 or below version.
LATERAL was introduced in PostgreSQL version 9.3: https://www.postgresql.org/docs/9.3/static/sql-select.html, but I’m pretty sure you can do similar things in 9.2 as well with a different syntax, for instance by putting a table valued function in the SELECT clause. That’s a bit esoteric, but it may have the same effect.