This topic describes SELECT statements with a WITH clause.
Purpose
If multiple identical subqueries appear in one query, you can use a WITH clause as a common table expression (CTE) to reference them in the main query.
Syntax
with_clause_select:
with_clause simple_select
with_clause:
WITH query_table_name [opt_column_alias_name_list] AS ( select_clause )
select_clause:
simple_select | select_clause_set
opt_column_alias_name_list:
(column_name_list)
column_name_list:
column_name [,column_name...]
Parameters
| Parameter | Description |
|---|---|
| query_table_name | The name of the subquery. |
| column_name | The column name. |
Examples
obclient> CREATE TABLE tbl1(col1 INT,col2 INT,col3 INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES(1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
/*Use the column names of the tbl1 table as the column names of the w_tbl1 table.*/
obclient> WITH w_tbl1 AS(SELECT * FROM tbl1) SELECT * FROM w_tbl1;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+------+------+------+
3 rows in set
/*Name the columns of the w_tbl1 table.*/
obclient> WITH w_tbl1(c1,c2,c3) AS(SELECT * FROM tbl1) SELECT * FROM w_tbl1;
+------+------+------+
| C1 | C2 | C3 |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+------+------+------+
3 rows in set