Palabra clave lateral en SQL

La palabra clave lateral representa una unión lateral entre dos o más tablas. Une la salida de la consulta externa con la salida de la subconsulta lateral subyacente. Es como un bucle for-each en SQL donde la subconsulta itera a través de cada fila de la tabla en cuestión, evaluando la subconsulta para cada fila. 

Las filas de salida devueltas por la subconsulta interna se agregan al resultado de la combinación con la consulta externa. Sin Lateral, cada subconsulta se evaluaría de forma independiente y no podría hacer referencia a los elementos de la tabla a la que se hace referencia en la consulta externa. 

Sintaxis de Lateral: 
una unión lateral se indica mediante la palabra clave Lateral que precede a la subconsulta interna, como se muestra a continuación: 

SELECT <Column Name>
FROM <Reference Table Name>
LATERAL <Inner Subquery> 

Ejemplo: 
supongamos que tenemos que encontrar los 3 mejores estudiantes de una clase con las calificaciones más altas. La consulta sería simple de la siguiente manera: 

SELECT studId, marks 
FROM student 
ORDER BY marks DESC FETCH FIRST 3 ROWS ONLY 

Ahora, asumiendo que cada clase tiene ‘n’ secciones y necesitamos encontrar los 3 mejores estudiantes por sección con las calificaciones más altas. Ahora tendríamos que unir las tablas de secciones para obtener el resultado y encontrar los 3 mejores estudiantes usando la función Rank(). La consulta seria asi: 

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 

Aquí es donde Lateral viene al rescate. Usaremos nuestra primera consulta donde buscamos a los 3 mejores estudiantes con las calificaciones más altas como la subconsulta interna. A continuación, unimos la tabla Sección con la subconsulta interna usando la palabra clave Lateral. La consulta interna que está a la derecha de Lateral se evaluaría para cada fila en la tabla de la izquierda. Así es como se vería la consulta: 

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) 

¿Por qué usar laterales?  
En términos simples, Lateral proporciona un enfoque más simple y limpio para devolver más de una columna como salida. Aunque dado que la subconsulta interna tiene que ejecutarse para cada fila de la consulta principal, hace que la consulta sea un poco lenta. Algunas aplicaciones importantes de la palabra clave Lateral son la agregación de dos o más tablas y en los registros de actividad donde el registro puede requerir una gran cantidad de datos temporales.