Support for LATERAL join / CROSS APPLY / OUTER APPLY

[Lukas Eder]

Hello,

This is just a suggestion for a nice-to-have feature to put on the roadmap.

Some databases support the SQL standard LATERAL join, which is also known as CROSS APPLY / OUTER APPLY in T-SQL, and since recently also in Oracle 12c, which now supports both syntaxes. An example use-case can be seen here, where a subquery unnecessarily needs to be expressed twice for a query, once as a derived table, and once as a correlated subquery, selecting only the first record:

SELECT CUSTOMERS.FIRSTNAME,
       CUSTOMERS.LASTNAME,
      (SELECT LICENSES.RELEASE_ID
       FROM LICENSES
       WHERE LICENSES.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
       ORDER BY LICENSES.CREATED_AT DESC
       LIMIT 1) AS REL_ID
FROM CUSTOMERS
WHERE EXISTS (
    SELECT 1 
    FROM LICENSES
    WHERE LICENSES.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
)

Much better:

SELECT CUSTOMERS.FIRSTNAME,
       CUSTOMERS.LASTNAME,
       l.RELEASE_ID
FROM CUSTOMERS 
CROSS JOIN LATERAL (
    SELECT *
    FROM LICENSES
    WHERE LICENSES.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
    ORDER BY LICENSES.CREATED_AT DESC
    LIMIT 1
) l

Taken from http://stackoverflow.com/a/25738021/521799.

The idea behind lateral joining is that the right hand side of the join can already access individual columns from the left-hand side of the join before the whole join expression is fed to the FROM clause. In the above example, the OP from Stack Overflow really just wanted the latest LICENSE for each CUSTOMER.

Hope this helps,

Lukas