All Products
Search
Document Center

SELECT

Last Updated: Aug 17, 2020

This topic describes how to query data from one or more tables in Distributed Relational Database Service (DRDS) by executing the SELECT statement.

  1. SELECT
  2. [ALL | DISTINCT]
  3. select_expr [, select_expr ...]
  4. [FROM table_references
  5. [WHERE where_condition]
  6. [GROUP BY {col_name | expr | position}
  7. [HAVING where_condition]
  8. [ORDER BY {col_name | expr | position}
  9. [ASC | DESC], ...]
  10. [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  11. [FOR UPDATE]

Descriptions of SELECT clauses:

  • select_expr specifies the column to be queried. One SELECT statement must contain at least one select_expr clause.
  • table_references specifies the tables from which data is retrieved. For more information, see JOIN syntax.
  • The WHERE clause specifies the query condition, that is, queries rows that meet the condition where_condition. If this clause is not specified, all rows are queried.
  • The GROUP BY clause supports references to column names, expressions, and positions in output columns.
  • The HIVING clause is similar to the WHERE clause, except that the aggregate function can be used.
  • The ORDER BY clause specifies the order in which data is sorted. It supports references to column names, expressions, and positions in output columns. You can also specify the sort direction, such as ASC (ascending order) and DESC (descending order).
  • The LIMIT/OFFSET clause specifies the offset and size of an output result set. You can use the following syntax: LIMIT followed by one or two numeric parameters, or LIMIT… OFFSET …
  • The FOR UPDATE clause adds exclusive locks to all rows in the query result to prevent concurrent modifications of other transactions or concurrent reads from other transactions.

Note the following points when you use the SELECT statement:

  • In the HAVING clause, do not use expressions that should be used in WHERE. For example:
  1. SELECT col_name FROM tbl_name HAVING col_name > 0;

Rewrite it into the following SQL statement:

  1. SELECT col_name FROM tbl_name WHERE col_name > 0;
  • The HAVING clause can reference aggregate functions, but the WHERE clause cannot.
  1. SELECT user, MAX(salary) FROM users
  2. GROUP BY user HAVING MAX(salary) > 10;
  • If the LIMIT clause contains two parameters, the first parameter indicates the offset of the first row returned, and the second parameter indicates the number of rows returned. If it contains only one parameter, it indicates the number of rows returned, and the default offset is 0.
  • The GROUP BY clause does not support ASC or DESC.
  • When both GROUP BY and ORDER BY exist, expressions that follow ORDER BY must belong to a SELECT expression or a GROUP BY expression. For example, the following SQL statement is not supported:
  1. 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. You can use an expression as a select_expr, assign an alias, and reference the alias in the ORDER BY clause.
  • Empty strings cannot be used as aliases at present.

JOIN syntax

DRDS supports the following JOIN syntax in table_references of the SELECT statement:

  1. table_references:
  2. escaped_table_reference [, escaped_table_reference] ...
  3. escaped_table_reference:
  4. table_reference
  5. | { OJ table_reference }
  6. table_reference:
  7. table_factor
  8. | join_table
  9. table_factor:
  10. [schema_name.]tbl_name [[AS] alias] [index_hint_list]
  11. | table_subquery [AS] alias
  12. | ( table_references )
  13. join_table:
  14. table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  15. | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  16. join_condition:
  17. ON conditional_expr
  18. | USING (column_list)
  19. index_hint_list:
  20. index_hint [, index_hint] ...
  21. index_hint:
  22. USE {INDEX|KEY}
  23. [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  24. | IGNORE {INDEX|KEY}
  25. [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  26. | FORCE {INDEX|KEY}
  27. [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  28. index_list:
  29. index_name [, index_name] ...

When you use JOIN statements, consider the following factors:

  • JOIN, CROSS JOIN, and INNER JOIN are syntax-equivalent, which is the same as in MySQL.
  • An INNER JOIN without ON is equivalent to using a comma, indicating the Cartesian product. For example, the following two SQL statements are equivalent:
  1. SELECT * FROM t1 INNER JOIN t2 WHERE t1.id > 10
  2. SELECT * FROM t1, t2 WHERE t1.id > 10
  • USING(column_list) specifies to join the column names that exist in two tables. DRDS constructs an equivalent condition based on these columns. For example, the following two SQL fragments are equivalent:
  1. a LEFT JOIN b USING(c1, c2)
  2. a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2
  • The JOIN operator takes precedence over the comma operator. The JOIN expression t1, t2 JOIN t3 is converted to (t1, (t2 JOIN t3) rather than ((t1, t2) JOIN t3).
  • LEFT/RIGHT JOIN must contain the ON condition.
  • index_hint specifies the index to be used by ApsaraDB RDS for MySQL. DRDS pushes the hint to the underlying ApsaraDB RDS for MySQL database.
  • STRAIGHT_JOIN and NATURAL JOIN are not supported.

UNION syntax

DRDS supports the following UNION syntax:

  1. SELECT ...
  2. UNION [ALL | DISTINCT] SELECT ...
  3. [UNION [ALL | DISTINCT] SELECT ...]

Note: DRDS does not support multiple columns with the same name in each SELECT clause of UNION. For example:

  1. # The column names in the SELECT clause of the following SQL statement are duplicate. This SQL statement is not supported.
  2. SELECT id, id, name FROM t1 UNION SELECT pk, pk, name FROM t2;

References