Postgres's lateral joins allow for quite the good eDSL

Did you know that a single `LATERAL` clause can replace an entire stored‑procedure language in many reporting scenarios? In PostgreSQL, the combination of **LATERAL** with set‑returning functions turns ordinary `SELECT` statements into a powerful, **embedded domain‑specific language (eDSL)** for complex data shaping—without leaving the comfort of plain **SQL**.

In This Article

• What is a LATERAL Join and Why It Feels Like an eDSL
• Building Complex Transformations with LATERAL (Code Walkthrough)
• Real‑World Use Cases – When LATERAL Beats MySQL & Traditional Approaches
• Why This Matters – Business Impact & Maintainability
• Actionable Takeaways & Best‑Practice Checklist
• Frequently Asked Questions

What is a LATERAL Join and Why It Feels Like an eDSL

The syntax is simple: `FROM …, LATERAL (sub‑query) AS alias`. It sounds harmless, but what it does is feed each row from the preceding `FROM` item straight into the sub‑query, one at a time. That means the sub‑query can reference columns of the outer row, turning it into a tiny program inside the main query. I’ve found that when you start writing `LATERAL` statements, you’re already talking to the database like a programmer, not a data‑wizard. The sub‑query acts like a mini‑language: you can declare variables, add conditionals, and even write recursion if you’re clever. But how does that differ from a regular join? A normal join evaluates the join table once and then matches rows. With `LATERAL`, the join table (or sub‑query) is re‑executed for each row. That per‑row evaluation lets you do things that would otherwise need a stored procedure or a client‑side loop. Sound familiar? That’s why I think this feature is basically the missing bridge between SQL and full‑blown procedural code, especially when you’re dealing with JSON, arrays, or custom PL/pgSQL functions.

Building Complex Transformations with LATERAL (Code Walkthrough)

Let’s walk through a real example that shows how `LATERAL` can explode a JSON array, calculate running totals, and filter results—all in a single statement. ```sql -- Sample table CREATE TABLE orders ( id serial PRIMARY KEY, customer_id int, items jsonb -- e.g. '[{"sku":"A1","qty":2,"price":10},{"sku":"B2","qty":1,"price":20}]' ); -- Query using LATERAL as an eDSL SELECT o.id, o.customer_id, i.elem ->> 'sku' AS sku, (i.elem ->> 'qty')::int AS qty, (i.elem ->> 'price')::numeric AS unit_price, qty * unit_price AS line_total, SUM(qty * unit_price) OVER (PARTITION BY o.id) AS order_total FROM orders AS o CROSS JOIN LATERAL jsonb_array_elements(o.items) AS i(elem) WHERE (i.elem ->> 'price')::numeric > 5; -- predicate pushed into LATERAL ``` Here’s what’s happening, step by step: 1. `jsonb_array_elements(o.items)` turns every JSON object into a row. 2. The `CROSS JOIN LATERAL` feeds each order row into that function, giving us a per‑row view. 3. We cast the JSON fields to native types to perform arithmetic. 4. A window function aggregates the line totals back into a per‑order total. And the best part: the planner can push the `WHERE` predicate into the `jsonb_array_elements` call, so it never expands items that you’ll discard anyway. That usually saves a ton of I/O. In my experience, writing this as a stored procedure would take a full page of boilerplate. With `LATERAL`, you get the same logic in a few dozen lines, and the database can optimize it just like any other join.

Real‑World Use Cases – When LATERAL Beats MySQL & Traditional Approaches

*Hierarchical data* You know those adjacency‑list tables that make you write recursive CTEs? `LATERAL` can handle simple parent‑to‑child lookups without recursion. Just join to a sub‑query that references the parent row, and you’re done. *Top‑N per group* The classic `ROW_NUMBER()` trick can be replaced by a `LATERAL` that limits itself to the first N rows per group. That gives you row‑level parameters inside the sub‑query, something window functions can’t do directly. *Dynamic pivot / cross‑tab* If you need to generate columns on the fly—say you have a `metrics` table with arbitrary keys—`crosstab` can be nested inside a `LATERAL`. MySQL 8.0 can’t do that natively, so this is a real win for PostgreSQL enthusiasts. And let’s be real: in the past few months I’ve seen teams drop MySQL in favor of Postgres because they could replace dozens of stored procedures with a single `LATERAL` query. It’s pretty much the future of data‑centric logic.

Why This Matters – Business Impact & Maintainability

Reduce the code base dramatically. One SQL file replaces multiple stored procedures and application‑side loops. When analysts read a query that looks like a small script, onboarding is faster. No more “you need to learn the procedural language first.” And because PostgreSQL’s planner treats `LATERAL` just like any other join, you get native performance. No extra middleware, no double‑pass scans. From a business perspective, this means fewer bugs, quicker feature releases, and a cleaner tech stack. That’s why I think Postgres’s `LATERAL` is a game‑changer for any organization where data analysts and developers share the same tools.

Actionable Takeaways & Best‑Practice Checklist

• When to reach for LATERAL: per‑row calculations, dependent sub‑queries, or when you need an eDSL‑style flow.
• Pitfalls to avoid: keep an eye out for Cartesian explosions. Make the sub‑query as sargable as possible. Test with `EXPLAIN (ANALYZE)` before shipping.
• Quick migration guide:
  1. Identify a correlated MySQL sub‑query.
  2. Rewrite it as a `LATERAL` sub‑query.
  3. Replace the MySQL derived table with a PostgreSQL `LATERAL`. Done.
• Performance hint: let the planner push predicates. Avoid putting heavy logic in the outer query that cannot be pushed down.

Frequently Asked Questions

What is the difference between `LATERAL` and a regular sub‑query in PostgreSQL?

A regular sub‑query is evaluated once for the whole query, while `LATERAL` re‑evaluates the sub‑query for each row coming from the preceding FROM item, allowing the inner query to reference columns of that row.

Can I use LATERAL in MySQL or other databases?

MySQL 8.0 introduced a limited `LATERAL` syntax for derived tables, but it lacks full support for set‑returning functions and the tight planner integration PostgreSQL provides.

How does LATERAL improve performance compared to using CTEs for row‑by‑row logic?

LATERAL lets the planner push filters into the inner query and can stream rows without materializing the entire CTE, often resulting in lower I/O and CPU usage.

Is LATERAL safe to use in production environments?

Yes—LATERAL is a native PostgreSQL feature that has been stable since version 9.3. As with any query, test with realistic data volumes and monitor the execution plan.

What are some common pitfalls when writing LATERAL queries?

Forgetting to alias the LATERAL sub‑query, unintentionally creating Cartesian products, and using non‑sargable predicates that prevent index usage.

---

Related reading: Original discussion

Related Articles

• Anthropic Joins the Blender Development Fund as...

What do you think?

Have experience with this topic? Drop your thoughts in the comments - I read every single one and love hearing different perspectives!