Basic SELECT Statements¶
Function¶
This statement is a basic query statement and is used to return the query results.
Syntax¶
SELECT [ALL | DISTINCT] attr_expr_list FROM table_reference
[WHERE where_condition]
[GROUP BY col_name_list]
[ORDER BY col_name_list][ASC | DESC]
[CLUSTER BY col_name_list | DISTRIBUTE BY col_name_list]
[SORT BY col_name_list]]
[LIMIT number];
Keywords¶
Parameter | Description |
|---|---|
ALL | Returns duplicate rows. By default, all repeated rows are returned. It is followed by asterisks ( |
DISTINCT | Removes duplicate rows from the result set. |
WHERE | Specifies the filter criteria for a query. Arithmetic operators, relational operators, and logical operators are supported. |
where_condition | Filter criteria. |
GROUP BY | Specifies the grouping field. Single-field grouping and multi-field grouping are supported. |
col_name_list | Field list |
ORDER BY | Sort the query results. |
ASC/DESC | ASC sorts from the lowest value to the highest value. DESC sorts from the highest value to the lowest value. ASC is the default sort order. |
CLUSTER BY | CLUSTER BY is used to bucket the table according to the bucketing fields and then sort within the bucketed table. If the field of DISTRIBUTE BY is the same as the field of SORT BY and the sorting is in descending order, the combination of DISTRIBUTE BY and SORT BY achieves the same function as CLUSTER BY. |
DISTRIBUTE BY | Specifies the bucketing fields without sorting the table. |
SORT BY | The objects will be sorted in the bucket. |
LIMIT | LIMIT is used to limit the query results. Only INT type is supported by the number parameter. |
Precautions¶
The table to be queried must exist. Otherwise, an error is reported.
Example¶
To filter the record, in which the name is Mike, from the student table and sort the results in ascending order of score, run the following statement:
SELECT * FROM student
WHERE name = 'Mike'
ORDER BY score;