Open In App

Lateral Keyword in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

The lateral keyword represents a lateral join between two or more tables. It joins the output of the outer query with the production 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 independently of each other and could not refer to the items in the table referenced in the outer query. 

Why Use Lateral?

In simple terms, Lateral provides a simpler and cleaner approach for returning more than one column as the output. However 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 keywords are aggregation of two or more tables and activity logs where logging might require a lot of temporary data.

Syntax of Lateral Join

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>

Example1: 
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)

Example2:

Let us assume that we have two tables Students and Courses so there is a query for Students Table-

CREATE TABLE Students (student_id INT, name VARCHAR);
INSERT INTO Students (student_id, name) VALUES
(1, 'Mohit'),
(2, 'Ritik');

Output

Student Table

Student Table

Query

CREATE TABLE Courses (course_ID INT, course_name VARCHAR, 
stu_Allocated_course_ID INT, project_names ARRAY);
INSERT INTO Courses (course_ID,course_name,stu_Allocated_course_ID,project_names) VALUES
(111,'Machine Learning',1,'Face detector'),
(112,'Big data',1,'Traffic control'),
(113,'Cloud Computing',2,'Web application');

Output

Courses table

Courses Table

So,there is a example using LATERAL JOIN with a subquery by using both the tables Student and courses-

SELECT * 
FROM Students AS s, LATERAL (SELECT * FROM courses AS c WHERE c.stu_Allocated_course_ID = s.student_id) AS iv2
ORDER BY course_ID;

Output

student_id

Name

course_ID

course_name

stu_Allocated_course_ID

Project_name

1

Mohit

111

Machine Learning

1

Face detector

1

Mohit

112

Big data

1

Traffic control

2

Ritik

113

Cloud Computing

2

Web application

Conclusion

  • A lateral join, a kind of join that lets the subquery access the columns of the outer query, is made using the lateral keyword.
  • Aggregations, correlated searches, and other actions that would be challenging or impossible to conduct with a typical join can be accomplished via lateral joins.

Last Updated : 04 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads