SELECT statement syntax and usage for querying data - PolarDB

Use the SELECT statement to query data from one or more tables in PolarDB-X 1.0.

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]

Clause reference

Clause	Description
`select_expr`	Specifies the column to query. Every SELECT statement must include at least one `select_expr`.
`FROM table_references`	Specifies the tables from which data is retrieved.
`WHERE where_condition`	Filters rows by a condition. Only rows that satisfy `where_condition` are returned. If omitted, all rows are returned.
`GROUP BY`	Groups rows by column names, expressions, or output column positions.
`HAVING where_condition`	Filters grouped results. Unlike WHERE, HAVING supports aggregate functions.
`ORDER BY`	Sorts results by column names, expressions, or output column positions. Use `ASC` for ascending order (default) or `DESC` for descending order.
`LIMIT`	Limits the number of rows returned and optionally sets a row offset. Both parameters must be integer constants. With one parameter, it specifies the row count (offset defaults to 0). With two parameters, the first is the offset of the first row to return (starting at 0, not 1), and the second is the maximum row count. The `LIMIT row_count OFFSET offset` syntax is also supported for PostgreSQL compatibility.
`FOR UPDATE`	Applies an exclusive lock on every row in the result set. This prevents other transactions from concurrently updating those rows, and also prevents concurrent reads under certain transaction isolation levels.

JOIN

PolarDB-X 1.0 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] ...

Keep the following behaviors in mind when using JOIN:

JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents, matching MySQL behavior.
An INNER JOIN without an ON clause is equivalent to a comma (,). Both produce a CROSS JOIN. For example, 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 columns that exist in both tables. PolarDB-X 1.0 constructs the equivalent ON condition automatically. For example, the following two fragments are equivalent:
```
a LEFT JOIN b USING(c1, c2)
a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2
```
The JOIN operator has higher precedence than the comma operator. The expression t1, t2 JOIN t3 is interpreted as (t1, (t2 JOIN t3)), not ((t1, t2) JOIN t3).
LEFT JOIN and RIGHT JOIN require an ON condition.
Index hints (USE INDEX, IGNORE INDEX, FORCE INDEX) are pushed down to the underlying MySQL instance.

UNION

PolarDB-X 1.0 supports the following UNION syntax:

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

Note In each SELECT clause of a UNION, PolarDB-X 1.0 does not support duplicate column names. The following statement is not supported:

SELECT id, id, name FROM t1 UNION SELECT pk, pk, name FROM t2;

MySQL compatibility

PolarDB-X 1.0 is MySQL-compatible but has the following differences and constraints:

WHERE vs. HAVING: Expressions used in a WHERE clause cannot be used in a HAVING clause. Use WHERE to filter raw rows and HAVING to filter aggregated results. For example, write this:
```
SELECT col_name FROM tbl_name WHERE col_name > 0;
```
Not this:
```
SELECT col_name FROM tbl_name HAVING col_name > 0;
```
Aggregate functions in HAVING: Aggregate functions are allowed in HAVING but not in WHERE. For example:
```
SELECT user, MAX(salary) FROM users
  GROUP BY user HAVING MAX(salary) > 10;
```
GROUP BY: Does not support ASC or DESC.
GROUP BY with ORDER BY: When both are used, every expression in ORDER BY must also appear in the SELECT clause or the GROUP BY clause. The following statement is not supported:
```
SELECT user FROM users GROUP BY age ORDER BY salary;
```
Aggregate functions in ORDER BY: 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 instead.
Aliases: Empty strings cannot be used as aliases.
STRAIGHT_JOIN and NATURAL JOIN: Not supported.
UNION with duplicate column names: Not supported. Each SELECT clause in a UNION must use unique column names.

PolarDB:SELECT

Syntax

Clause reference

JOIN

UNION

MySQL compatibility

References