This topic describes how to execute the SELECT statement to query data from one or more tables.

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]
Descriptions of the clauses in the SELECT statement:
  • select_expr specifies the column to be queried. One SELECT statement must contain at least one select_expr expression.
  • table_references specifies the tables from which data is retrieved.
  • The WHERE clause specifies query criteria. If this clause is specified as where_condition, the system returns the rows that meet the requirement in where_condition. If this clause is not specified, all rows are returned.
  • The GROUP BY clause supports the references to column names, expressions, and positions in output columns.
  • The HAVING clause is similar to the WHERE clause. The difference is that the HAVING clause allows you to use aggregate functions.
  • The ORDER BY clause specifies the order in which data is sorted. This clause supports the 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 OFFSET clause specifies the offset of an output result set. The LIMIT clause specifies the size of an output result set. The LIMIT clause allows you to specify one or two numeric parameters. The parameters must be integer constants. If you specify two parameters, the first parameter specifies the offset of the first row to be returned and the second parameter specifies the maximum number of rows to be returned. The initial offset of the first row is 0 instead of 1. To be compatible with PostgreSQL, MySQL also supports LIMIT and OFFSET.
  • The FOR UPDATE clause applies an exclusive lock on each row of the query results. This prevents other transactions from concurrently updating the rows. This also prevents other transactions from concurrently reading the rows for which some transaction isolation levels are specified.

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

DRDS 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. DRDS 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. DRDS pushes the hint to the underlying MySQL database.
  • STRAIGHT_JOIN and NATURAL JOIN are not supported.

UNION

DRDS supports the following UNION syntax:

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]        
Note In each SELECT clause of UNION, DRDS does not support multiple columns with the same name. The following SQL statement is not supported because the column names in the SELECT clause are duplicates.
SELECT id, id, name FROM t1 UNION SELECT pk, pk, name FROM t2;          

References