Lateral Thinking Just Like Lateral Function

Lateral Join in SQL

I have been reading SQL interview Questions Lately and encountered that frequently asked question is:-

Find the Top 3 Highest salaried Employees.

It is simple for an experienced Database Developer to answer the question.

One Answer for this question would be something like:

SELECT empno,salary FROM employee ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY

Usually the counter question after you answered this question is to

Find Top 3 Highest salaried Employees for each Department.

Now we all know that we would need to join seperate department table to get the results.

One Answer can be to rank the employees according to their salary for each department using Rank() function and then displaying all employees with rank less than or equal to 3.

The Query would be:-

SELECT deptno,empno,salary FROM 
(SELECT dp.deptno,emp.empno,emp.salary,RANK() OVER (PARTITION BY dp.deptno ORDER BY salary DESC) rn 
FROM 
employee emp, department dp WHERE dp.deptno = emp.deptno
)
WHERE rn <= 3

Lately i encountered Lateral Join.

Lateral Join is more like a correlated subquery.

Our First Query where we fetched top 3 employees with highest salary would be our correlated subquery.

We will join the Department Table with the correlated subquery using Lateral function.

Lateral function returns a table.

The query to the right of the Lateral would be evaluated for every row of the left table.

Here is the query:-

SELECT dp.deptno,emp.empno,emp.salary FROM 
department dp,
LATERAL( 
SELECT empno,salary FROM employee emp WHERE emp.deptno = dp.deptno ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY
)

TABLE keyword is a synonym to LATERAL keyword means you can use TABLE in the query instead of LATERAL.

1. LATERAL keyword is also known as lateral correlation.
2. As said earlier that query in right of the LATERAL will run for every row of the table on left.Query Optimizer can optimize use of LATERAL.