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>
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.
- Calculate Median in MySQL
- Difference between BCNF and 4NF in DBMS
- Relational Query Evaluation | Set 2
- Relational Query Evaluation | Set 1
- Difference between CouchDB and Redis
- Full join and Inner join in MS SQL Server
- Difference between Impala and MongoDB
- Difference between Couchbase and Redis
- Result Serializability in DBMS
- Difference between Oracle and Cassandra
- Difference between Hive and Derby
- Difference between Redis and MariaDB
- Difference between ROLAP, MOLAP and HOLAP
- Difference between RDBMS and IBM DB2
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.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.