Tableless Query

1. Feature Overview

Tableless queries allow SQL query statements without a FROM clause to be executed directly without relying on an actual database table. They return calculation results, constant values, or system information, and are mainly used in scenarios such as expression evaluation, system function calls, and constant queries.

Note: This feature is supported starting from V2.0.11.1.

2. Syntax

SELECT expression [ [ AS ] column_alias ]
    [, expression [ [ AS ] column_alias ] ]*;

Where:

• expression: The expression to evaluate. It can be a constant, mathematical function, string function, date and time function, or aggregate function. Currently, functions with no arguments or only constant arguments are supported.

• column_alias: An optional alias for the result column, used to improve the readability of query results. If no alias is specified, the system automatically generates column names such as _col0 and _col1.

• SELECT * is not supported.

• When SELECT COUNT(*); is executed, the result is always 1.

3. Examples

3.1 Constants and Mathematical Calculations

Query statement:

SELECT 100 + 50 AS sum_result,
  10 * 5 AS product,
  SQRT(144) AS square_root,
  ABS(-25) AS absolute_value,
  SIN(1) AS sin_value;

Result:

+----------+-------+-----------+--------------+------------------+
|sum_result|product|square_root|absolute_value|         sin_value|
+----------+-------+-----------+--------------+------------------+
|       150|     50|       12.0|            25|0.8414709848078965|
+----------+-------+-----------+--------------+------------------+

3.2 String Functions

Query statement:

SELECT
  CONCAT('Hello', ' ', 'World') AS greeting,
  UPPER('iotdb') AS uppercase,
  LOWER('IOTDB') AS lowercase,
  LENGTH('database') AS str_length;

Result:

+-----------+---------+---------+----------+
|   greeting|uppercase|lowercase|str_length|
+-----------+---------+---------+----------+
|Hello World|    IOTDB|    iotdb|         8|
+-----------+---------+---------+----------+

3.3 Date and Time Functions

Query statement:

SELECT NOW() AS now_time;

Result:

+-------------+
|     now_time|
+-------------+
|1784776724555|
+-------------+

3.4 Aggregate Functions

Query statement:

SELECT
  AVG(10),
  SUM(10),
  COUNT(10),
  COUNT(*);

Result:

+-----+-----+-----+-----+
|_col0|_col1|_col2|_col3|
+-----+-----+-----+-----+
| 10.0| 10.0|    1|    1|
+-----+-----+-----+-----+