DocsICP FilingConsole
官方文档
首页

SELECT

更新时间：
复制 MD 格式
产品详情
我的收藏

The SELECT statement queries data from one or more tables in PolarDB-X.

Syntax

SELECT
    [ALL | DISTINCT]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [FOR UPDATE]
ClauseDescription
select_exprSpecifies the columns to query. A SELECT statement must include at least one select_expr.
FROM table_referencesSpecifies the tables from which data is retrieved.
WHERE where_conditionFilters rows by the specified condition. If omitted, all rows are returned.
GROUP BYGroups results by column names, expressions, or output column positions. Does not support ASC or DESC.
HAVING where_conditionFilters grouped results. Unlike WHERE, aggregate functions are allowed.
ORDER BYSorts results by column names, expressions, or output column positions. Supports ASC (ascending) and DESC (descending).
LIMITLimits the number of rows returned. Accepts one or two integer parameters.
FOR UPDATEAcquires an exclusive lock on each row in the result set, preventing other transactions from concurrently updating those rows. For certain transaction isolation levels, it also prevents concurrent reads.

Usage notes

WHERE and HAVING

Expressions used in a WHERE clause cannot be used in a HAVING clause. Use WHERE to filter individual rows and HAVING to filter grouped results.

-- Not supported: filtering with col_name directly in HAVING
SELECT col_name FROM tbl_name HAVING col_name > 0;

-- Correct: filter individual rows with WHERE
SELECT col_name FROM tbl_name WHERE col_name > 0;

Aggregate functions are allowed in HAVING but not in WHERE:

SELECT user, MAX(salary) FROM users
  GROUP BY user HAVING MAX(salary) > 10;

LIMIT

With two parameters, the first specifies the offset of the first row to return and the second specifies the number of rows to return. The initial row offset is 0, not 1.

-- Return 5 rows starting from the 11th row (offset 10)
SELECT * FROM orders LIMIT 10, 5;

-- Equivalent syntax (PostgreSQL-compatible)
SELECT * FROM orders LIMIT 5 OFFSET 10;

With one parameter, it specifies the number of rows to return with a default offset of 0:

SELECT * FROM orders LIMIT 20;

ORDER BY

When using both GROUP BY and ORDER BY, expressions in the ORDER BY clause must also appear in the SELECT clause or the GROUP BY clause.

-- Not supported: salary appears in neither SELECT nor GROUP BY
SELECT user FROM users GROUP BY age ORDER BY salary;

Aggregate functions and expressions that contain aggregate functions cannot be used directly in ORDER BY. Define the expression as a select_expr, assign it an alias, and reference the alias in ORDER BY:

SELECT user, MAX(salary) AS max_salary
FROM users
GROUP BY user
ORDER BY max_salary DESC;

Aliases

Empty strings cannot be used as aliases.

MySQL compatibility

PolarDB-X follows MySQL syntax with the following differences:

FeatureMySQLPolarDB-X
GROUP BY with ASC/DESCSupportedNot supported
STRAIGHT_JOINSupportedNot supported
NATURAL JOINSupportedNot supported
Empty string as aliasSupportedNot supported
Duplicate column names in UNION SELECTSupportedNot supported

JOIN

PolarDB-X supports the following JOIN syntax in table_references:

table_references:
    escaped_table_reference [, escaped_table_reference] ...
escaped_table_reference:
    table_reference
  | { OJ table_reference }
table_reference:
    table_factor
  | join_table
table_factor:
    [schema_name.]tbl_name [[AS] alias] [index_hint_list]
  | table_subquery [AS] alias
  | ( table_references )
join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
join_condition:
    ON conditional_expr
  | USING (column_list)
index_hint_list:
    index_hint [, index_hint] ...
index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
    index_name [, index_name] ...

Key behaviors:

  • JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents in PolarDB-X and MySQL.

  • An INNER JOIN without an ON clause is equivalent to the comma operator (,). Both produce a CROSS JOIN. The following two statements are equivalent:

    SELECT * FROM t1 INNER JOIN t2 WHERE t1.id > 10
SELECT * FROM t1, t2 WHERE t1.id > 10

  • USING(column_list) specifies column names that exist in both tables. PolarDB-X generates equivalent join conditions based on those columns. The following two statements are equivalent:

    a LEFT JOIN b USING(c1, c2)
a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2

  • JOIN has higher precedence than the comma operator. t1, t2 JOIN t3 is interpreted as (t1, (t2 JOIN t3)), not ((t1, t2) JOIN t3).

  • LEFT JOIN and RIGHT JOIN require an ON clause.

  • index_hint specifies the index for MySQL to use. PolarDB-X pushes the hint down to MySQL.

  • STRAIGHT_JOIN and NATURAL JOIN are not supported.

UNION

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
Each SELECT clause in a UNION must not contain duplicate column names. The following statement is not supported:
SELECT id, id, name FROM t1 UNION SELECT pk, pk, name FROM t2;

References

Previous：Related operations for SQL statementsNext：Subqueries