How to execute a SELECT statement to query data - Tablestore

This topic describes how to execute a SELECT statement to query data in a table.

Preparations

If you want to query data in a table, create a mapping table for the table. For more information, see Create mapping tables for tables. If you want to query data by using a search index, create a mapping table for the search index. For more information, see Create mapping tables for search indexes.

Usage notes

Clauses in a SELECT statement are executed in the following order: WHERE > GROUP BY > HAVING > ORDER BY > LIMIT and 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	Specifies whether to remove duplicate field values. Default value: ALL. Valid values: ALL: returns all values of the specified fields, including duplicate values. DISTINCT: removes duplicate values and returns only the distinct values of fields. DISTINCTROW: removes rows that contain duplicate values and returns rows that contain only distinct values of fields.
select_expr	Yes	The column names or expressions in the `column_name[, column_name][, column_exp],..` format. For more information, see Column expression (select_expr).
table_references	Yes	The information about the table whose data you want to query. The value of this parameter can be a table name or a SELECT statement in the `table_name \| select_statement` format. For more information, see Table information (table_references).
join_expr	No	The JOIN expression that is used to join tables. The format is `table_references join_type table_references [ ON join_condition \| USING ( join_column [, ...] ) ]`. This parameter is required only if you want to use the JOIN function. The JOIN function allows you to join two or more tables and returns data that meets the join and query conditions. For more information, see Join.
where_condition	No	The WHERE clause that can be used to specify different conditions to implement specific features. You can use relational operators in the WHERE clause to query the data that meets specified conditions. The format is `column_name operator value [AND \| OR] [column_name operator value]`. For more information, see WHERE clause (where_condition). You can specify the conditions of match query or match phrase query in the WHERE clause to perform full-text search. For more information, see Full-text search. You can use the `ARRAY_EXTRACT(col_name)` function in the WHERE clause to query data of the ARRAY type that is supported in search indexes. For more information, see ARRAY supported in search indexes. `col_name` in the function specifies the name of the ARRAY field to query. You can use operators or the `NESTED_QUERY(subcol_column_condition)` function in the WHERE clause to query data of the NESTED type that is supported in search indexes. For more information, see NESTED supported in search indexes. `subcol_column_condition` in the function specifies the condition for querying data of child fields at the same nesting level. You can use virtual columns in the WHERE clause to query the data that meets the specified conditions. For more information, see Virtual columns of search indexes.
groupby_condition	No	The GROUP BY clause that can be used with aggregate functions. The format is `column_name`. For more information, see GROUP BY clause (groupby_condition).
having_condition	No	The HAVING clause that can be used with aggregate functions. The format is `aggregate_function(column_name) operator value`. For more information, see HAVING clause (having_condition).
order_condition	No	The ORDER BY clause in the `column_name [ASC \| DESC][,column_name [ASC \| DESC],...]` format. For more information, see ORDER BY clause (order_condition).
row_count	No	The maximum number of rows to return in the query.
offset	No	The data that is skipped in the query. Default value: 0.

Column expression (select_expr)

You can use select_expr to specify the columns that you want to query. When you use select_expr, take note of the following items:

You can use the wildcard (*) to query all columns. You can also use the WHERE clause to specify the query condition.
```
SELECT * FROM orders;
```
The following example shows how to use the WHERE clause to specify a query condition:
```
SELECT * FROM orders WHERE orderprice >= 100;
```
You can use a column name to specify the column that you want to query.
```
SELECT username FROM orders;
```
You can use JSON functions to query JSON objects.
```
SELECT coljson, coljson->>'$.a' AS subdoc FROM json_table WHERE pkint = 1;
```
For more information, see JSON functions.

Table information (table_references)

You can use table_references to specify the table whose data you want to query.

SELECT orderprice FROM orders;

WHERE clause (where_condition)

You can use where_condition to query data that meets the specified conditions. When you use where_condition, take note of the following items:

You can use simple expressions that are constructed by using operators such as arithmetic operators or relational operators.
```
SELECT * FROM orders WHERE username = 'lily';
SELECT * FROM orders WHERE orderprice >= 100;
```
You can use combined expressions that are constructed by using logical operators.
```
SELECT * FROM orders WHERE username = 'lily' AND orderprice >= 100;
```

For more information about operators, see SQL operators.

GROUP BY clause (groupby_condition)

You can use groupby_condition to group the row data in the result set of a SELECT statement based on the specified condition. When you use groupby_condition, take note of the following items:

You can group row data by field.

SELECT username FROM orders GROUP BY username;

You can use aggregate functions on grouped columns.

SELECT username,COUNT(*) FROM orders GROUP BY username;

You must add the columns on which no aggregate functions are run in the SELECT statement to the GROUP BY clause.
```
SELECT username,orderprice FROM orders GROUP BY username,orderprice;
```

For more information about aggregate functions, see Aggregate functions.

HAVING clause (having_condition)

You can use having_condition to filter the row data that you grouped in the result sets that are obtained by using the WHERE and GROUP BY clauses. The row data that you grouped is filtered based on specified conditions.

In most cases, the HAVING clause is used together with aggregate functions to filter data.

SELECT username,SUM(orderprice) FROM orders GROUP BY username HAVING SUM(orderprice) < 500;

ORDER BY clause (order_condition)

You can use order_condition to sort the row data in the result set of a query based on the specified field and sorting method. When you use having_condition, take note of the following items:

You can use the ASC or DESC keyword to specify the sorting method. By default, the data is sorted in ascending order (ASC).
```
SELECT * FROM orders ORDER BY orderprice DESC LIMIT 10;
```
You can specify multiple fields based on which you want to sort data.
```
SELECT * FROM orders ORDER BY username ASC,orderprice DESC LIMIT 10;
```
You can use LIMIT to limit the number of rows to return.
```
SELECT * FROM orders ORDER BY orderprice LIMIT 10;
```