Spark SQL 指南

LATERAL 子查询

描述

LATERAL 子查询 是一个子查询，前面带有关键字 LATERAL。它提供了一种引用前面 FROM 子句中列的方法。如果没有 LATERAL 关键字，子查询只能引用外部查询中的列，而不能引用 FROM 子句中的列。LATERAL 子查询 使复杂的查询更简单、更高效。

语法

[ LATERAL ] primary_relation [ join_relation ]

参数

示例

CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (0, 1), (1, 2);

CREATE TABLE t2 (c1 INT, c2 INT);
INSERT INTO t2 VALUES (0, 2), (0, 3);

SELECT * FROM t1,
  LATERAL (SELECT * FROM t2 WHERE t1.c1 = t2.c1);
+--------+-------+--------+-------+
|  t1.c1 | t1.c2 |  t2.c1 | t2.c2 |
+-------+--------+--------+-------+
|    0   |   1   |    0   |   3   |
|    0   |   1   |    0   |   2   |
+-------+--------+--------+-------+

SELECT a, b, c FROM t1,
  LATERAL (SELECT c1 + c2 AS a),
  LATERAL (SELECT c1 - c2 AS b),
  LATERAL (SELECT a * b AS c);
+--------+-------+--------+
|    a   |   b   |    c   |
+-------+--------+--------+
|    3   |  -1   |   -3   |
|    1   |  -1   |   -1   |
+-------+--------+--------+