All Products
Search
Document Center

SELECT INTO

Last Updated: Jun 18, 2021

The SELECT INTO operator assigns the query result to a list of variables. Only one row of data is returned for the query.

In the following query example, the SELECT statement outputs the COUNT(*) and MAX(c1) columns. The query results are assigned to variables @a and @b, respectively.

obclient>CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(1,1);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(2,2);
Query OK, 1 rows affected (0.12 sec)

obclient>EXPLAIN SELECT COUNT(*), MAX(c1) INTO @a, @b FROM t1\G;
*************************** 1. row ***************************
Query Plan:
=========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST|
-----------------------------------------
|0 |SELECT INTO     |    |0        |37  |
|1 | SCALAR GROUP BY|    |1        |37  |
|2 |  TABLE SCAN    |t1  |2        |37  |
=========================================
Outputs & filters: 
-------------------------------------
  0 - output([T_FUN_COUNT(*)], [T_FUN_MAX(t1.c1)]), filter(nil)
  1 - output([T_FUN_COUNT(*)], [T_FUN_MAX(t1.c1)]), filter(nil), 
      group(nil), agg_func([T_FUN_COUNT(*)], [T_FUN_MAX(t1.c1)])
  2 - output([t1.c1]), filter(nil), 
      access([t1.c1]), partitions(p0)

In the preceding example, the Outputs & filters section in the execution plan display shows in detail the output information of the SELECT INTO operator.

Field

Description

output

The expressions by which the operator assigns values to the list of variables.

filter

The filter conditions of the operator.

In this example, the condition is set to nil because no filter is configured for the SELECT INTO operator.