In the realm of PostgreSQL, optimizing query performance is a constant pursuit. Two powerful tools in a developer’s arsenal are LATERAL JOIN and subqueries. While both can achieve similar results, understanding their nuances is crucial for writing efficient and maintainable SQL. This post delves into the differences between LATERAL JOIN and subqueries in PostgreSQL, exploring their strengths, weaknesses, and ideal use cases. We’ll equip you with the knowledge to choose the right tool for the job, ultimately leading to faster and more elegant database interactions.

Understanding Subqueries in PostgreSQL

Subqueries, nested queries within another query, are a fundamental part of SQL. They allow you to retrieve data based on the results of another query. Imagine needing to find all customers who have placed orders above the average order value. A subquery can calculate the average order value, and the outer query can then select customers based on this calculated average. This nested structure allows for complex logic within a single query statement. Subqueries can be used in various clauses like WHERE, HAVING, and FROM. However, traditional subqueries have limitations when it comes to correlating data from the outer query with the inner query, especially when dealing with row-by-row operations.

Subqueries can be categorized as correlated and non-correlated. Non-correlated subqueries execute only once, independent of the outer query. Correlated subqueries, on the other hand, execute repeatedly, once for each row returned by the outer query. This can lead to performance issues, especially with large datasets.

Example: SELECT FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_total > (SELECT AVG(order_total) FROM orders));

Introducing LATERAL JOINs

LATERAL JOIN was introduced in PostgreSQL 9.3 to address some of the limitations of correlated subqueries. It provides a more efficient and readable way to perform row-by-row operations. The keyword LATERAL allows the join to reference columns from the preceding tables in the FROM clause. This means you can effectively create a dynamic subquery that changes its behavior based on each row of the joined table. This feature makes LATERAL JOIN extremely powerful for tasks that require processing data on a per-row basis.

Think of a scenario where you need to find the top three products purchased by each customer. A LATERAL JOIN can efficiently achieve this by joining the customer table with a subquery that selects the top three products for each customer ID, using the customer ID from the outer query within the subquery. This targeted approach avoids unnecessary calculations and improves performance significantly compared to a correlated subquery.

Example: SELECT c.customer_name, p.product_name FROM customers c LATERAL JOIN (SELECT product_name FROM orders o WHERE o.customer_id = c.customer_id ORDER BY order_date DESC LIMIT 3) p ON true;

Key Differences and When to Use Each

The core difference lies in how they access data. Subqueries operate in a nested manner, while LATERAL JOINs operate on a row-by-row basis, referencing preceding tables. This distinction affects both performance and readability. For simple lookups or aggregations, subqueries can be sufficient. However, for complex row-by-row operations, especially those involving sorting or limiting results based on the outer query, LATERAL JOIN often offers better performance and clarity.

• Performance: LATERAL JOIN often outperforms correlated subqueries, especially with large datasets and complex logic.
• Readability: LATERAL JOIN can improve the readability of complex queries by flattening the nested structure and making the logic more explicit.

Here’s a helpful table summarizing the key differences:

Real-World Examples and Best Practices

Consider a scenario in e-commerce where you want to display personalized product recommendations based on a user’s recent browsing history. Using a LATERAL JOIN, you can efficiently join the user’s browsing history table with a product recommendation table, filtering recommendations based on the user’s individual browsing patterns. This ensures that each user sees relevant product suggestions, enhancing their shopping experience.

Another example is in data analysis, where you might need to calculate running totals or moving averages. LATERAL JOIN allows you to access previous rows within the data set, making such calculations more straightforward and efficient. Using a window function within a LATERAL JOIN can be especially powerful for these types of analytical queries.

1. Analyze your query requirements: Determine if you need row-by-row processing.
2. Consider data volume: For large datasets, LATERAL JOIN is often more efficient.
3. Prioritize readability: Choose the approach that makes your query logic clearer.

Looking for another resource to dive deeper? Check out PostgreSQL’s official documentation on LATERAL joins. This documentation provides comprehensive explanations and further examples.

Infographic Placeholder: (Visual comparison of LATERAL JOIN vs. Subquery performance)

FAQ

Q: Can I use LATERAL JOIN with any type of subquery?

A: Yes, you can use LATERAL JOIN with various types of subqueries, including those that involve aggregation, sorting, and filtering.

See also these related articles from reputable sources to further expand your understanding:

• Postgres Lateral Join Examples
• PostgreSQL LATERAL Joins

Choosing between LATERAL JOIN and subqueries depends on the specific needs of your query. For complex, row-by-row operations, LATERAL JOIN often provides a more efficient and readable solution. By understanding the nuances of each technique, you can significantly improve the performance and maintainability of your PostgreSQL queries. Explore the provided resources and examples, experiment with different approaches, and optimize your queries for maximum efficiency. Learn more about advanced SQL techniques to elevate your database skills. Consider exploring related topics like window functions and common table expressions (CTEs) to further enhance your query optimization capabilities.

Question & Answer :
Since Postgres came out with the ability to do LATERAL joins, I’ve been reading up on it, since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall query take four minutes or more.

I understand that LATERAL joins may be able to help me, but even after reading articles like this one from Heap Analytics, I still don’t quite follow.

What is the use case for a LATERAL join? What is the difference between a LATERAL join and a subquery?

What is a LATERAL join?

The feature was introduced with PostgreSQL 9.3. The manual:

Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.)

Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function’s arguments can contain references to columns provided by preceding FROM items in any case.

Basic code examples are given there.

More like a correlated subquery

A LATERAL join is more like a correlated subquery, not a plain subquery, in that expressions to the right of a LATERAL join are evaluated once for each row left of it - just like a correlated subquery - while a plain subquery (table expression) is evaluated once only. (The query planner has ways to optimize performance for either, though.)
Related answer with code examples for both side by side, solving the same problem:

• Optimize GROUP BY query to retrieve latest row per user

For returning more than one column, a LATERAL join is typically simpler, cleaner and faster.
Also, remember that the equivalent of a correlated subquery is LEFT JOIN LATERAL ... ON true:

• Call a set-returning function with an array argument multiple times

Things a subquery can’t do

There are things that a LATERAL join can do, but a (correlated) subquery cannot (easily). A correlated subquery can only return a single value, not multiple columns and not multiple rows - with the exception of bare function calls (which multiply result rows if they return multiple rows). But even certain set‑returning functions are only allowed in the FROM clause. Like unnest() with multiple parameters in Postgres 9.4 or later. The manual:

This is only allowed in the FROM clause;

So this works, but cannot (easily) be replaced with a subquery:

CREATE TABLE tbl (a1 int[], a2 int[]); SELECT * FROM tbl, unnest(a1, a2) u(elem1, elem2); -- implicit LATERAL 

The comma (,) in the FROM clause is short notation for CROSS JOIN.
LATERAL is assumed automatically for table functions.
About the special case of UNNEST( array_expression [, ... ] ):

• How do you declare a set-returning-function to only be allowed in the FROM clause?

Set-returning functions in the SELECT list

You can also use set-returning functions like unnest() in the SELECT list directly. This used to exhibit surprising behavior with more than one such function in the same SELECT list up to Postgres 9.6. But it has finally been sanitized with Postgres 10 and is a valid alternative now (even if not standard SQL). See:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

Building on above example:

SELECT *, unnest(a1) AS elem1, unnest(a2) AS elem2 FROM tbl; 

Comparison:

fiddle for pg 9.6
fiddle for pg 10

To note: a (combination of) set-returning function(s) in the SELECT list that produces no rows eliminates the row. Internally it translates to CROSS JOIN LATERAL ROWS FROM ..., not to LEFT JOIN LATERAL ... ON true!

fiddle for pg 16 demonstrating the difference.

Clarify misinformation

The manual:

For the INNER and OUTER join types, a join condition must be specified, namely exactly one of NATURAL, ON join_condition, or USING (join_column [, …]). See below for the meaning.
For CROSS JOIN, none of these clauses can appear.

So these two queries are valid (even if not particularly useful):

SELECT * FROM tbl t LEFT JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t <b>ON true</b>; SELECT * FROM tbl t, LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t; 

While this one is not:

<strike><code>SELECT * FROM tbl t LEFT JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;</code></strike>

That’s why Andomar’s code example is correct (the CROSS JOIN does not require a join condition) and Attila’s is was not.