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_IDFROM CUSTOMERSWHERE EXISTS (
    SELECT 1 
    FROM LICENSES
    WHERE LICENSES.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID)


Much better:

SELECT CUSTOMERS.FIRSTNAME,
       CUSTOMERS.LASTNAME,
       l.RELEASE_IDFROM 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

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Reply via email to