:SELECT

Standard syntax:

The syntax of SELECT is as follows:

[ WITH with_subquery_table_name AS ( query ) ]
SELECT
[DISTINCT] select_expr [, select_expr ...]
[FROM table_reference [, ...] ]
[WHERE filter_condition]
[GROUP BY { expr | ROLLUP ( expr_list ) | CUBE ( expr_list ) | GROUPING SETS ( expr_list )} , ...]
[HAVING having_condition]
[ORDER BY {col_name | expr }
[ASC | DESC], ...]
[{ UNION [ ALL ] | INTERSECT | EXCEPT } (SELECT select_expr..)]
[LIMIT {row_count}]
            

WITH clause

The WITH clause defines one or more subqueries. Each subquery defines a temporary table, similar to a view definition. Other clauses in the current query can reference the temporary table defined in the WITH clause. All temporary tables that are defined in the WITH statement can also be defined in subqueries that use the SELECT clause. When all the subqueries or temporary tables are referenced multiple times in follow-up statements, the WITH statement reuses the first result of the temporary tables to reduce the number of times common table expressions are used.

Syntax:

[ WITH with_subquery [, ...] ]
            

Syntax of with_subquery:

with_subquery_table_name AS ( query )
            

Parameters:

• with_subquery_table_name: a unique temporary table name in the current query.
• query: all SELECT queries that are supported.

Example:

with t as (select x,y from A) select t.y from t order by t.x limit 10

SELECT list

Basic structure of the projection expression in the SELECT statement:

SELECT [ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...]
            

Parameters:

• ALL: an optional redundant field that is used when the DISTINCT parameter is not required.
• DISTINCT: deletes duplicate rows.
• *: returns all columns.
• expression: one or more column references, or a column expression that includes one or more functions.
• AS column_alias: defines the alias of the specified column, where the AS keyword is optional. If the alias following AS is a string that contains one or more spaces, you can enclose this string using two backticks (`).

FROM clause

Syntax:

FROM table_reference [, ...]
            

In this statement, table_reference supports the following formats:

with_subquery_table_name [ [ AS ] alias ]
table_name [ * ] [ [ AS ] alias ]
( subquery ) [ AS ] alias 
table_reference join_type table_reference
[ ON join_condition ]
            

Parameters:

• with_subquery_table_name : a subquery name defined in the WITH statement.
• table_name: a table name or a view name.
• alias: a table alias or a view alias.
• join_type: the join type, including [INNER] JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, and CROSS JOIN.
• join_condition: the ON condition used in a join. The condition following ON can only be an equivalence relation. Non-equivalence relations are defined in the WHERE clause.

WHERE clause

Syntax:

[WHERE filter_condition]
            

In this statement, filter_condition can either be several expressions that are connected with Boolean logical operators such as AND and OR, or correlated or non-correlated subqueries or semi-joins that contain IN, NOT IN, EXIST, or NOT EXIST.

GROUP BY clause

The GROUP BY clause divides the query result into groups of rows. The syntax is as follows:

GROUP BY [ROLLUP | CUBE] expression [, ...]
            

The expression following GROUP BY must be a non-aggregate expression in the SELECT list.

HAVING clause

The HAVING clause specifies a search condition for a group or an aggregate after grouping. The syntax is as follows:

[ HAVING condition ]
            

ORDER BY clause

The ORDER BY clause sorts data returned by a query. The syntax is as follows:

[ ORDER BY expression
[ ASC | DESC ]
[ LIMIT { count | ALL } ]
            

Parameters:

• expression: specifies a column or expression as the sort criterion for the query result set. This parameter can be the column or alias in the SELECT list, or be the column that does not appear in the SELECT list.
• ASC | DESC: specifies that the values in the specified column should be sorted in ascending or descending order. The Null values are treated as the lowest possible values.
• LIMIT number | ALL: limits the number of rows returned. The number parameter specifies the number of rows, and ALL specifies that all rows are returned.

UNION/INTERSECT/EXCEPT/MINUS clause

The UNION/INTERSECT/EXCEPT/MINUS clause is used to perform set operations including union, intersection, and difference. The syntax is as follows:

query
{ UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query
            

Parameters:

• query: The query parameter must return the consistent number and type of columns prior to and following the operators.
• UNION [ALL]: returns the result of performing the union operation on sets. The ALL parameter specifies that deduplication is not required.
• INTERSECT: returns the result of performing the intersection operation on query result sets.
• EXCEPT: returns the result of the difference operation on query result sets.
• MINUS: returns the result of the difference operation on query result sets. The effect is the same as EXCEPT.

Order of the set operations:

The UNION and EXCEPT operators are left-associative, and start operations from the left to the right. Here is an example:

select * from t1
union
select * from t2
except
select * from t3
order by c1;
            

In this statement, the operations t1 union t2 except t3 are finished first, and then followed by global sorting of the preceding operation result according to c1.

The INTERSECT operator is prior to UNION and EXCEPT. Here is an example:

select * from t1
union
select * from t2
intersect
select * from t3
order by c1;
            

The preceding statement is equivalent to the following statement:

select * from t1
union
(select * from t2
intersect
select * from t3)
order by c1;
            

CONNECT BY clause in hierarchical queries

• The hierarchical query that uses the START WITH... CONNECT BY PRIOR clause is supported. Required fields must appear in the projection expression of the SELECT statement.

• The START WITH clause allows comparison conditions and IN expressions, but does not support subqueries and the WHERE statement.

• The CONNECT BY PRIOR clause allows one equi-join, but does not support multiple equi-joins or any non-equi joins or subqueries.

• Except CONNECT BY PRIOR, other CONNECT BY clauses are not supported.

• Other hidden fields or hierarchical functions such as LEVEL and SYS_CONNECT_BY_PATH are not supported.

-- Common query
select id, long_test from test start with id < 100 connect by prior id = long_test

-- Subquery
select * from (select id, long_test from test start with id in (1,2,3) connect by prior id = long_test) as hier order by 1,2
            

MySQL functions supported by the SELECT statement

HybridDB for MySQL supports the following MySQL functions used in the SELECT statement:

• Unless specified otherwise, all the following functions are defined in MySQL 5.6.

• Currently, the following functions can only be used in the SELECT statement, and do not support other SQL statements, such as UPDATE, DELETE, INSERT, and REPLACE.

Compatibility

• HybridDB for MySQL does not use any number, which is out of the permissible range of the column data type, from a user-defined function. Otherwise, an error occurs to indicate that the number is out of range.

MySQL functions unsupported by the SELECT statement

Compared with functions in MySQL 5.6, HybridDB for MySQL does not support the following MySQL functions:

Oracle functions supported by the SELECT statement

Currently, HybridDB for MySQL supports the following Oracle functions in the query that uses the SELECT statement: