Lateral Join vs. Subquery: What’s the Difference

1. Introduction

Joins and subqueries represent fundamental tools in SQL for combining and retrieving data from multiple tables. Among these, lateral joins and subqueries often solve similar problems but in fundamentally different ways. However, each has its unique behavior, use case, and performance implications.

In this tutorial, we’ll explore the differences between lateral joins and subqueries, emphasizing how each technique behaves in practice. For demonstration purposes, we’ll use the Baeldung University database and perform all operations in the PostgreSQL database management system (DBMS).

2. Lateral Joins

A lateral join enables a subquery in the FROM clause to access columns from preceding tables in the same FROM clause. The LATERAL keyword makes it possible for the subquery to be evaluated for each row of the preceding table. This effectively allows the subquery to act like a correlated subquery, but within a join context.

Furthermore, the explained mechanism proves particularly valuable when working with functions, aggregations, or filtered result sets that rely on values from the current row of the preceding table. Additionally, lateral joins simplify query logic that would otherwise require nested subqueries or complex Common Table Expressions (CTEs).

Some of the key characteristics of lateral joins include:

• It can reference columns from tables listed earlier in the FROM clause
• It evaluates the subquery for each row of the outer table
• It can return one or more rows for each row of the outer table
• The LATERAL keyword is mandatory in PostgreSQL to indicate this behavior

Moving forward, let’s show a practical example of the usage of Lateral joins.

2.1. Practical Example

For illustration, we use the Faculty and Teaching tables in the Baeldung University database. Let’s retrieve the faculty’s name and their latest teaching assignment based on the year and semester:

SELECT 
    f.id, 
    f.name, 
    t.course_id, 
    t.semester, 
    t.year
FROM 
    Faculty f
LEFT JOIN LATERAL (
    SELECT 
        te.course_id, 
        te.semester, 
        te.year
    FROM 
        Teaching te
    WHERE 
        te.faculty_id = f.id
    ORDER BY 
        te.year DESC, 
        CASE te.semester
            WHEN 'FALL' THEN 3
            WHEN 'SUMMER' THEN 2
            WHEN 'SPRING' THEN 1
            ELSE 0
        END DESC
    LIMIT 1
) t ON true;
+-----+-----------------+-----------+----------+--------+
| id  | name            | course_id | semester | year   |
|-----+-----------------+-----------+----------+--------|
| 1   | Anubha Gupta    | EC111     | SPRING   | 2023   |
| 2   | Anubha Gupta    | <null>    | <null>   | <null> |
| 3   | Anubha Gupta    | <null>    | <null>   | <null> |
| 21  | Peter Pan       | EC121     | SPRING   | 2023   |
| 22  | Peter Pan       | <null>    | <null>   | <null> |
| 23  | Peter Pan       | <null>    | <null>   | <null> |
| 33  | Nando de Fretas | EC131     | SPRING   | 2023   |
...
Time: 0.013s

In this result, each row displays the faculty’s ID and name from the Faculty table. If the faculty has taught any course, their most recent course ID, semester, and year are shown. Conversely, if the faculty has no teaching records, the fields from the Teaching table appear as NULL.

The LEFT JOIN LATERAL enables the query to run the subquery individually for each faculty record. Additionally, the subquery retrieves the latest course each faculty member taught, and the LIMIT 1 returns only the top record. When the query finds no matching teaching assignment, the LEFT JOIN keeps the faculty’s details and fills the teaching-related fields with NULL.

3. Subquery

A subquery is a query nested inside another SQL query. We typically use it to retrieve data for filtering, aggregation, or comparison in the outer query. Additionally, subqueries run independently of the outer query unless correlated, and can appear in the WHERE, FROM, or SELECT clauses.

Unlike lateral joins, subqueries don’t automatically have access to the columns of the outer query unless explicitly correlated. When correlated, a subquery uses values from the outer query row to compute its result for that specific row, much like how lateral joins evaluate subqueries per row.

Some of the key characteristics of subqueries include:

• It either depends on the outer query (correlated) or runs independently (non-correlated)
• It simplifies the main query by handling intermediate logic separately
• In certain cases, especially with correlated subqueries, they reduce performance by executing for each row of the outer query
• Subqueries access outer query values without the LATERAL keyword, but the WHERE clause must explicitly define the correlation

Let’s take a practical example similar to the previous one, but this time with the use of a subquery.

3.1. Practical Example

For example, let’s retrieve the faculty’s name and their most recent teaching assignment using a correlated subquery inside the SELECT clause:

SELECT 
    f.id, 
    f.name,
    (
        SELECT te.course_id
        FROM Teaching te
        WHERE te.faculty_id = f.id
        ORDER BY te.year DESC, 
            CASE te.semester
                WHEN 'FALL' THEN 3
                WHEN 'SUMMER' THEN 2
                WHEN 'SPRING' THEN 1
                ELSE 0
            END DESC
        LIMIT 1
    ) AS latest_course_id
FROM Faculty f;
+-----+-----------------+------------------+
| id  | name            | latest_course_id |
|-----+-----------------+------------------|
| 1   | Anubha Gupta    | EC111            |
| 2   | Anubha Gupta    | <null>           |
| 3   | Anubha Gupta    | <null>           |
| 21  | Peter Pan       | EC121            |
| 22  | Peter Pan       | <null>           |
| 23  | Peter Pan       | <null>           |
| 33  | Nando de Fretas | EC131            |
...
Time: 0.018s

In this query, the correlated subquery inside the SELECT clause runs for each faculty row and fetches the most recent course_id from the Teaching table. Further, the WHERE clause establishes the correlation by linking te.faculty_id to the current f.id. Then, the ORDER BY and LIMIT 1 ensure the subquery returns only the latest teaching assignment. If no teaching record exists for a faculty member, the subquery returns NULL for latest_course_id.

4. Differences Between Lateral Join and Subquery

Let’s look at the key differences between lateral join and subquery:

The table summarizes the differences between lateral joins and subqueries

5. Conclusion

In this article, we’ve explored the differences between lateral joins and subqueries. We highlighted how each handles data retrieval across related tables. While both techniques can achieve similar results, lateral joins offer more flexibility and often better performance when working with per-row dependent subqueries. Finally, their distinct behaviors and use cases help in writing cleaner, more efficient SQL queries.