Can my DB?

SELECT c.id, c.name, latest.order_date, latest.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT order_date, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_date DESC
  LIMIT 1
) latest;

• Added in 9.3. The right side can reference columns from any preceding FROM item.
• LEFT JOIN LATERAL ... ON TRUE is the outer form, which preserves left rows when the lateral subquery returns nothing.
• Pairs naturally with set-returning functions (unnest, json_array_elements, regexp_matches) for per-row expansion.

SELECT c.id, c.name, latest.order_date, latest.total
FROM customers c
JOIN LATERAL (
  SELECT order_date, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_date DESC
  LIMIT 1
) latest ON TRUE;

• Added in 8.0.14. Both inner and LEFT outer forms are supported.

• No user-facing LATERAL. MariaDB applies lateral-style rewrites internally, but does not expose the syntax. Tracked as MDEV-19078; unshipped as of 11.4.

SELECT c.id, c.name, latest.order_date, latest.total
FROM customers c
CROSS APPLY (
  SELECT order_date, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_date DESC
  FETCH FIRST 1 ROW ONLY
) latest;

• LATERAL, CROSS APPLY, and OUTER APPLY all added in 12.1.

SELECT c.id, c.name, latest.order_date, latest.total
FROM customers c
CROSS APPLY (
  SELECT TOP 1 order_date, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_date DESC
) latest;

• APPLY operators have been there since 2005; no LATERAL keyword.
• The common use case is calling a table-valued function with per-row arguments from the left side.

• No LATERAL or APPLY. A scalar correlated subquery in the SELECT list is a partial substitute, but only when one value per row is enough.

• No LATERAL. Approximations using window functions or stacked CTEs cover simple cases but not per-row table-valued expansion.

SELECT c.id, c.name, latest.order_date, latest.total
FROM customers c,
LATERAL (
  SELECT order_date, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_date DESC
  FETCH FIRST 1 ROW ONLY
) AS latest;

• Supports inner and outer LATERAL in the FROM clause.

SELECT c.id, c.name, f.value AS tag
FROM customers c,
LATERAL FLATTEN(input => c.tags) f;

• Standard LATERAL subqueries work, but LATERAL FLATTEN(input => col) is the Snowflake idiom for exploding VARIANT, ARRAY, and OBJECT columns.

SELECT c.id, c.name, tag
FROM customers c
CROSS JOIN UNNEST(c.tags) AS tag;

• No LATERAL keyword; CROSS JOIN UNNEST(array_col) is the idiomatic per-row expansion, LEFT JOIN UNNEST the outer form.