SQL is the standard language for managing relational databases, and queries are at its core. This topic describes the core syntax concepts of SQL queries.
SQL queries
The SELECT statement is used to select data from a table. The SELECT statement is the core of the query syntax, and all advanced query syntax is built around it. For more information, see SELECT.
OpenSearch does not currently support retrieving summaries. For more information, see FROM - Summary query.
The WHERE statement is used to filter data that meets specified conditions from a table. For more information, see WHERE.
GROUP BY is used to aggregate data by one or more fields to calculate values such as the maximum, minimum, and average. For more information, see GROUP BY.
The ORDER BY statement is used to sort data by one or more fields. The default order is ascending (ASC). Because sorting performance is poor, a LIMIT clause is required when you sort data. For more information, see ORDER BY.
The UNION statement is used to merge multiple data streams that have the same schema. The fields on both sides must be exactly the same, including field types and field order. For more information, see UNION.
The JOIN statement is used to combine data from multiple tables to extend query dimensions. For more information, see JOIN operation types.
Dynamic parameters are similar to PrepareStatement in databases. You can set placeholders (represented by ?) in SQL and pass the corresponding values for the placeholders. The engine automatically replaces the placeholders internally. Dynamic parameters are mainly used to improve the cache hit ratio and significantly improve performance in scenarios with fixed query patterns. For more information, see Dynamic parameters.
In TuringSQL, a table-valued function (TVF) represents a set of functions that can operate on an entire table. The input of a TVF consists of one or more scalar parameters and an SQL statement (which can be regarded as a table), and the output is a table. For more information, see Table-Valued Functions.
A common table expression (CTE) lets you define a temporary named result set that exists for the lifetime of the query. Subsequent statements in the query can then reference the CTE directly to simplify the query. CTEs are not limited to SELECT statements, but only SELECT statements are currently supported. For more information, see Common Table Expressions.
Limits
DDL and DML syntax is not currently supported.
The ORDER BY clause must be used together with LIMIT.
SELECT * is not supported when you use the UNNEST clause to query sub-tables.
JOIN can currently be executed only on Searchers. You must ensure proper data distribution for your business.
For UNION, the field names, field types, and number of fields on both sides must be the same.
Numeric analysis indexes do not support queries that use "=". Instead, you can use a query such as WHERE QUERY('consign_time', '[1, 10]').