Tableless Query

1. Overview

Tableless query allows you to execute SQL statements without a FROM clause, without relying on any actual database table. It retrieves computed results, constant values, or system information, and is mainly used in scenarios such as expression evaluation, system function calls, and constant queries.

Note: This feature is supported since V2.0.11.

2. Syntax

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

Where:

• expression: the expression to be evaluated. It can be a constant, a mathematical function, a string function, a date-time function, or an aggregate function. Currently, only functions that take no arguments or constant arguments only are supported.

• column_alias: an optional alias for the result column, which improves the readability of query results. If no alias is specified, the system automatically generates column names such as _col0, _col1, etc.

• SELECT * is not supported.

• When executing SELECT COUNT(*);, 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|
+-----+-----+-----+-----+