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 but not 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 indicates the offset of the first row that is returned, and the second parameter indicates the number of rows that are returned. If the LIMIT clause contains only one parameter, this parameter indicates 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 that follow ORDER BY must be included 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 a 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 1.0 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] ...
            

To use the JOIN statements, consider the following factors:

• JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents. This is also the case in MySQL.
• An INNER JOIN statement without an ON clause is equivalent to using a comma (,). Both of them 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) is used to specify the column names that exist in both tables from which you want to combine data. PolarDB-X 1.0 constructs an equivalent condition based on these columns. For example, the following SQL 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 JOIN expression t1, t2 JOIN t3 is interpreted as (t1, (t2 JOIN t3)), not as ((t1, t2) JOIN t3).
• LEFT JOIN and RIGHT JOIN must contain the ON condition.
• index_hint specifies the index to be used by MySQL. PolarDB-X 1.0 pushes the hint to the underlying MySQL database.
• STRAIGHT_JOIN and NATURAL JOIN are not supported.

UNION

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