What is the difference between #LATERAL JOIN and a subquery in PostgreSQL

What is a #LATERAL join?
The LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.)

🔗 [ https://stackoverflow.com/questions/28550679/what-is-the-difference-between-lateral-join-and-a-subquery-in-postgresql ]


The following relational database systems support the LATERAL JOIN syntax:
- #Oracle since 12c
- #PostgreSQL since 9.3
- #MySQL since 8.0.14


@javaCode☕️

How to limit the SQL query result set to Top-N rows only

#SQL:
SELECT
title
FROM
post
ORDER BY
id DESC
FETCH FIRST 5 ROWS ONLY

#PostgreSQL 8.3 and #MySQL:
SELECT
title
FROM
post
ORDER BY
id DESC
LIMIT 5

#Oracle 11g and older versions:
SELECT *
FROM (
SELECT
title
FROM
post
ORDER BY
id DESC
)
WHERE ROWNUM <= 5


@javaCode☕️