PolarDB:SELECT

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]

Note

• The expressions that are used in a WHERE clause cannot be used in a HAVING clause. For example, the following SQL statement 1 must be rewritten as SQL statement 2.
  SQL statement 1:

  SELECT col_name FROM tbl_name HAVING col_name > 0;

  SQL statement 2:

  SELECT col_name FROM tbl_name WHERE col_name > 0;

• You can use aggregate functions in the HAVING clause. You cannot use aggregate functions in the WHERE clause.

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

• If the LIMIT clause contains two parameters, the first parameter specifies the offset of the first row that is returned and the second parameter specifies the number of rows that are returned. If the LIMIT clause contains only one parameter, this parameter specifies the number of rows that are returned, and the default offset is 0.
• The GROUP BY clause does not support ASC or DESC.
• If both GROUP BY and ORDER BY are used, the expressions specified after ORDER BY must be specified in a SELECT clause or a GROUP BY clause. For example, the following SQL statement is not supported:

  SELECT user FROM users GROUP BY age ORDER BY salary;     

• Aggregate functions and expressions that contain aggregate functions cannot be used in the ORDER BY clause. If you want to use such an expression, define the expression as select_expr, assign an alias to the expression, and then reference the alias in the ORDER BY clause.
• Empty strings cannot be used as aliases.

JOIN

PolarDB-X supports the following JOIN syntax in table_references of the SELECT statement:

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] ...

Before you execute the JOIN statements, take note of the following factors:

• JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents in PolarDB-X and MySQL.
• An INNER JOIN statement without an ON clause is equivalent to the comma operator (,). The INNER JOIN statement and the comma operator (,) each indicate a CROSS JOIN. For example, the following SQL 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 the names of columns that exist in both tables. PolarDB-X specifies equivalent conditions based on these columns. For example, the following SQL statements 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 JOIN expression t1, t2 JOIN t3 is used as (t1, (t2 JOIN t3)) instead of ((t1, t2) JOIN t3).
• LEFT JOIN and RIGHT JOIN must contain ON clauses.
• index_hint specifies the index used by MySQL. PolarDB-X pushes the hint down to MySQL.
• STRAIGHT_JOIN and NATURAL JOIN are not supported.

UNION

supports the following UNION syntax:

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

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

References

• SELECT syntax in MySQL
• JOIN syntax in MySQL
• UNION syntax in MySQL