SQL Reference - LATERAL

LATERAL

LATERAL is an SQL clause used in conjunction with a subquery in the FROM clause. The LATERAL keyword allows the subquery to refer to columns of preceding tables in the FROM clause. Contrary to standard subqueries, which are independent and executed first, a LATERAL subquery is reevaluated for each row in the outer query, thereby allowing for complex, row-dependent operations.

Example

SELECT product, sale
FROM products
LATERAL (SELECT sum(quantity) as sale FROM sales WHERE products.product_id = sales.product_id) as subquery;

Output

| product      | sale |
| ----------- | ----------- |
| Product A   | 1500        |
| Product B   | 1320        |
| Product C   | 3430        |

Explanation

In the specified SQL query, LATERAL is used to allow subquery in FROM clause to refer to columns of preceding FROM items. The query returns a table with columns product and sale. Here, sale is a sum of quantities for each product from the sales table where the product_id matches between the sales and products tables.

Example

SELECT p.*
FROM Person p
OUTER APPLY (
    SELECT TOP (1) *
    FROM SalesOrderHeader soh
    WHERE soh.CustomerID = p.BusinessEntityID
    ORDER BY soh.OrderDate DESC
) as s

Output

| BusinessEntityID | Title | FirstName | LastName | SalesOrderID | OrderDate | CustomerID |
|------------------|-------|-----------|----------|--------------|-----------|------------|
| 1                | Mr.   | John      | Doe      | 730          | 2014-06-30| 1          |
| 2                | Ms.   | Jane      | Doe      | 729          | 2014-05-31| 2          |

Explanation

In the given SQL Server example, the OUTER APPLY clause (equivalent to the standard SQL keyword LATERAL) allows each row in the Person table to be used in the subquery to obtain the most recent SalesOrderHeader for that specific person. It functions similarly to a more dynamic JOIN clause, where each row in the left table (Person) can be utilized in a correlated subquery with the right table (SalesOrderHeader).

Example

SELECT a.col_name AS A_col_name,
       b.col_name AS B_col_name
FROM table_A AS a,
LATERAL (SELECT b.col_name FROM table_B AS b WHERE b.column_name = a.column_name) b

Output

+-------------+-------------+
|  A_col_name |  B_col_name |
+-------------+-------------+
| Value1      |  ValueA     |
| Value2      |  ValueB     |
| Value3      |  ValueC     |
+-------------+-------------+

Explanation

This SQL snippet applies the LATERAL join clause to interrelate rows from table_A with those of table_B using a shared column_name.LATERAL allows a subquery in the FROM clause to refer to columns in preceding items in the FROM list. In this example, it enables the subquery to access values in the table_A within its scope.