LATERAL VIEW clause
Applies to: Databricks SQL Databricks Runtime
Used in conjunction with generator functions such as EXPLODE
, which generates a virtual table containing one or more rows. LATERAL VIEW
applies the rows to each original output row.
In Databricks SQL and starting with Databricks Runtime 12.2 this clause is deprecated. You should invoke a table valued generator function as a table_reference.
Syntax
LATERAL VIEW [ OUTER ] generator_function ( expression [, ...] ) [ table_identifier ] AS column_identifier [, ...]
Parameters
OUTER
If
OUTER
specified, returns null if an input array/map is empty or null.-
A generator function (EXPLODE, INLINE, etc.).
-
The alias for
generator_function
, which is optional. -
Lists the column aliases of
generator_function
, which may be used in output rows. The number of column identifiers must match the number of columns returned by the generator function.
Examples
> CREATE TABLE person (id INT, name STRING, age INT, class INT, address STRING);
> INSERT INTO person VALUES
(100, 'John', 30, 1, 'Street 1'),
(200, 'Mary', NULL, 1, 'Street 2'),
(300, 'Mike', 80, 3, 'Street 3'),
(400, 'Dan', 50, 4, 'Street 4');
> SELECT * FROM person
LATERAL VIEW EXPLODE(ARRAY(30, 60)) tableName AS c_age
LATERAL VIEW EXPLODE(ARRAY(40, 80)) AS d_age;
id name age class address c_age d_age
------ ------- ------- -------- ----------- -------- --------
100 John 30 1 Street 1 30 40
100 John 30 1 Street 1 30 80
100 John 30 1 Street 1 60 40
100 John 30 1 Street 1 60 80
200 Mary NULL 1 Street 2 30 40
200 Mary NULL 1 Street 2 30 80
200 Mary NULL 1 Street 2 60 40
200 Mary NULL 1 Street 2 60 80
300 Mike 80 3 Street 3 30 40
300 Mike 80 3 Street 3 30 80
300 Mike 80 3 Street 3 60 40
300 Mike 80 3 Street 3 60 80
400 Dan 50 4 Street 4 30 40
400 Dan 50 4 Street 4 30 80
400 Dan 50 4 Street 4 60 40
400 Dan 50 4 Street 4 60 80
> SELECT c_age, COUNT(1) FROM person
LATERAL VIEW EXPLODE(ARRAY(30, 60)) AS c_age
LATERAL VIEW EXPLODE(ARRAY(40, 80)) AS d_age
GROUP BY c_age;
c_age count(1)
-------- -----------
60 8
30 8
SELECT * FROM person
LATERAL VIEW EXPLODE(ARRAY()) tableName AS c_age;
id name age class address c_age
----- ------- ------ -------- ---------- --------
> SELECT * FROM person
LATERAL VIEW OUTER EXPLODE(ARRAY()) tableName AS c_age;
id name age class address c_age
------ ------- ------- -------- ----------- --------
100 John 30 1 Street 1 NULL
200 Mary NULL 1 Street 2 NULL
300 Mike 80 3 Street 3 NULL
400 Dan 50 4 Street 4 NULL