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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
