SELECT Syntax

SQL Statements

Description

Descriptions to detailed syntax elements of this SQL Command.

The UPSERT or REPLACE statement without a subquery is similar to the UPDATE statement. The only difference is when the WHERE clause predicate is false, it adds a new record to the table like the INSERT statement.

In case of a table which has a PRIMARY KEY, the PRIMARY KEY column must be included in the column list.

Columns defined with NOT NULL without default specification have to be included in the column list as well.

Other columns are filled with default value or NULL if not specified.

The UPSERT or REPLACE statement with a subquery works like the INSERT statement, except that if an old row in the table has the same value as a new row for a PRIMARY KEY, then the old row is changed by values of the returned record from a subquery. Unless the table has a PRIMARY KEY, it becomes equivalent to INSERT because there is no index to be used to determine whether or not a new row duplicates another.

The UPSERT or REPLACE statement with a 'WITH PRIMARY KEY' is same as one with the subquery. It works based on the PRIMARY KEY.

Example

CREATE TABLE A (A INT PRIMARY KEY, B INT);

UPSERT A VALUES (1, 1);

UPSERT A SELECT A + 1, B FROM A;

SELECT Syntax

<select_statement> ::= <subquery> [ <for_update> | <time_travel> ] | ( <subquery> ) [ <for_update> | <time_travel> ] <subquery> ::= <select_clause>

<from_clause >

[<where_clause>]

[<group_by_clause>]

[<having_clause>]

[{<set_operator> <subquery>, ... }]

[<order_by_clause>]

[<limit>]

SELECT clause

The select clause specifies an output to be returned either to users or to an outer select clause if exists.

<select_clause> ::= SELECT [TOP n] [ ALL | DISTINCT ] <select_list>

<select_list> ::= {<select_item>, ... } <select_item> ::= [<table_name>.] <asterisk>

| <expression> [ AS ] <column_alias>

<table_name> ::= [<schema_name>.] <identifier>

<asterisk> ::= *

TOP

TOP can be used to return the first n records from the SQL statement.

DISTINCT and ALL

DISTINCT can be used to return only one copy of each set of duplicate records selected.

ALL can be used to return all rows selected, including all copies of duplicates. The default is ALL.

select_list

The select list allows users to specify columns they want to retrieve from tables.

asterisk

Asterisk (*) can be used to select all columns from all tables or views listed in the FROM clause. If a schema name and a table name or a table name is provided with asterisk(*), it is used to limit the scope of the result set to the specified table.

column_alias

Column aliases can be used to represent columns or expressions simply.

FROM clause

The from clause specifies inputs such as tables, views, and subqueries to be used in the select statement.

<from_clause> ::= FROM {<table>, ... }

<joined_table> ::= <table> [<join_type>] JOIN <table> ON <predicate>

| <table> CROSS JOIN <table>

| <joined_table>

<join_type> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]

The join_type specifies the type of join being performed. LEFT indicates a left outer join, RIGHT indicates a right outer join, and FULL indicates a full outer join. OUTER may or may not be used when an outer join is being performed.

ON <predicate>

The ON clause specifies a join predicate.

CROSS JOIN

CROSS indicates that a cross join is being performed. A cross join produces the cross-product of two tables.

WHERE clause

The WHERE clause is used to specify predicates on inputs in the FROM clause, so users can retrieve the desired records.

<where_clause> ::= WHERE <condition>

<condition> ::=

| <condition> AND <condition>

| NOT <condition>

| ( <condition> ) | <predicate>

| <range_preciate>

| <in_predicate>

| <exist_predicate>

| <like_predicate>

| <null_predicate>

<comparison_predicate> ::= <expression> { = | != | <> | > | < | >= | <= } [ ANY | SOME | ALL ] ({<expr_list> | <subquery>}) <range_predicate> ::= <expression> [NOT] BETWEEN <expression> AND <expres sion>

<in_predicate> ::= <expression> [NOT] IN ( { <expr_list> | <subquery> } ) <exist_predicate> ::= [NOT] EXISTS ( <subquery> )

<like_predicate> ::= <expression> [NOT] LIKE <expression> [ESCAPE <expres sion>]

<null_predicate> ::= <expression> IS [NOT] NULL

GROUP BY clause

<group_by_clause> ::=

GROUP BY { <group_by_expression_list> | <grouping_set> } <group_by_expression_list> ::= { <expression>, ... } <grouping_set> ::= { GROUPING SETS | ROLLUP | CUBE } [BEST number] [LIMIT number [OFFSET number] ] [WITH SUBTOTAL] [WITH BALANCE] [WITH TOTAL]

[TEXT_FILTER <filterspec> [FILL UP [SORT MATCHES TO TOP]]]

[STRUCTURED RESULT [WITH OVERVIEW] [PREFIX value] | MULTIPLE RESULTSETS]

( <grouping_expression_list> )

<grouping_expression_list> ::= { <grouping_expression>, ... } <grouping_expression> ::=

| ( <expression>, ... )

| ( ( <expression>, ... ) <order_by_clause> )

The GROUP BY clause is used to group the selected rows based on the values in the specified columns.

GROUPING SETS

Generates results with specified multiple groupings of data in a single statement. If no additional options such as best and limit are set, the result is the same as UNION ALL of the aggregation of each specified group. For example, "select col1, col2, col3, count(*) from t group by grouping sets ( (col1, col2), (col1, col3) )" is equivalent to "select col1, col2, NULL, count(*) from t group by col1, col2 union all select col1, NULL, col3, count(*) from t group by col1, col3". In the grouping-sets query each of (col1, col2) and (col1, col3) specifies the grouping.

ROLLUP

Generates results with multiple levels of aggregation in a single statement. For example, "rollup (col1, col2, col3)" is equivalent to "grouping sets ( (col1, col2, col3), (col1, col2), (col1) )" with an additional aggregation without grouping. Thus, the number of grouping that result set contains is the number of columns in ROLLUP list plus one for last aggregation if there is no additional option.

CUBE

Generates results with multiple levels of aggregations in a single statement. For example, "cube (col1, col2, col3)" is equivalent to "grouping sets ( (col1, col2, col3), (col1, col2), (col1, col3), (col2, col3), (col1), (col2), (col3) )" with an additional aggregation without grouping. Thus, the number of grouping that result set contains is the same as all possible permutations of columns in the CUBE list plus one for the last aggregation if there is no additional option.

BEST n

Returns only the top-n grouping sets sorted in descending order of the number of rows aggregated in each grouping set. n can be any of zero, positive, and negative. When n is zero, it is the same with no BEST option.

When n is negative, it means sorting in ascending order.

LIMIT n1 [OFFSET n2]

Returns the first n1 grouped records after skipping n2 ones for each grouping set.

WITH SUBTOTAL

Returns for each grouping set an additional subtotal of the returned results as controlled by OFFSET or LIMIT.

Unless OFFSET and LIMIT is set, the value is the same as the one for WITH TOTAL.

WITH BALANCE

Returns for each grouping set an additional aggregated value of the remaining values not returned as controlled by OFFSET or LIMIT.

WITH TOTAL

Returns for each grouping set an additional row that is the aggregated total value. OFFSET and LIMIT options cannot change this value.

TEXT_FILTER <filterspec>

Performs text filtering or highlighting on the grouping columns with <filterspec>, which is a single-quoted string that follows the below syntax.

<filterspec> ::= '[<prefix>]<element>{<subsequent>, ...}' <prefix> ::= + | - | NOT

<token> ::= !! Unicode letters or digits

<phrase> ::= !! double-quoted string that does not contain double quotations inside

<prefix_subsequent> ::= + | - | NOT | AND | AND NOT | OR

A filter defined by <filterspec> is a token/phrase or tokens/phrases connected with logical operators such as AND, OR and NOT. A token matches a string that contains its corresponding word case-insensitively. For example, 'ab' matches 'ab cd' and 'cd Ab' but does not match 'abcd' A token can contain wildcard characters ' that matches any string and ' that matches any character. Inside phrase, however, ' and ' do not work as wildcard characters. With tokens and phrases logical operators AND, OR and NOT may be used together.

Since OR is the default operator, 'ab cd' is the same as 'ab OR cd' Note that logical operators should be written in uppercase characters. As a kind of logical operators, prefixes '+' and '-' mean inclusion (AND) and exclusion (AND NOT), respectively. For example, 'ab -cd' is the same as 'ab AND NOT cd' If there is no FILL UP option, only grouped records that have matching values are returned. Note that a filter is applied to only the

first grouping column in each grouping set.

FILL UP

Returns not only matched grouped records, but also non-matched ones. Function text_filter is useful to identify which one is matched. Refer to 'Related Functions' below.

SORT MATCHES TO TOP

Returns matching values before non-matching ones for each grouping set. This option cannot be used with SUBTOTAL, BALANCE and TOTAL.

STRUCTURED RESULT

Returns results as temporary tables. For each grouping set a single temporary table is created. If WITH OVERVIEW option is set, an additional temporary table is created for the overview of grouping sets. The names of temporary tables are specified by PREFIX option.

WITH OVERVIEW

Returns the overview in a separate table additionally.

PREFIX value

Specifies a prefix for naming the temporary tables. It must start with "#", which means the temporary table.

If omitted, the default prefix is "#GN". Then, the concatenation of this prefix value and a nonnegative integer number is used as the name of temporary tables; for example, "#GN0", "#GN1" and "#GN2". Refer to 'Return Format' below.

MULTIPLE RESULTSETS

Returns results in multiple result sets.

The projection clause must include all grouping columns used in the GROUPING SETS specification.

Related Functions

grouping_id ( <grouping_column1, ..., grouping_columnn> ) function returns an integer number to identify which grouping set each grouped record belongs to. text_filter ( <grouping_column> ) function, which is used with TEXT_FILTER, FILL UP, and SORT MATCHES TO TOP, displays matching values or NULL. NULL is displayed for non-matching values when FILL UP option is specified.

Return Format

If neither STRUCTURED RESULT nor MULTIPLE RESULTSETS is set, the unioned result of all grouping sets is returned, with NULL values filling up attributes that are not included in a specific grouping set. With STRUCTURED RESULT, temporary tables are created additionally which can be queried using "SELECT * FROM <table name>" in the same session. The name of the tables follows the form

<PREFIX>0: this table will contain the overview if WITH OVERVIEW is specified

<PREFIX>n: n-th grouping set subject to re-ordering by the BEST parameter

With MULTIPLE RESULTSETS, multiple result sets are returned. Grouped records for each grouping set are in a single result set.

Example

Here is t1 table.

drop table t1;

create column table t1 ( id int primary key, customer varchar(5), year int, product varchar(5), sales int );

insert into t1 values(1, 'C1', 2009, 'P1', 100);

insert into t1 values(2, 'C1', 2009, 'P2', 200);

insert into t1 values(3, 'C1', 2010, 'P1', 50);

insert into t1 values(4, 'C1', 2010, 'P2', 150);

insert into t1 values(5, 'C2', 2009, 'P1', 200);

insert into t1 values(6, 'C2', 2009, 'P2', 300);

insert into t1 values(7, 'C2', 2010, 'P1', 100);

insert into t1 values(8, 'C2', 2010, 'P2', 150);

The following GROUPING SETS query is equivalent to the second below group-by query. Note that two groups inside grouping sets in the first query are specified at each group by in the second query.

select customer, year, product, sum(sales) from t1

select customer, year, NULL, sum(sales) from t1

group by customer, year union all

select customer, NULL, product, sum(sales) from t1

group by customer, product;

ROLLUP and CUBE are concise representations of grouping sets that are used frequently. The following ROLLUP query is equivalent to the second below grouping-set query.

select customer, year, sum(sales) from t1

group by ROLLUP(customer, year);

select customer, year, sum(sales) from t1

select NULL, NULL, sum(sales) from t1;

The following CUBE query is equivalent to the second below grouping-set query.

select customer, year, sum(sales) from t1

group by CUBE(customer, year);

select customer, year, sum(sales) from t1

select NULL, NULL, sum(sales) from t1;

BEST 1 specifies that the following query returns only top-1 best group. In this example, 4 records exist for (customer, year) group and 2 records exist for (product) group, so the former 4 records are returned. For 'BEST -1' instead of 'BEST 1', the latter 2 records are returned.

select customer, year, product, sum(sales) from t1

LIMIT 2 limits the number of records to maximum 2 for each group. For (customer, year) group, the number of its records are 4, then only first 2 records will be returned. For (product) group, the number of its records are 2, so all the records will be returned.

select customer, year, product, sum(sales) from t1

WITH SUBTOTAL produces additional one record for each group to display subtotal of returned records.

These subtotal records get NULL for each of customer, year, product columns and the sum of sum(sales) values in the select list.

select customer, year, product, sum(sales) from t1

group by grouping sets LIMIT 2 WITH SUBTOTAL (

(customer, year), (product) );

WITH BALNACE produces additional one record for each group to display subtotal of unreturned records.

select customer, year, product, sum(sales) from t1

WITH TOTAL produces additional one record for each group to display total of all grouped records without regard that the records are returned or not.

select customer, year, product, sum(sales) from t1

TEXT_FILTER allows users to retrieve the first column of each group with a given <filterspec>. The following query will search columns ending with '2': customers for the first grouping set and products for the second one. Only matched three records will be returned. TEXT_FILTER function in the select list is useful to see which values are matched.

select customer, year, product, sum(sales), text_filter(customer), text_filter(prod uct)

FILL UP is used to return both matched and non-matched records with <filterspec>. Therefore, the following query returns six records whereas the previous query does three ones.

select customer, year, product, sum(sales), text_filter(customer), text_filter(prod uct)

SORT MATCHES TO TOP is used to raise matched records up. For each grouping set, its grouped records will be sorted.

select customer, year, product, sum(sales), text_filter(customer), text_filter(prod uct)

from t1

group by grouping sets TEXT_FILTER '*2' FILL UP SORT MATCHES TO TOP (

(customer, year), (product) );

STRUCTURED RESULT creates temporary tables: one for each grouping set and one more for the overview table optionally. "#GN1" table is for (customer, year) grouping set and "#GN2" table is for (product) one.

Note that each table contains only related columns. That is, "#GN1" table does not have "product" column and "#GN2" table does not have "customer" and "year" columns.

select customer, year, product, sum(sales) from t1

group by grouping sets STRUCTURED RESULT (

(customer, year), (product) );

);

select * from "#GN1";

select * from "#GN2";

WITH OVERVIEW creates a temporary table "#GN0" for the overview table.

drop table "#G1";

drop table "#G2";

select customer, year, product, sum(sales) from t1

group by grouping sets structured result WITH OVERVIEW (

(customer, year), (product) );

select * from "#GN0";

select * from "#GN1";

select * from "#GN2";

Users can change the names of temporary tables by using PREFIX keyword. Note that the names still must start with '#', which is the prefix of temporary tables.

select customer, year, product, sum(sales) from t1

group by grouping sets STRUCTURED RESULT WITH OVERVIEW PREFIX '#MYTAB' (

(customer, year), (product) );

select * from "#MYTAB0";

select * from "#MYTAB1";

select * from "#MYTAB2";

Temporary tables are dropped when the corresponding session is closed or when a user executes a drop command. A list of temporary tables are seen in m_temporary_tables.

select * from m_temporary_tables;

MULTIPLE RESULTSETS returns resultsets as multiple resultsets. In SAP HANA Studio, the following query will return three resultsets: one is for the overview table and two are for grouping sets.

select customer, year, product, sum(sales) from t1

group by grouping sets MULTIPLE RESULTSETS (

(customer, year), (product) );

HAVING clause

The HAVING clause is used to select the specified groups that satisfy the predicates. If this clause is omitted, all groups are selected.

<having_clause> ::= HAVING <condition>

SET OPERATORS

The set operators enable more than one select statements to be combined and only one result set is returned.

<set_operator> ::= UNION [ ALL | DISTINCT ] | INTERSECT [DISTINCT] | EXCEPT [DISTIN CT]

UNION ALL

Selects all records from all select statements. Duplicates are not removed.

UNION [DISTINCT]

Selects all unique records from all select statements by removing duplicates found from different select statements. UNION is the same as UNION DISTINCT.

INTERSECT [DISTINCT]

Returns all unique records that exist in all select statements in common.

EXCEPT [DISTINCT]

Returns all unique records from the first select statements after removing the duplicates in the following select statements.

ORDER BY clause

<order_by_clause> ::= ORDER BY { { <expression> | <position> } [ ASC | DESC ] }

The ORDER BY clause is used to sort records by expressions or positions. A position means the index of the select list. For "select col1, col2 from t order by 2", 2 indicates col2 as the second expression used in the select list. ASC is used to sort records in ascending order and DESC is used to sort records in descending order.

The default value is ASC.

LIMIT

LIMIT keyword specifies the number of output records.

<limit> ::= LIMIT n1 [ OFFSET n2 ]

LIMIT n1 [OFFSET n2]

Returns the first n1 records after skipping n2 ones.

FOR UPDATE

FOR UPDATE keywords lock the selected records so that other users cannot lock or change the records until end of this transaction.

<for_update> ::= FOR UPDATE

TIME TRAVEL

Keywords related wtih time travel can be used for statement-level time travel to go back to the snapshot specified by commit_id or timestamp.

Example

<time_travel> ::= AS OF [ COMMIT ID | TIMESTAMP ] [ <commit_id> | <timestamp> ]

UNLOAD

In document hana_sql_en (Page 94-101)