Lateral Keyword in SQL

The lateral keyword represents a lateral join between two or more tables. It joins the output of the outer query with the output of the underlying lateral subquery. It is like a for-each loop in SQL where the subquery iterates through each row of the concerned table, evaluating the subquery for each row. 

The output rows returned by the inner subquery are then added to the result of the join with the outer query. Without Lateral, each subquery would be evaluated independent of each others and could not refer to the items in the table referenced in the outer query. 

Syntax of Lateral:
A Lateral join is denoted by the keyword Lateral which precedes the inner subquery, as shown below: 

SELECT <Column Name>
FROM <Reference Table Name>
LATERAL <Inner Subquery> 

Example:
Let us assume that we have to find the top 3 students of a class with the highest marks. The query would be a simple one as follows: 

SELECT studId, marks 
FROM student 
ORDER BY marks DESC FETCH FIRST 3 ROWS ONLY 

Now assuming that each class has ‘n’ sections and we need to find section-wise top 3 students with the highest marks. Now we would need to join the section tables to get the result and find the top 3 students using the Rank() function. The query would be like this: 

SELECT secId, studId, marks 
FROM ( SELECT sec.secId, stud.studId, stud.marks, 
       RANK() OVER (PARTITION BY sec.secId ORDER BY marks DESC) rn 
       FROM student stud, section sec WHERE sec.secId = stud.secId )
WHERE rn <= 3 

This is where Lateral comes to the rescue. We will use our first query where we fetched the top 3 students with the highest marks as the inner subquery. Next, we join the Section table with the inner subquery using the Lateral keyword. The inner query which is to the right of Lateral would be evaluated for every single row in the left table. Here’s how the query would look like: 

SELECT sec.secId, stud.studId, stud.marks 
FROM section sec,
LATERAL (SELECT studId, marks FROM student stud 
         WHERE sec.secId = stud.secId 
         ORDER BY marks DESC FETCH FIRST 3 ROWS ONLY) 

Why Use Lateral ?
In simple terms, Lateral provides a simpler and cleaner approach for returning more than one columns as the output. Although since the inner subquery has to run for every row of the main query, it makes the query a little slow. Some important applications of Lateral keyword are aggregation of two or more tables and in activity logs where logging might require a lot of temporary data.

My Personal Notes arrow_drop_up

Save