docs/query-data/lateral-view.md

---
{
    "title": "Lateral View",
    "language": "en",
    "description": "Doris LATERAL VIEW works with generator functions such as EXPLODE to expand a single row into multiple rows, enabling SQL column-to-row queries.",
    "keywords": [
        "Doris LATERAL VIEW",
        "column to row",
        "EXPLODE",
        "row to column",
        "array expansion",
        "lateral view explode",
        "SQL one row to many rows"
    ]
}
---

<!-- Knowledge type: Syntax reference -->
<!-- Applicable scenario: Array/collection type expansion queries, column to row -->

`LATERAL VIEW` is the column-to-row syntax provided by Doris. Combined with generator functions (such as `EXPLODE`), it expands a collection-typed field in a row into multiple rows and joins the expanded result, treated as a virtual table, with the original row.

## Applicable Scenarios

When a row of data contains an enumerable collection such as an array or list, and you want to split each element of the collection into a separate row for analysis in a SQL query, you can use `LATERAL VIEW`. Typical scenarios include:

- Expanding an array field in a row and outputting each element together with the other original columns.
- Performing aggregation, filtering, or joining with other tables on each element of the collection separately.
- Using the output of a generator function (such as `EXPLODE`, `EXPLODE_SPLIT`) as a virtual table in a query.

## Syntax

```sql
LATERAL VIEW generator_function ( expression [, ...] ) table_identifier AS column_identifier [, ...]
```

## Parameters

| Parameter | Description |
| --- | --- |
| `generator_function` | A generator function, such as `EXPLODE` or `EXPLODE_SPLIT`. |
| `table_identifier` | The alias of the virtual table produced by `generator_function`. |
| `column_identifier` | The column alias used to name the output rows. The number of column aliases must match the number of columns returned by the generator function. |

## Usage Example

The following example shows how to use `LATERAL VIEW` to perform a column-to-row query.

### 1. Prepare the Data

Create a `person` table and insert several rows of test data:

```sql
CREATE TABLE `person` (
  `id` int(11) NULL,
  `name` text NULL,
  `age` int(11) NULL,
  `class` int(11) NULL,
  `address` text NULL
) ENGINE=OLAP
UNIQUE KEY(`id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2",
"disable_auto_compaction" = "false"
);

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');
```

### 2. Run the LATERAL VIEW Query

Use `LATERAL VIEW` together with the `EXPLODE` function to perform a Cartesian expansion of each element in the array `ARRAY(30, 60)` with each row of the `person` table:

```sql
SELECT * FROM person
LATERAL VIEW EXPLODE(ARRAY(30, 60)) tableName AS c_age;
```

### 3. View the Result

The query result contains the combination of each row of the `person` table with each row generated by `EXPLODE`:

```sql
+------+------+------+-------+----------+-------+
| id   | name | age  | class | address  | c_age |
+------+------+------+-------+----------+-------+
|  100 | John |   30 |     1 | Street 1 |    30 |
|  100 | John |   30 |     1 | Street 1 |    60 |
|  200 | Mary | NULL |     1 | Street 2 |    30 |
|  200 | Mary | NULL |     1 | Street 2 |    60 |
|  300 | Mike |   80 |     3 | Street 3 |    30 |
|  300 | Mike |   80 |     3 | Street 3 |    60 |
|  400 | Dan  |   50 |     4 | Street 4 |    30 |
|  400 | Dan  |   50 |     4 | Street 4 |    60 |
+------+------+------+-------+----------+-------+
8 rows in set (0.12 sec)
```