SQL query syntax

SQL query statements

• The SELECT statement retrieves data from a table. All advanced query syntax is built around SELECT. For more information, see SELECT.

• OpenSearch does not currently support summary retrieval. For more information, see FROM-Summary query.

• The WHERE clause filters data from a table based on specified conditions. For more information, see WHERE.

• The GROUP BY clause aggregates data from one or more fields to calculate values, such as the maximum, minimum, and average. For more information, see GROUP BY.

• The ORDER BY clause sorts data based on one or more fields. The default sort order is ascending (ASC). Because sorting can negatively impact performance, you must use a LIMIT clause with an ORDER BY clause. For more information, see ORDER BY.

• The UNION statement merges data from multiple ingest endpoints that have the same schema. The fields, data types, and order of fields must be identical for each statement. For more information, see UNION.

• The JOIN clause combines data from multiple tables to expand the query dimensions. For more information, see JOIN operation types.

• Dynamic parameters work like the PreparedStatement interface in a database. You use question mark (?) placeholders in your SQL statement and pass the corresponding values, which the engine substitutes automatically. Dynamic parameters improve the cache hit rate and can significantly boost performance for fixed query patterns. For more information, see Dynamic parameters.

• A Table Value Function (TVF) in TuringSQL takes one or more scalar parameters and an SQL query as input, treats the query as a table, and returns a table as output. For more information, see Table Value Function.

• A Common Table Expression (CTE) defines a temporary, named result set available within a single query. You can reference the CTE in subsequent parts of the query to simplify its structure. Currently, only SELECT statements are supported. For more information, see Introduction to Common Table Expressions.

Limits

• Data Definition Language (DDL) and Data Manipulation Language (DML) statements are not currently supported.

• The ORDER BY clause must be used with a LIMIT clause.

• SELECT * is not supported when you use the UNNEST clause to query a child table.

• JOIN operations are currently executed only on the Searcher. You must ensure proper data distribution.

• For a UNION operation, the number of fields, field names, and data types must be identical in all SELECT statements.

• Numeric analysis indexes do not support queries that use the equality operator (=). Instead, you must use a range query, such as WHERE QUERY('consign_time', '[1, 10]').