Lateral Keyword in SQL

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 other 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 Rank() function. The query would be like:



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 rescue. We will use our first query where we fetched the top 3 students with highest marks as the inner subquery. Next we join the Section table with the inner subquery using 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.

Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.