Spark SQL 指南

橫向子查詢

說明

橫向子查詢 是由關鍵字 橫向 前置的子查詢。它提供一種方法來參照前一個 FROM 子句中的欄位。沒有 橫向 關鍵字，子查詢只能參照外部查詢中的欄位，但不能參照 FROM 子句中的欄位。 橫向子查詢 使得複雜的查詢更簡單、更有效率。

語法

[ 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   |
+-------+--------+--------+