Help Center/ Data Lake Insight/ SQL Syntax Reference (Kuala Lumpur Region)/ Spark SQL Syntax Reference/ Basic SELECT Statements
Updated on 2023-03-06 GMT+08:00
View PDF
Share

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

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:

1
2
3
 
SELECT * FROM student
  WHERE name = 'Mike'
  ORDER BY score;