This topic describes how to use the WITH clause in a SELECT statement.
Purpose
If a query statement contains multiple identical subqueries, you can place the identical subqueries in the WITH CLAUSE as common expressions and directly 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 name of the column. |
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
/*Directly use the column names of table tbl1 as the column names of w_tbl1.*/
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
/*Directly specify column names for w_tbl1.*/
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