SQL query syntax reference: SELECT, aggregate functions, and joins - Tablestore - Tablestore

Prerequisites

Create a mapping relationship before running SELECT statements. For more information, see DDL operations.

Query data

Use SELECT statements to retrieve rows from mapping tables with optional filtering, grouping, sorting, and pagination.

Note

Search index mapping tables support additional capabilities such as full-text search, array queries, nested queries, vector search, and JSON functions. For more information, see Search index operations.
When both secondary indexes and search indexes exist on a data table, the SQL engine automatically selects the most appropriate index. For more information, see Query optimization.
Clause execution order: WHERE > GROUP BY > HAVING > ORDER BY > LIMIT/OFFSET.

Syntax

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    select_expr [, select_expr] ...
    [FROM table_references | join_expr]
    [WHERE where_condition]
    [GROUP BY groupby_condition]
    [HAVING having_condition]
    [ORDER BY order_condition]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Parameters

Parameter	Required	Description
ALL \| DISTINCT \| DISTINCTROW	No	Deduplication mode. ALL (default) returns all rows. DISTINCT removes duplicate rows from the result set. DISTINCTROW is equivalent to DISTINCT.
select_expr	Yes	Column names or expressions in the format `column_name \| expression [AS alias]`. Use `*` to return all columns.
table_references	Yes	Target table. Specify a table name or a SELECT subquery in the format `table_name \| select_statement`. For multi-table queries, see the Join section.
where_condition	No	WHERE clause. Supports primary key equality and range conditions, logical operators (AND/OR/NOT), comparison operators (=, >, <, >=, <=, !=), IN, LIKE, IS NULL, and BETWEEN.
groupby_condition	No	GROUP BY clause. Groups rows by specified columns, typically used with aggregate functions.
having_condition	No	HAVING clause. Filters grouped results produced by GROUP BY.
order_condition	No	ORDER BY clause. Sorts results by specified columns. Supports ASC (ascending, default) and DESC (descending).
LIMIT / OFFSET	No	Limits the number of returned rows. `LIMIT row_count` returns the first N rows. `LIMIT offset, row_count` or `LIMIT row_count OFFSET offset` skips offset rows and then returns N rows.

Examples

Query all data from exampletable, returning up to 20 rows:

SELECT * FROM exampletable LIMIT 20;

Query with conditions and sorting:

SELECT pk, col_long, col_keyword FROM exampletable WHERE col_long > 100 ORDER BY col_long DESC LIMIT 10;

Deduplicate results:

SELECT DISTINCT col_keyword FROM exampletable;

Group and count:

SELECT col_keyword, COUNT(*) AS cnt FROM exampletable GROUP BY col_keyword HAVING cnt > 1;

Paginate results (skip the first 10 rows and return 5 rows):

SELECT * FROM exampletable LIMIT 10, 5;

Aggregate functions

Aggregate functions compute a single result from multiple rows. Use them with GROUP BY to produce grouped statistics.

Function	Return type	Description
COUNT()	BIGINT	Returns the number of rows that match a specified condition. `COUNT(*)` counts all rows. `COUNT(column)` counts only non-NULL values.
COUNT(DISTINCT)	BIGINT	Returns the number of distinct values in the specified column.
SUM()	DOUBLE	Returns the sum of a numeric column.
AVG()	DOUBLE	Returns the average of a numeric column.
MAX()	Same as column type	Returns the maximum value in a column.
MIN()	Same as column type	Returns the minimum value in a column.

Examples

SELECT COUNT(*) FROM exampletable;
SELECT SUM(col_long), AVG(col_long) FROM exampletable;
SELECT col_keyword, COUNT(*) AS cnt, MAX(col_long) FROM exampletable GROUP BY col_keyword;

Join

Join two or more tables to combine rows based on matching column values.

Syntax

table_references join_type table_references [ ON join_condition | USING ( join_column [, ...] ) ]

table_references : {
  table_name [ [ AS ] alias_name ]
  | select_statement
}

join_type : {
  [ INNER ] JOIN
  | LEFT [ OUTER ] JOIN
  | RIGHT [ OUTER ] JOIN
  | CROSS JOIN
}

Parameters

Parameter	Required	Description
table_references	Yes	Tables to join. Specify a table name with an optional alias, or a SELECT subquery. The table on the left side of the JOIN keyword is the left table, and the table on the right side is the right table.
join_type	Yes	Join type: INNER JOIN (default): Returns only rows that satisfy the join condition in both tables. LEFT OUTER JOIN: Returns all rows from the left table. Fills NULL for columns where no match exists in the right table. RIGHT OUTER JOIN: Returns all rows from the right table. Fills NULL for columns where no match exists in the left table. CROSS JOIN: Returns the Cartesian product of both tables.
join_condition	Yes	Defines the join columns. Use `ON` to specify the condition, or `USING(column)` when both tables share identically named columns. The following two statements are equivalent: `SELECT * FROM t1 JOIN t2 ON t1.col_a = t2.col_a AND t1.col_b = t2.col_b; SELECT * FROM t1 JOIN t2 USING (col_a, col_b);`

Join algorithms

Tablestore uses INDEX JOIN by default and falls back to HASH JOIN when the join columns of the right table do not satisfy index conditions.

Algorithm

Applicable condition

Description

INDEX JOIN

Join columns of the right table satisfy index conditions

Reads data from the left table and uses the index or primary key of the right table to look up matching rows. The join columns of the right table must meet one of the following conditions:

When accessed through a primary key or secondary index, the join columns satisfy leftmost prefix matching.
When accessed through a search index, all join columns are indexed in the search index and filter conditions can be pushed down.

HASH JOIN

Join columns do not satisfy INDEX JOIN conditions

Builds a hash table from the left table, then probes it with rows from the right table to find matches. No index required.

Note

Without a suitable index on the right table, INDEX JOIN degrades to a full table scan. Add indexes on join columns and filter columns to improve query performance.
INNER JOIN performs better for smaller result sets, while HASH JOIN performs better for larger result sets. Place the smaller table on the left side for better performance.

Examples

Assume two tables, orders and customers:

-- orders table
+----------+-------------+------------+--------------+
| order_id | customer_id | order_date | order_amount |
+----------+-------------+------------+--------------+
|     1001 |           1 | 2023-01-01 |           50 |
|     1002 |           2 | 2023-01-02 |           80 |
|     1003 |           3 | 2023-01-03 |          180 |
|     1004 |           4 | 2023-01-04 |          220 |
|     1005 |           6 | 2023-01-05 |          250 |
+----------+-------------+------------+--------------+

-- customers table
+-------------+---------------+----------------+
| customer_id | customer_name | customer_phone |
+-------------+---------------+----------------+
|           1 |         Alice |    11111111111 |
|           2 |           Bob |    22222222222 |
|           3 |         Carol |    33333333333 |
|           4 |         David |    44444444444 |
|           5 |           Eve |    55555555555 |
+-------------+---------------+----------------+

INNER JOIN returns only rows where customer_id matches in both tables. Order 1005 (customer_id=6) is excluded because no matching customer exists.

SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
-- Equivalent:
SELECT * FROM orders JOIN customers USING(customer_id);

LEFT JOIN returns all rows from the left table (orders). Columns from the right table are filled with NULL when no match exists.

SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id;

CROSS JOIN returns the Cartesian product of both tables.

SELECT * FROM orders CROSS JOIN customers;