Lateral Join (Left Join Lateral)

Allows a subquery in the FROM clause to reference columns from preceding tables.

• references any column from the outer table

Left Join simply combines the rows from two tables based on a condition while Lateral Join evaluates the subquery on every row of the outer table.

Also see - correlated subqueries (not the same, but similar)

SELECT c.customer_id, c.customer_name, o.latest_order_id
FROM Customers c
LEFT JOIN LATERAL (
    SELECT order_id AS latest_order_id
    FROM Orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 1
) o ON true;

Subqueries vs Lateral Join

Subqueries are the logically correct way to solve problems of the form: "Get facts from A, conditional on facts from B".

• In such instances, it makes more logical sense to stick B in a sub-query than to do a join.
• It is also safer, in a practical sense, since you don't have to be cautious about getting duplicated facts from A due to multiple matches against B.

Practically speaking, however, the answer usually comes down to performance. This depends on the optimizers.

Historically, explicit joins usually win, hence the established wisdom that joins are better.

From MySQL manual:

"A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better — a fact that is not specific to MySQL Server alone."