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>
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.