Help Center> Data Lake Insight> SQL Syntax Reference> SQL Syntax of Batch Jobs> Basic SELECT Statements
View PDF

Basic SELECT Statements

Function

This statement is a basic query statement and is used to return the query results.

Syntax

1
2
3
4
5
6
7
 
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];

Keyword

Table 1 SELECT parameter description

Parameter

Description

ALL

Returns duplicate rows. By default, all repeated rows are returned. It is followed by asterisks (*) only. Otherwise, an error will occur.

DISTINCT

Removes duplicate rows from the result set.

WHERE

WHERE is used to specify the filtering condition, which can be the arithmetic operator, relational operator, or logical operator.

where_condition

Filtering criteria.

GROUP BY

GROUP BY is used to specify the grouping field, which can be a single field or multiple fields.

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

DISTRIBUTE BY is used to specify 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 to-be-queried table 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:

1
2
3
 
SELECT * FROM student
  WHERE name = 'Mike'
  ORDER BY score;
Parent topic: SQL Syntax of Batch Jobs