SQL 中的 Lateral 关键字详解

Lateral 关键字表示两个或多个表之间的横向连接。它将外部查询的输出与底层横向子查询的结果连接在一起。这就像是 SQL 中的一个“for-each”循环，子查询会遍历相关表的每一行，并为每一行计算子查询的结果。

内部子查询返回的输出行随后会被添加到与外部查询的连接结果中。如果没有 Lateral，每个子查询将彼此独立评估，并且无法引用外部查询中引用的表里的项。

为什么要使用 Lateral？

简单来说，Lateral 为返回多列作为输出提供了一种更简单、更清晰的方法。然而，由于内部子查询必须针对主查询的每一行运行，这会使查询稍微变慢。Lateral 关键字的一些重要应用包括两个或多个表的聚合以及活动日志，在这些场景中，日志记录可能需要大量临时数据。

Lateral 连接的语法

Lateral 连接用关键字 Lateral 表示，该关键字位于内部子查询之前，如下所示：

> SELECT

>

> FROM

>

> LATERAL

示例1：

让我们假设我们需要找出一个班级中分数最高的前3名学生。该查询将是一个简单的查询，如下所示：

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

现在假设每个班级有 ‘n‘ 个部分，我们需要找出每个部分中分数最高的前3名学生。现在我们需要连接部分表来获取结果，并使用 Rank() 函数找出前3名学生。查询将如下所示：

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

这就是 Lateral 大显身手的地方。我们将使用我们获取分数最高的前3名学生的第一个查询作为内部子查询。接下来，我们使用 Lateral 关键字将 Section 表与内部子查询连接起来。Lateral 右侧的内部查询将针对左侧表中的每一行进行评估。查询如下所示：

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)

示例2：

让我们假设有两个表 Students（学生）和 Courses（课程），这里有一个针对 Students 表的查询：

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

输出

!Student TableStudent Table

查询

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‘);

输出

!Courses tableCourses Table

因此，这里有一个使用 LATERAL JOIN 和子查询结合 Student 和 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;

输出

coursename

Project_name

—

—

—

Mohit

Machine Learning

Face detector

Mohit

Big data

Traffic control

Ritik

Cloud Computing

Web application## 总结

• Lateral 关键字用于创建一种特殊的连接，它允许子查询访问外部查询的列。
• 通过 Lateral 连接，我们可以实现聚合、相关搜索以及其他操作，这些操作如果使用普通连接通常很难甚至无法完成。