SQL References (H~Z)

INSERT INTO

Function

It creates new rows in a table.

Syntax

<insert statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
    ;

<insert source> ::=
      <values clause>
    | <from subquery>
    | <from default>

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<from default> ::=
    DEFAULT VALUES

Invocation and Access Rules

A user should satisfy the following conditions to perform <Insert statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table in which the row is to be created.
It can define schema to which the table belongs such as schema_name.table_name and if schema_name is omitted, the default schema name of the user performing the statement is used.

[ ( column_name [, ...] ) ]

It is the column name of a table.
The column list can be omitted.
The number of columns and the number of  <insert source> values should be same, and DEFAULT value is assigned to the omitted column.

<values clause>

It is the list of values to be assigned to the corresponding columns.

Multiple rows can be created as follows.

INSERT INTO table_name VALUES ( 1, 'A' ), ( 2, 'B' ), ( 3, 'C' )

<from subquery>

It is the query to create rows.
For more information, refer to query expression clause of SELECT statement.

DEFAULT VALUES

It fills every column with default value.
DEFAULT VALUES clause means as same as the following.
VALUES ( DEFAULT, DEFAULT, ..., DEFAULT )

Description

Differences among INSERT-related Statements

Examples

The following is an example of creating a single row by using INSERT statement.

gSQL> INSERT INTO region VALUES ( 0, 'AFRICA' );

1 row created.

The following is an example of using the DEFAULT value or identity value of the column in INSERT statement.

gSQL> CREATE TABLE region
(
    r_regionkey   BIGINT    GENERATED BY DEFAULT AS IDENTITY
  , r_name        CHAR(25)  DEFAULT 'N/A'
);

Table created.

gSQL> COMMIT;

Commit complete.

• DEFAULT is inserted into all columns.

gSQL> INSERT INTO region DEFAULT VALUES;

1 row created.

• DEFAULT is inserted into all columns.

gSQL> INSERT INTO region VALUES (DEFAULT, DEFAULT);

1 row created.

• If a column is omitted, the DEFAULT value of r_name column is used.

gSQL> INSERT INTO region(r_regionkey) VALUES (-100);

1 row created.

• If a column is omitted, the identity value of r_regionkey column is used.

gSQL> INSERT INTO region(r_name) VALUES ('ASIA');

1 row created.


gSQL> SELECT * FROM region;

R_REGIONKEY R_NAME                   
----------- -------------------------
          1 N/A                      
          2 N/A                      
       -100 N/A                      
          3 ASIA                     

4 rows selected.

The following is an example of creating multiple rows by describing them in VALUES clause.

gSQL> INSERT INTO region
       VALUES ( 1, 'AFRICA' ),
              ( 2, 'ASIA'   ),
              ( 3, 'EUROPE' );

3 rows created.

The following is an example of creating multiple rows by using a subquery.

gSQL> INSERT INTO region SELECT r_regionkey, r_name FROM tmp_region WHERE r_regionkey < 3;

3 rows created.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F781

Self-referencing operations

X

F222

INSERT statement: DEFAULT VALUES clause

O

S204

Enhanced structured types

X

S043

Enhanced reference types

X

T111

Updatable joins, unions, and columns

X

For More Information

Refer to the followings.

INSERT INTO name RETURNING

Function

It creates new rows in the table, and retrieves them.

Syntax

<insert statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <returning clause>
    ;

<insert source> ::=
      <values clause>
    | <from subquery>
    | <from default>

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<from default> ::=
    DEFAULT VALUES

<returning clause> ::=
      [ RETURN | RETURNING ] { * | { <value expression> [ [AS] alias_name ] } [, ...]

Invocation and Access Rules

A user should satisfy the following conditions to perform <insert returning query statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table in which the row is to be created.

[ ( column_name [, ...] ) ]

It is the column name of a table.
For more information, refer to INSERT INTO.

<values clause>

It is the list of values to be assigned to the corresponding columns.
For more information, refer to INSERT INTO.

<from subquery>

It is the query to create rows.
For more information, refer to INSERT INTO.

DEFAULT VALUES

It fills every column with default value.
For more information, refer to INSERT INTO.

<returning clause>

It returns the inserted rows.

The keywords RETURNING and RETURN have the same meaning.

Description

For more information, refer to Differences among INSERT-related Statements.

Examples

The following is an example of retrieving the column values created by using INSERT statement.

gSQL> CREATE TABLE region
(
    r_regionkey   BIGINT    GENERATED BY DEFAULT AS IDENTITY
  , r_name        CHAR(25)  DEFAULT 'N/A'
);

Table created.

gSQL> COMMIT;

Commit complete.
gSQL> INSERT INTO region VALUES ( DEFAULT, DEFAULT ) RETURNING r_regionkey, r_name;

R_REGIONKEY R_NAME                   
----------- -------------------------
          1 N/A                      

1 row created.
gSQL> INSERT INTO region(r_name) VALUES ('ASIA') RETURNING r_regionkey;

R_REGIONKEY
-----------
          2

1 row created.

The following is an example of retrieving the rows created by using the subquery.

gSQL> INSERT INTO region 
      SELECT r_regionkey, r_name FROM tmp_region WHERE r_regionkey < 3 
      RETURNING r_regionkey, r_name;

R_REGIONKEY R_NAME                   
----------- -------------------------
          0 AFRICA                   
          1 AMERICA                  
          2 ASIA                     

3 rows created.

Compatibility

The SQL standard does not define <insert returning query statement>.

For More Information

Refer to the followings.

INSERT INTO name RETURNING .. INTO

Function

It creates a single row in a table, and obtains the value of the created row into a host variable.

Syntax

<insert statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <returning into clause>
    ;

<insert source> ::=
      <values clause>
    | <from subquery>
    | <from default>

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<from default> ::=
    DEFAULT VALUES

<returning into clause> ::=
      [ RETURN | RETURNING ] { * | { <value expression> [ [AS] alias_name ] } [, ...] INTO variable_name [, ...]

Invocation and Access Rules

A user should satisfy the following conditions to perform <insert returning into statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table in which the row is to be created.

[ ( column_name [, ...] ) ]

It is the column name of a table.
For more information, refer to INSERT INTO.

<values clause>

It is the list of values to be assigned to the corresponding columns.
For more information, refer to INSERT INTO.

<from subquery>

It is the query to create rows.
For more information, refer to INSERT INTO.

DEFAULT VALUES

It fills every column with default value.
For more information, refer to INSERT INTO.

<returning clause>

It returns the inserted rows.
For more information, refer to <returning clause> in INSERT INTO name RETURNING statement.

INTO variable_name [, ...]

The number of variables in INTO clause should be equal to the number of the expressions in RETURNING clause.
The row to be created should be one or less. If two or more rows are created, an error occurs.

Description

For more information, refer to Differences among INSERT-related Statements.

Example

The following is an example of obtaining the value of the created row into a host variable.

gSQL> CREATE TABLE region
(
    r_regionkey   BIGINT    GENERATED BY DEFAULT AS IDENTITY
  , r_name        CHAR(25)  DEFAULT 'N/A'
);

Table created.

gSQL> COMMIT;

Commit complete.

• The host variables are declared.

\VAR v_key  BIGINT
\VAR v_name VARCHAR(128)

• The created DEFAULT values are obtained into the host variables.

gSQL> INSERT INTO region 
      VALUES ( DEFAULT, DEFAULT ) 
      RETURNING r_regionkey, r_name 
      INTO :v_key, :v_name;

V_KEY V_NAME                   
----- -------------------------
    1 N/A                      

1 row created.

• The omitted column value is obtained into the host variable.

gSQL> INSERT INTO region(r_name) 
      VALUES ('ASIA') 
      RETURNING r_regionkey 
      INTO :v_key;

V_KEY
-----
    2

1 row created.

Compatibility

The SQL standard does not define <insert returning into statement>.

For More Information

Refer to the followings.

INSERT INTO name ... UPDATE

Function

It creates new rows in a table. If it violates the unique constraint, then it updates the existing rows.

Syntax

<upsert statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <duplicate key clause>
    ;

<insert source> ::=
      <values clause>
    | <from subquery>
    | DEFAULT VALUES

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<duplicate key clause>
    ON DUPLICATE KEY { DO NOTHING | <do update clause> }

<do update clause> ::=
    [DO] UPDATE [SET] <set clause>  [, ...]

<set value clause> ::= 
      <value expression>
    | DEFAULT
    | VALUES( column_name )

<set clause> ::=
      column_name = <set value clause>
    | ( column_name [, ...] ) = ( <set value clause> [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

Invocation and Access Rules

A user should satisfy the following conditions to perform <upsert statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table in which the row is to be created.
Or, it is the name of a target table to be updated when they are updated because it violates the unique constraint. 
It can define schema to which the table belongs such as schema_name.table_name and if schema_name is omitted, the default schema name of the user performing the statement is used.

[ ( column_name [, ...] ) ]

It is the column name of a table.
For more information, refer to [ ( column_name [, ...] ) ] clause of INSERT INTO statement.

<values clause>

It is the list of values to be assigned to the corresponding columns.
For more information, refer to <values clause> of INSERT INTO statement.

<from subquery>

It is the query to create rows.
For more information, refer to query expression clause of SELECT statement.

DEFAULT VALUES

It fills every column with default value.
For more information, refer to DEFAULT VALUES clause of INSERT INTO statement.

<duplicate key clause>

It defines the action to perform when it violates the unique constraint.

DO NOTHING

It does not perform any operation when it violates the unique constraint.

<do update clause>

It updates the values in columns according to <set clause> when it violates the unique constraint.

<set value clause>

It defines the values to assign to the columns to be updated.

It can be defined as follows.

DO UPDATE SET column1 = value1, column2 = value2, column3 = value3
DO UPDATE SET column1 = DEFAULT, column2 = DEFAULT, column3 = DEFAULT

<insert source> value is used to update the value.

DO UPDATE SET column1 = VALUES(column1), column2 = VALUES(column2), column3 = VALUES(column2)

<set clause>

It defines the columns to be updated and the values to be assigned, and the number of columns in <set clause> and the number of values should be same.

It can be defined as follows.

ON DUPLICATE KEY
   DO UPDATE SET column1 = value1, column2 = value2, column3 = value3
ON DUPLICATE KEY
   DO UPDATE SET ( column1, column2, column3 ) = ( value1, value2, value3 )
ON DUPLICATE KEY
   DO UPDATE SET column1 = ( SELECT max(value1) FROM other_table_name )

<query expression> should be a query creating a single row.

If DEFAULT is used as a column value, it uses the default value (refer to <default clause>.) defined when performing CREATE TABLE, and NULL value is assigned when it is not defined.

Description

Differences among INSERT INTO name ... UPDATE-related Statements

<upsert statement> is a deterministic statement.

The results of the two equivalent and different UPSERT statements should be same as follows.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 );

3 rows created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

3 rows created.

gSQL> SELECT * FROM t1;

C1
--
 2
 3
 4

3 rows selected.
gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 );

3 rows created.

gSQL> INSERT INTO t1 VALUES( 3 ),( 2 ),( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

3 rows created.

gSQL> SELECT * FROM t1;

C1
--
 2
 3
 4

3 rows selected.

Examples

The following is an example of updating a single row because it violates the unique constraint.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 );

1 row created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

1 row created.

gSQL> SELECT * FROM t1;

C1
--
 2

1 row selected.

The following is an example of not updating a row even when it violates the unique constraint.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 );

1 row created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY DO NOTHING;

no rows created.

gSQL> SELECT * FROM t1;

C1
--
 1

1 row selected.

The following is an example of inserting or updating multiple rows by using a subquery.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 ),( 4 );

4 rows created.

gSQL> INSERT INTO t1 ( SELECT c1 FROM t1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

4 rows created.

gSQL> SELECT * FROM t1;

C1
--
 2
 3
 4
 5

4 rows selected.

Compatibility

The SQL standard does not define <upsert statement>.

For More Information

Refer to the followings.

INSERT INTO name ... UPDATE RETURNING

Function

It creates new rows in a table. If it violates the unique constraint, then it updates the existing rows.
Then, it retrieves the created rows or the updated rows.

Syntax

<upsert returning statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <duplicate key clause>
        <returning clause>    
    ;
<insert source> ::=
      <values clause>
    | <from subquery>
    | DEFAULT VALUES

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<duplicate key clause>
    ON DUPLICATE KEY { DO NOTHING | <do update clause> }

<do update clause> ::=
    [DO] UPDATE [SET] <set clause>  [, ...]

<set value clause> ::= 
      <value expression>
    | DEFAULT
    | VALUES( column_name )

<set clause> ::=
      column_name = <set value clause>
    | ( column_name [, ...] ) = ( <set value clause> [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

<returning clause> ::=
    [ RETURN | RETURNING ] { * | { <value expression> [ [AS] alias_name ] } [, ...]

Invocation and Access Rules

A user should satisfy the following conditions to perform <upsert returning statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table in which the row is to be created.
Or, it is the name of a target table to be updated when they are updated because it violates the unique constraint. 
For more information, refer to table_name clause of INSERT INTO name ... UPDATE statement.

[ ( column_name [, ...] ) ]

It is the column name of a table.
For more information, refer to [ ( column_name [, ...] ) ] clause of INSERT INTO statement.

<values clause>

It is the list of values to be assigned to the corresponding columns.
For more information, refer to <values clause> of INSERT INTO statement.

<from subquery>

It is the query to create rows.
For more information, refer to query expression clause of SELECT statement.

DEFAULT VALUES

It fills every column with default value.
For more information, refer to DEFAULT VALUES clause of INSERT INTO statement.

<duplicate key clause>

It defines the action to perform when it violates the unique constraint.

DO NOTHING

It does not perform any operation when it violates the unique constraint.

<do update clause>

It updates the values in columns according to <set clause> when it violates the unique constraint.

<set value clause>

It defines the values to assign to the columns to be updated.
For more information, refer to <set value clause> of INSERT INTO name ... UPDATE statement.

<set clause>

It defines the columns to be updated and the values to be assigned, and the number of columns in <set clause> and the number of values should be same.
For more information, refer to <set clause> of INSERT INTO name ... UPDATE.

<returning clause>

It returns the inserted rows or the updated rows.

Description

For more information, refer to Differences among INSERT INTO name ... UPDATE-related Statements.

The following is an example of inserting four rows, and returning the inserted results.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ), ( 2 ), ( 3 ), ( 4 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1;

C1
--
 1
 2
 3
 4

4 rows created.

The following is an example of updating rows because it violates the unique constraint, and returning the updated results.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 ),( 4 );

4 rows created.

gSQL> INSERT INTO t1 ( SELECT c1 FROM t1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1;

C1
--
 2
 3
 4
 5

4 rows created.

Compatibility

The SQL standard does not define <upsert returning statement>.

For More Information

Refer to the followings.

INSERT INTO name ... UPDATE RETURNING ... INTO

Function

It creates a single row in a table. If it violates the unique constraint, then it updates the existing rows.
Then, it obtains the created rows or the updated rows as the host variable.

Syntax

<upsert returning into statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <duplicate key clause>
        <returning clause>    
        <into clause>    
    ;
<insert source> ::=
      <values clause>
    | <from subquery>
    | DEFAULT VALUES

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<duplicate key clause>
    ON DUPLICATE KEY { DO NOTHING | <do update clause> }

<do update clause> ::=
    [DO] UPDATE [SET] <set clause>  [, ...]

<set value clause> ::= 
      <value expression>
    | DEFAULT
    | VALUES( column_name )

<set clause> ::=
      column_name = <set value clause>
    | ( column_name [, ...] ) = ( <set value clause> [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

<returning clause> ::=
    [ RETURN | RETURNING ] { * | { <value expression> [ [AS] alias_name ] } [, ...] 

<into clause> ::= INTO variable_name [, ...]

Invocation and Access Rules

A user should satisfy the following conditions to perform <upsert returning into statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table in which the row is to be created.
Or, it is the name of a target table to be updated when they are updated because it violates the unique constraint. 
For more information, refer to table_name clause of INSERT INTO name ... UPDATE statement.

[ ( column_name [, ...] ) ]

It is the column name of a table.
For more information, refer to [ ( column_name [, ...] ) ] clause of INSERT INTO statement.

<values clause>

It is the list of values to be assigned to the corresponding columns.
For more information, refer to <values clause> of INSERT INTO statement.

<from subquery>

It is the query to create rows.
For more information, refer to query expression clause of SELECT statement.

DEFAULT VALUES

It fills every column with default value.
For more information, refer to DEFAULT VALUES clause of INSERT INTO statement.

<duplicate key clause>

It defines the action to perform when it violates the unique constraint.

DO NOTHING

It does not perform any operation when it violates the unique constraint.

<do update clause>

It updates the values in columns according to <set clause> when it violates the unique constraint.

<set value clause>

It defines the values to assign to the columns to be updated.
For more information, refer to <set value clause> of INSERT INTO name ... UPDATE statement.

<set clause>

It defines the columns to be updated and the values to be assigned, and the number of columns in <set clause> and the number of values should be same.
For more information, refer to <set clause> of INSERT INTO name ... UPDATE.

<returning clause>

It returns the inserted rows or the updated rows.
For more information, refer to <returning clause> of INSERT INTO name ... UPDATE RETURNING.

<into clause>

The number of variables specified in INTO clause should be same as the number of expressions specified in RETURNING clause. 
The row should be created one or less. If two or more rows are created, then an error occurs.

Description

For more information, refer to Differences among INSERT INTO name ... UPDATE-related Statements.

The following is an example of inserting a single row, then obtaining the inserted result as the host variable.

gSQL> \VAR v_c1 INTEGER;

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1 INTO :v_c1;

V_C1
----
   1

1 row created.

The following is an example of updating a single row because it violates the unique constraint, then obtaining the updated result as the host variable.

gSQL> \VAR v_c1 INTEGER;

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 );

1 row created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1 INTO :v_c1;

V_C1
----
   2

1 row created.

Compatibility

The SQL standard does not define <upsert returning into statement>.

For More Information

Refer to the followings.

LOCK TABLE

Function

It locks one or more tables.

Syntax

<lock table statement> ::=
    LOCK TABLE lock target [, ...] 
    IN <lock mode> MODE [<wait clause>]
    ;

<lock mode> ::=
    SHARE
    | EXCLUSIVE
    | ROW SHARE
    | ROW EXCLUSIVE
    | SHARE ROW EXCLUSIVE


<wait clause> ::=
    NOWAIT
    | WAIT time

Invocation and Access Rules

One of the following privileges is required to perform <lock table statement>.

Syntax Rules and Parameters

<lock target>

It specifies the target table to be locked.

<lock mode>

It specifies the LOCK mode.

<wait clause>

It specifies the waiting time to acquire the lock.

Description

If the transaction is committed or rolled back all acquired locks are automatically released. When using ROLLBACK TO SAVEPOINT statement, all locks acquired since that savepoint are released.

Examples

The following is an example of locking the TABLE t1 to prevent any updating operation by another transaction.

gSQL> LOCK TABLE t1 IN EXCLUSIVE MODE;

Table locked.

The following is an example of performing LOCK statement for multiple tables.

gSQL> LOCK TABLE t1, t2 IN EXCLUSIVE MODE;

Table locked.

The following is an example of acquiring SHARE ROW EXCLUSIVE lock for the TABLE t1.

gSQL> LOCK TABLE t1 IN SHARE ROW EXCLUSIVE MODE;

Table locked.

The following statement is performed only when the lock can be immediately acquired for the table. If the lock can not be acquired, an error occurs.

gSQL> LOCK TABLE t1 IN EXCLUSIVE MODE NOWAIT;

Table locked.

The following is an example of waiting 10 seconds to acquire the lock.

gSQL> LOCK TABLE t1 IN EXCLUSIVE MODE WAIT 10;

Table locked.

Compatibility

The SQL standard does not cover the concepts of the lock table.

For More Information

Refer to the followings.

NOAUDIT POLICY

Function

It deactivates the audit policy.

Syntax

<noaudit policy statement> ::= 
    NOAUDIT POLICY policy_name
    [ <specified_user_option> ]
    ;

<specified_user_option> ::=
      BY user_name [, ...]

Invocation and Access Rules

AUDIT SYSTEM ON DATABASE privilege is required to perform <noaudit policy statement>.

Syntax Rules and Parameters

policy_name

It is the name of the audit policy object to be deactivated.
The deactivated audit policy does not effect on the existing session, and it effects only on the newly created session.

<specified_user_option>

It specifies the user to be excluded from the auditing target.

Unlike AUDIT POLICY statement, NOAUDIT POLICY does not have EXCEPT option.

If AUDIT POLICY name BY clause is used, NOAUDIT POLICY name BY statement should be used to deactivate it.
If AUDIT POLICY name EXCEPT clause is used, NOAUDIT POLICY name statement without BY clause should be used to deactivate it.

NOAUDIT POLICY statement should be used as follows according to the usage of AUDIT POLICY statement to deactivate it.

Activating/ deactivating audit policy

Type

AUDIT POLICY statement

NOAUDIT POLICY statement

All users

AUDIT POLICY p1

NOAUDIT POLICY p1

Using BY

AUDIT POLICY p1 BY u1

NOAUDIT POLICY p1 BY u1

Using EXCEPT

AUDIT POLICY p1 EXCEPT u1

NOAUDIT POLICY p1

When deactivating all activated users, the audit policy object is completely deactivated.

Description

The activation information of an audit policy object can be queried as follows.

SELECT policy_name
     , enabled_opt
     , user_name
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';
NOAUDIT POLICY statement deletes each created information about activationaccording to the AUDIT POLICY specifying method.
If the information activated through the query above does not exist, then the audit policy is completely deactivated.

If all users are activated as follows, NOAUDIT POLICY BY clause does not does not affect it.

AUDIT POLICY p1;
NOAUDIT POLICY p1 BY u1;
NOAUDIT POLICY p1;

If one or more users are separately activated, use NOAUDIT POLICY statement according to the AUDIT POLICY specifying method.

When Activated by Using BY

If the audit policy is activated as follows,

AUDIT POLICY p1 WHENEVER NOT SUCCESSFUL;
AUDIT POLICY p1 BY u1;
AUDIT POLICY p1 BY u2;

the information about activation is as follows.

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

POLICY_NAME  ENABLED_OPT  USER_NAME    WHEN_SUCCESS  WHEN_FAILURE
-----------  -----------  ---------    ------------  ------------
P1           BY           ALL USERS    NO            YES
P1           BY           U1           YES           YES
P1           BY           U2           YES           YES

The following is an example of performing NOAUDIT statement and the information about activation.

NOAUDIT POLICY p1;

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

POLICY_NAME  ENABLED_OPT  USER_NAME    WHEN_SUCCESS    WHEN_FAILURE
-----------  -----------  ---------    ------------    ------------
P1           BY           U1           YES             YES
P1           BY           U2           YES             YES
The auditing for a failure for ALL USERS is deactivated, but the auditing for user u1, u2 is still activated.

If NOAUDIT POLICY statement is additionally used through BY option as follows, then audit policy p1 is completely deactivated.

NOAUDIT POLICY p1 BY u1, u2;

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

no rows selected.

When Activated by Using EXCEPT

If the audit policy is activated as follows,

AUDIT POLICY p1 EXCEPT u1, sys;

the information about activation is as follows.

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

POLICY_NAME  ENABLED_OPT  USER_NAME    WHEN_SUCCESS    WHEN_FAILURE
-----------  -----------  ---------    ------------    ------------
P1           EXCEPT       U1           YES             YES
P1           EXCEPT       SYS          YES             YES
Unlike AUDIT POLICY statement, NOAUDIT POLICY does not have EXCEPT option, so execute the statement without an option as follows.
NOAUDIT POLICY p1;

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

no rows selected.
In other words, if the audit policy is activated by using EXCEPT option, each user can not be deactivated again by using NOAUDIT POLICY statement.

Examples

The following is an example of deactivating all users.

NOAUDIT POLICY table_pol;

The following is an example of deactivating a specific activated user by using BY.

NOAUDIT POLICY table_pol BY u1;

Compatibility

The SQL standard does not have the audit policy.

For More Information

Refer to the followings.

OPEN cursor_name

Function

It opens a cursor.

Syntax

<open statement> ::=
    OPEN cursor_name [ <parameter using clause> ]
    ;

<parameter using clause> ::=
      <using parameter arguments>

<using parameter arguments> ::=
    USING variable_name [, ...]

Invocation and Access Rules

If cursor_name is a dynamic cursor which is declared by using PREPARE statement_name and DECLARE cursor_name, it can be used in an embedded SQL.
It is same with the privilege of <cursor query> included in DECLARE cursor_name which declared cursor_name.

Syntax Rules and Parameters

cursor_name

It should be a cursor declared with DECLARE cursor_name within the session.

<parameter using clause>

It can be used in an embedded SQL.
When <parameter using clause> is used, cursor_name should be a dynamic cursor declared by using PREPARE statement_name and DECLARE cursor_name.

<using parameter arguments>

When <using parameter arguments> is used, the number of variable_name should be equal to the number of the parameter included in a query which is referenced by PREPARE statement_name.
The listed variable_name corresponds to the dynamic parameter in an order of its description.
{
    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT c1, c2 FROM t1 WHERE c1 IN ( ?, ?, ? )';
    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
    EXEC SQL OPEN cur1 USING :sValue1, :sValue2, :sValue3;
    ...
    EXEC SQL WHENEVER NOT FOUND DO break;
    for(;;)
    {
        EXEC SQL FETCH cur1 INTO :sC1, :sC2;    
    }
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    ...
    EXEC SQL CLOSE cur1;    
    ... 
}

Description

The cursor is a distinguishable object in a session. The cursor being used in the current session has nothing to do with the cursor being used in another session.
To use OPEN cursor_name statement, it should be a cursor declared with DECLARE cursor_name, and it should be a closed cursor.

Examples

The following is an example of declaring a cursor and using OPEN cursor statement in an interactive SQL (gsql).

gSQL> DECLARE cur1 CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur1;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

no rows fetched.

gSQL> CLOSE cur1;

Cursor closed.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

B031

Basic Dynamic SQL

O

For More Information

Refer to the followings.

PREPARE statement_name

Function

It prepares a dynamic SQL statement for a repeated execution.

Syntax

<prepare statement> ::=
    PREPARE statement_name FROM <SQL statement variable>
    ;

<SQL statement variable> ::=
      variable_name
    | 'sql statement'
    | "sql statement"
    | sql statement

Invocation and Access Rules

It can be used in an embedded SQL.
An appropriate privilege according to the type of a dynamic SQL statement is required.

Syntax Rules and Parameters

statement_name

It is the name of the statement to be prepared.
The length of the statement name should be shorter than 128 bytes.
EXECUTE statement_name and DECLARE cursor_name, which are to be performed later, refers to the statement_name.
If the same statement_name exists, the previously prepared dynamic SQL is dropped.
{
    ...

    EXEC SQL PREPARE stmt1 FROM 'DELETE FROM t1';
    ...
    EXEC SQL PREPARE stmt1 FROM 'UPDATE t1 SET c1 = c1 + 10';
    ...
}

<SQL statement variable>

<SQL statement variable> can be used as following four types.

The single quote (') is used twice as follows to represent string data within single-quoted string.

{
    ...
    PREPARE stmt_name FROM 'INSERT INTO t1 VALUES ( ''literal data'' )'; 
    ...
}
The dynamic SQL statement referenced by <SQL statement variable> can use a host variable (:var) or parameter marker (?). 
However, if the unquoted SQL statement is used, the parameter marker (?) can not be used.
Depending on the characteristics of the referenced dynamic SQL statements, the variable can be either input or output dynamic parameter. 
The dynamic parameter described in the dynamic SQL statement does not have a meaning for the variable name, and it is identified by the specified order regardless of its type.
{
    ...
    int sValue1;
    int sValue2;
    ...
    EXEC SQL PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 BETWEEN ? AND ?';
    EXEC SQL EXECUTE stmt1 USING :sValue1, :sValue2;   
    ...
}
{
    ...
    int sValue1;
    int sValue2;
    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT SUM(c2) INTO :v1 FROM t1 WHERE c1 > :v2';
    EXEC SQL EXECUTE stmt1 USING :sValue1, :sValue2;
    ...
}

variable_name

The type corresponding to variable_name should be a character string.
The dynamic SQL statement defined in variable_name should be valid.

sql statement

The dynamic SQL statement defined in the sql statement should be valid.

Description

PREPARE statement_name FROM sql_string statement analyzes SQL statement to use EXECUTE or cursor statement. Statement_name is an identifier which informs the precompiler the statement in an embedded SQL source code. A separate type or declaration is not required because statement_name is not a host variable.
For more information, refer to Embedded Dynamic SQL.

Example

The following is an example of using PREPARE statement_name in an embedded SQL source code.

{
    ...
    sprintf( sUpdateSql, "UPDATE EMP SET sal = sal * :v1 WHERE JOB = 'SALES'");
    EXEC SQL PREPARE UPDATE_STMT FROM :sUpdateSql;
    if(sqlca.sqlcode != 0)
    {
        goto fail_exit;
    }

    sRatio = 1.1;
    EXEC SQL EXECUTE UPDATE_STMT USING :sRatio;
    if(sqlca.sqlcode != 0)
    {
        goto fail_exit;
    }
    ...
}

The full source code in which PREPARE statement_name was used can be viewed in Dynamic Embedded SQL Example Program.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

B031

Basic Dynamic SQL

O

B034

Dynamic specification of cursor attributes

X

For More Information

Refer to the followings.

PURGE

Function

It permanently drops objects stored in the recycle bin.

Syntax

<purge statement> :==
    PURGE <purge action>
    ;

<purge action> :==
     TABLE table_name
   | INDEX index_name
   | CONSTRAINT constraint_name
   | TABLESPACE tablespace_name [ USER user_name ]
   | RECYCLEBIN 
   | USER_RECYCLEBIN
   | DBA_RECYCLEBIN

Invocation and Access Rules

One of the following privileges is required for a user to perform <purge statement>.

Syntax Rules and Parameters

table_name

It is the name of the object stored or of the dropped table in the recycle bin.
It can define the schema to which the table belongs in the dropped table name, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used. In this case, indexes and constraints which are related to the table are also dropped.

index_name

It is the name of the object stored or of the dropped index in the recycle bin. 
It can define the schema to which the index belongs such as schema_name.index_name. 
If schema_name is omitted, the default schema name of the user performing the statement is used. 
The key index which is created with a constraint should be dropped with the constraint.

constraint_name

It is the name of the object stored or of the dropped constraint in the recycle bin.

tablespace_name

It is the name of the tablespace. 
When assigning USER, DROP ANY TABLE ON DATABASE privilege is required.

user_name

It is the name of the user.

recyclebin

It is the alias of user_recyclebin.

user_recyclebin

It drops all recycle bins owned by a user.

dba_recyclebin

It drops all recycle bins in the database. 
PURGE DBA_RECYCLEBIN ON DATABASE privilege is required.

Description

It permanently drops objects stored in the recycle bin by using the object name or the dropped table name stored in the recycle bin. If the name which is as same as that of the dropped table exists, then the oldest table object is dropped.
When specifying a tablespace in the recycle bin object owned by a user, then only the objects included in the tablespace are dropped. In this case, if a user is assigned, then only the objects included in the specified tablespace owned by the user are dropped.
PURGE TABLE, INDEX, CONSTRAINT statements  can be rolled back if it is before when the transaction is committed. However, PURGE TABLESPACE, RECYCLEBIN, DBA_RECYCLEBIN statements can not be rolled back, and the transaction which performed the statement is automatically committed.

Example

The following is an example of dropping a table stored in the recycle bin.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$135B9908166111EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$135B993A166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$135B991C166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      
BIN$135B9926166111EA9C5C835D3E4BBBF7 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE TABLE t1;

Table purged.

The following is an example of dropping an index stored in the recycle bin.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$135B9908166111EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$135B993A166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$135B991C166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      
BIN$135B9926166111EA9C5C835D3E4BBBF7 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE INDEX t1_idx1;

Index purged.

The following is an example of dropping constraints stored in the recycle bin.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$135B9908166111EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$135B993A166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$135B991C166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      

3 rows selected.

gSQL> PURGE CONSTRAINT t1_primary_key;

Constraints purged.

The following is an example of dropping objects included in the tablespace stored in the recycle bin.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE, TABLESPACE_NAME FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME OBJECT_TYPE TABLESPACE_NAME
------------------------------------ ------------- ----------- ---------------
BIN$02C76B24166311EA9C5C835D3E4BBBF7 T1            TABLE       MEM_DATA_TBS   

1 row selected.

gSQL> PURGE TABLESPACE MEM_DATA_TBS;

Tablespace purged.

The following is an example of dropping all recycle bins owned by a user.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$64F6BFFC166311EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$64F6C042166311EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$64F6C010166311EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      
BIN$64F6C024166311EA9C5C835D3E4BBBF7 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE USER_RECYCLEBIN;

Recyclebin purged.

The following is an example of dropping all recycle bins in the system.

gSQL> SELECT OWNER, OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OWNER OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
----- ------------------------------------ -------------------- -----------
TEST  BIN$F0FB26F0166311EAA7C5D51B86D72AB6 T1                   TABLE      
TEST  BIN$F0FB272C166311EAA7C5D51B86D72AB6 T1_PRIMARY_KEY       CONSTRAINT 
TEST  BIN$F0FB2704166311EAA7C5D51B86D72AB6 T1_PRIMARY_KEY_INDEX INDEX      
TEST  BIN$F0FB2718166311EAA7C5D51B86D72AB6 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

Compatibility

The SQL standard does not define <purge statement>.

For More Information

Refer to the followings.

RELEASE SAVEPOINT savepoint_specifier

Function

It releases a savepoint.

Syntax

<release savepoint statement> ::=
    RELEASE SAVEPOINT savepoint_name 
    ;

Syntax Rules and Parameters

savepoint_name

It is a name of the savepoint, and it should exist. 
The length of the savepoint name should be shorter than 128 bytes.

Description

If multiple savepoints are defined and RELEASE SAVEPOINT savepoint_name statement is performed, all savepoints defined since the savepoint_name are also released.

Example

The following is an example of releasing a savepoint.

gSQL> RELEASE SAVEPOINT sp2;

Savepoint dropped.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T271

Savepoints

O

For More Information

Refer to the followings.

REVOKE privileges FROM

Function

It revokes the granted privilege from a user.

Syntax

<revoke privilege statement> ::=
    REVOKE [ <revoke option extention> ] <privilege>
      FROM <grantee> [, ...]
      [ <revoke behavior> ]
    ;

<revoke option extention> ::=
      GRANT OPTION FOR

<revoke behavior> ::=
      RESTRICT
    | CASCADE
    | CASCADE CONSTRAINTS

Syntax Rules and Parameters

<privilege>

It is a privilege which is to be revoked from the revokee (the user whose privilege is to be revoked).
The revoker (the user who performs the statement) should satisfy one of the following conditions.
When using ALL [PRIVILEGES], it succeeds even when the satisfying <privilege> does not exist.
For more information about the types of <privilege>, refer to <privilege> clause of GRANT privileges TO statement.

<grantee>

It is a user whose privilege is to be revoked.

GRANT OPTION FOR

It revokes WITH GRANT OPTION included in the privilege. 
It also revokes WITH GRANT OPTION of the dependent privilege.
The privilege is maintained.

<revoke behavior>

Description

Data Definition Language (DDL) such as REVOKE privilege can be rolled back if it is before when the transaction is committed.
When performing the following DROP statement, all privilege information related to the object is revoked even without performing any separate REVOKE statement.

Examples

The following is an example of revoking multiple privileges for the table t1.

gSQL> REVOKE INSERT, UPDATE, DELETE, LOCK, ALTER, INDEX ON t1 FROM u1;

Revoke succeeded.

The following is an example of revoking SELECT ON TABLE t1 privilege granted to the PUBLIC account, which means all users. However, only the privilege for PUBLIC account is revoked, and SELECT ON TABLE t1 privilege which was explicitly granted to a specific user is not revoked.

gSQL> REVOKE SELECT ON t1 FROM PUBLIC;

Revoke succeeded.

The following is an example that SELECT ON TABLE t1 privilege granted to user u1 is remained, and only REVOKE GRANT OPTION which can grant the privilege to another user is revoked.

gSQL> REVOKE GRANT OPTION FOR SELECT ON t1 FROM u1;

Revoke succeeded.

The following is an example that an error occurs when the privilege granted to the user u1 is revoked by using RESTRICT option and the user u1 grants it to another user. CASCADE option is used to revoke these dependent privileges as well.

gSQL> REVOKE SELECT ON t1 FROM u1 RESTRICT;

ERR-2B000(16235): dependent privilege descriptors still exist

gSQL> REVOKE SELECT ON t1 FROM u1 CASCADE;

Revoke succeeded.

Compatibility

The SQL standard does not define the following privileges.

<revoke behavior> of the SQL standard has the following differences.

SQL standard compatibility

Feature ID

Description

Compatibility

T311

Basic roles

X

F034

Extended REVOKE statement

X

S081

Subtables

X

For More Information

Refer to the followings.

ROLLBACK

Function

It rolls back a transaction, or the operation after the savepoint.

Syntax

<rollback statement> ::=
    ROLLBACK [ WORK ] [ <rollback force clause> | <savepoint clause> ]
    ;

<rollback force clause> ::=
    FORCE 'xid_string' [ COMMENT 'comment_string' ]

<savepoint clause> ::=
    TO SAVEPOINT savepoint_name

Syntax Rules and Parameters

WORK

It is a reserved word which does not affect the operation.

<rollback force clause>

It is used to manually rollback the distributed transaction.

<savepoint clause>

It specifies the rollback scope of the current transaction.

Description

ROLLBACK statement undoes the following statements performed in the transaction.

Exceptionally, the following statements of DDL which deals with OS resources or alters the DATA TYPE can not be rolled back, but are automatically committed when executing the statement.

Examples

The following is an example of rolling back the INSERT statement.

gSQL> INSERT INTO t1 VALUES ( 1, 'anonymous' );

1 row created.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous

1 row selected.

gSQL> ROLLBACK;

Rollback complete.

gSQL> SELECT * FROM t1;

no rows selected.

The following is an example of ROLLBACK after performing the DROP TABLE statement.

gSQL> DROP TABLE t1;

Table dropped.

gSQL> SELECT * FROM t1;

ERR-42000(16040): table or view does not exist : 
SELECT * FROM t1
              *
ERROR at line 1:

gSQL> ROLLBACK;

Rollback complete.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T271

Savepoints

O

T261

Chained transactions

X

For More Information

Refer to the followings.

SAVEPOINT savepoint_specifier

Function

It defines a savepoint.

Syntax

<savepoint statement> ::=
    SAVEPOINT savepoint_name 
    ;

Syntax Rules and Parameters

savepoint_name

It is a name of the savepoint.
If the savepoint name is as same as the existing savepoint name, then the existing savepoint is deleted.
The length of the savepoint name should be shorter than 128 bytes.

Description

The defined savepoint is used by ROLLBACK TO SAVEPOINT statement (refer to ROLLBACK.), and DML or DDL statement which has been performed up to the savepoint is rolled back. Then the locks acquired by using that statement are released, too.

The defined savepoint is automatically deleted when the transaction is committed or rolled back, or it can be explicitly deleted by using RELEASE SAVEPOINT savepoint_specifier.

Example

The following is an example of defining the savepoint and using ROLLBACK TO SAVEPOINT statement.

gSQL> SAVEPOINT sp1;

Savepoint created.

gSQL> INSERT INTO t1 VALUES ( 1, 'anonymous' );

1 row created.

gSQL> SAVEPOINT sp2;

Savepoint created.

gSQL> INSERT INTO t1 VALUES ( 2, 'someone' );

1 row created.

gSQL> SAVEPOINT sp3;

Savepoint created.

gSQL> INSERT INTO t1 VALUES ( 3, 'anyone' );

1 row created.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous
 2 someone  
 3 anyone   

3 rows selected.

gSQL> ROLLBACK TO SAVEPOINT sp3;

Rollback complete.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous
 2 someone  

2 rows selected.

gSQL> ROLLBACK TO SAVEPOINT sp2;

Rollback complete.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous

1 row selected.

gSQL> ROLLBACK TO SAVEPOINT sp1;

Rollback complete.

gSQL> SELECT * FROM t1;

no rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T271

Savepoints

O

For More Information

Refer to the followings.

SELECT

query expression

Function

It retrieves desired rows from one or more tables or views.

Syntax

<query expression> ::=
    [ <with clause> ] <query expression body> [ <order by clause> ] [ <offset limit clause> ]

<query expression body> ::=
      <query term>
    | <set operator>

<query term> ::=
      <query specification>
    | <left paren> <query expression body> [ <order by clause> ] [ <offset limit clause> ] <right paren>

Invocation and Access Rules

One of the following privileges for all tables used in the statement is required for a user to perform <query expression>.

Syntax Rules and Parameters

<with clause>

<with clause> defines the temporary result set, and it refers to that result set. 
For more information, refer to with clause.

<set operator>

It performs a set operation among the subqueries.
For more information, refer to set operator.

<query specification>

It specifies a single subquery. 
For more information, refer to <query specification>.

<order by clause>

It specifies sorting information of a query result. 
For more information, refer to order by clause.

<offset limit clause>

It specifies the number of rows to skip and the number of rows to fetch from the query result set. 
For more information, refer to offset limit clause.

Description

It specifies a query with SELECT statement. 
<with clause>, <order by clause>, <offset limit clause> can be omitted.
Two or more subqueries can be specified by using <set operator>.

Examples

The following is an example of SELECT statement.

gSQL> SELECT s_name, s_nation FROM supplier;

S_NAME                    S_NATION     
------------------------- -------------
Supplier#1                FRANCE       
Supplier#2                KOREA        
Supplier#3                GERMANY      
Supplier#4                UNITED STATES
Supplier#5                CANADA       

5 rows selected.

The following is an example of the SELECT statement which uses <order by clause>.

gSQL> SELECT s_name, s_nation FROM supplier ORDER BY s_name DESC;

S_NAME                    S_NATION
------------------------- -------------
Supplier#5                CANADA
Supplier#4                UNITED STATES
Supplier#3                GERMANY
Supplier#2                KOREA
Supplier#1                FRANCE

5 rows selected.

The following is an example of the SELECT statement which uses <offset limit clause>.

gSQL> SELECT s_name, s_nation FROM supplier OFFSET 1;

S_NAME                    S_NATION
------------------------- -------------
Supplier#2                KOREA
Supplier#3                GERMANY
Supplier#4                UNITED STATES
Supplier#5                CANADA

4 rows selected.

gSQL> SELECT s_name, s_nation FROM supplier LIMIT 1; 

S_NAME                    S_NATION
------------------------- --------
Supplier#1                FRANCE  

1 row selected.

The following is an example of the SELECT statement which uses <order by clause> and <offset limit clause>.

gSQL> SELECT s_name, s_nation FROM supplier ORDER BY s_name DESC OFFSET 3 LIMIT 1; 

S_NAME                    S_NATION
------------------------- --------
Supplier#2                KOREA   

1 row selected.

The following is an example of SELECT statement which uses <with clause>.

* Non Recursive CTE

gSQL> WITH revenue ( supplier_no, total_revenue ) AS
      (
            SELECT
                   l_suppkey,
                   SUM(l_extendedprice * (1 - l_discount))
              FROM lineitem
             WHERE l_shipdate >= DATE '1996-01-01'
               AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
             GROUP BY
               l_suppkey
      )
    select
        s_suppkey,
        s_name,
        s_address,
        s_phone,
        ROUND( total_revenue, 2 ) as total_revenue
    from
        supplier,
        revenue
    where
          s_suppkey = supplier_no
      and total_revenue = (
                            select
                                max(total_revenue)
                            from
                                revenue
                          )
    order by
       s_suppkey;

S_SUPPKEY S_NAME                    S_ADDRESS         S_PHONE         TOTAL_REVENUE
--------- ------------------------- ----------------- --------------- -------------
     8449 Supplier#000008449        Wp34zim9qYFbVctdW 20-469-856-8873    1772627.21

1 row selected.


* Recursive CTE

gSQL> WITH GenerateRecord ( c1, c2 ) AS 
     (
          SELECT 1, 11
            FROM dual
          UNION ALL
          SELECT c1 + 1, c2 + 1
            FROM GenerateRecord
           WHERE c1 < 10
     )
SELECT c1, c2 FROM GenerateRecord;

C1 C2
-- --
 1 11
 2 12
 3 13
 4 14
 5 15
 6 16
 7 17
 8 18
 9 19
10 20

10 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T121

WITH(excluding RECURSIVE) in query expression

O

T122

WITH(excluding RECURSIVE) in subquery

O

T131

Recursive query

O

T132

Recursive query in subquery

O

F661

Simple tables

O

F302

INTERSECT table operator

O

F301

CORRESPONDING in query expressions

X

T551

Optional key words for default syntax

O

F304

EXCEPT ALL table operator

O

F850

Top-level <order by clause> in <query expression>

O

F851

<order by clause> in subqueries

O

F855

Nested <order by clause> in <query expression>

O

F856

Nested <fetch first clause> in <query expression>

O

F857

Top-level <fetch first clause> in <query expression>

O

F858

<fetch first clause> in subqueries

O

F860

dynamic <fetch first row count> in <fetch first clause>

X

F861

Top-level <result offset clause> in <query expression>

O

F862

<result offset clause> in subqueries

O

F863

Nested <result offset clause> in <query expression>

O

F865

dynamic <offset row count> in <result offset clause>

X

F866

FETCH FIRST clause: PERCENT option

X

F867

FETCH FIRST clause: WITH TIES option

X

with clause

Function

<with clause> defines the temporary result set, and it refers to that result set. 
The result set a temporary result set with a given name, and it is defined and referred in SELECT statement. It is called as Common Table Expression (CTE).

Syntax

<with clause> ::=
    WITH <with list>

<with list> ::=
    <with list element> [ { <comma> <with list element> }... ]

<with list element> ::=
    <query name> [ <left paren> <with column list> <right paren> ] 
        AS <table subquery> [ <search or cycle clause> ]

<with column list> ::=
    <column name list>   
 
<search or cycle clause> ::=
    <search clause>
  | <cycle clause>
  | <search clause> <cycle clause>

<search clause> ::=
    SEARCH <recursive search order> SET <sequence column>

<recursive search order> ::=
    DEPTH FIRST BY <ordering column list>
  | BREADTH FIRST BY <ordering column list>

<ordering column list> ::= 
    <ordering column> [ { <comma> <ordering column> }... ]

<ordering column> ::= 
    <column name> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

<sequence column> ::=
    <column name>

<cycle clause> ::=
    CYCLE <cycle column list> SET <cycle mark column> TO <cycle mark value>
        DEFAULT <non-cycle mark value>

<cycle column list> ::=
    <cycle column> [ { <comma> <cycle column> }... ]

<cycle column> ::=
    <column name>
    
<cycle mark column> ::=
    <column name>
 
<cycle mark value> ::=
    <value expression>

<non-cycle mark value> ::=
    <value expression>

Invocation and Access Rules

It is supported in <query expression> statement, and the user should satisfy the access privilege of  <query expression> to perform it. 
For more information, refer to query expression.

Syntax Rules and Parameters

<with list>

It can define multiple <with list element>.

<with list element>

It defines the temporary result set of specified <query name>.
<with list element> is called as Common Table Expression (CTE).
CTE is divided into a recursive CTE and a non-recursive CTE.
For more information, refer to Description.
--# success : non recursive CTE
WITH CTE_1( c1 ) AS
    (
         SELECT i1
           FROM t1
    ),
    CTE_2( c2 ) AS
    (
         SELECT c1
           FROM CTE_1      1 Referring to the leading CTE
    )
SELECT c2 FROM CTE_2;

--# error : non recursive CTE
WITH CTE_1( c1 ) AS
    (  
         SELECT c2
           FROM CTE_2      2 Referring to the trailing CTE
    ),
    CTE_2( c2 ) AS
    (  
         SELECT i1
           FROM t1
    )
SELECT c1 FROM CTE_1;
--# success : recursive CTE
WITH CTE_1( c1 ) AS
     (  
          SELECT i1
            FROM t1
     ),
     CTE_2( c2 ) AS
     (  
          SELECT c1
            FROM CTE_1          1 Referring to the leading CTE
     ),
     CTE_3( c3 ) AS
     (  
          SELECT 1
            FROM CTE_1
          UNION ALL
          SELECT 1
           FROM CTE_2, CTE_3     2 Referring to the leading CTE or self CTE
     )
SELECT c3 FROM CTE_3;

--# error : recursive CTE
WITH CTE_RECURSIVE( c1 ) AS  
   (  
          SELECT i1
            FROM t1
          WHERE i1 IS NULL
          UNION ALL
          SELECT 1
            FROM CTE_RECURSIVE A, CTE_RECURSIVE B   3 Self-reference CTE is allowed only once
          WHERE 1 = 0
     )
SELECT c1 FROM CTE_RECURSIVE;

<query name>

<query name> should not be duplicated within WITH clause.

<with column list>

Recursive CTE can not omit <with column list>.

<search clause>

<cycle clause>

It stores <cycle mark value> or <non-cycle mark value> in <cycle mark column> according to whether cycle occurs.

Description

<with clause> defines a temporary result set, and it can refer to that result set.  
It is defined and referenced within SELECT statement, and it is a temporary result set with a given name.
It is called as Common Table Expression (CTE).
CTE is classified into recursive CTE and non-recursive CTE.
WITH RECURSIVE_CTE ( c1 ) AS
     (
          SELECT 1 
            FROM dual
          UNION ALL
          SELECT c1 + 1 
            FROM RECURSIVE_CTE
           WHERE c1 < 10
     )
SELECT c1 FROM RECURSIVE_CTE;
WITH NON_RECURSIVE_CTE ( c1 ) AS 
     (
          SELECT i1
            FROM t1
          UNION ALL
          SELECT i1
            FROM t2 
     )
SELECT c1 FROM NON_RECURSIVE_CTE;
<with clause> can be described in  SELECT, INSERT, UPDATE, DELETE, CREATE TABLE AS SELECT, CREATE VIEW statement.

<with list element>

It defines the temporary result set of the described <query name>.
<with list element> is called as Common Table Expression (CTE).
CTE is classified into recursive CTE and non-recursive CTE.
WITH CTE_RECURSIVE( c1, c2 ) AS
    (  
         SELECT i1, i2                          1 Anchor member query
           FROM t1
          WHERE i2 IS NULL
         UNION ALL
         SELECT i1, i2                          2 Recursive member query
           FROM CTE_RECURSIVE, t1      3 Self reference
          WHERE CTE_RECURSIVE.c1 = t1.i2
    )
SELECT c1, c2 FROM CTE_RECURSIVE;

<search clause>

It describes the sort order of CTE result records.
Sibling rows are sorted with <ordering column list>, it specifies the returning order of sibling rows and child rows for the sorted records.
The sequence of result records are stored in <sequence column>.
gSQL>
SELECT * FROM t1;

I1  I2 
--- ---
A   ---
AA  A  
AB  A  
AC  A  
AAX AA 
ABX AB 
ACX AC 

7 rows selected.

* SEARCH BREADTH FIRST BY

gSQL> 
WITH w1( w_i1, w_i2 ) AS
    ( 
         SELECT i1, i2
           FROM t1
          WHERE i1 = 'A'
         UNION ALL
         SELECT i1, i2
           FROM w1, t1
          WHERE w_i1 = i2
    ) SEARCH BREADTH FIRST BY w_i1, w_i2 SET w_seq
SELECT w_i1, w_i2, w_seq
 FROM w1;

W_I1 W_I2 W_SEQ
---- ---- -----
A    ---      1
AA   A        2
AB   A        3
AC   A        4
AAX  AA       5
ABX  AB       6
ACX  AC       7

7 rows selected.

* SEARCH DEPTH FIRST BY

gSQL> 
WITH w1( w_i1, w_i2 ) AS
    ( 
         SELECT i1, i2
           FROM t1
          WHERE i1 = 'A'
         UNION ALL
         SELECT i1, i2
           FROM w1, t1
          WHERE w_i1 = i2
    ) SEARCH DEPTH FIRST BY w_i1, w_i2 SET w_seq
SELECT w_i1, w_i2, w_seq
  FROM w1;

W_I1 W_I2 W_SEQ
---- ---- -----
A    ---      1
AA   A        2
AAX  AA       3
AB   A        4
ABX  AB       5
AC   A        6
ACX  AC       7

7 rows selected.

<cycle clause>

If <cycle clause> statement is not described, then an error occurs when cycle occurs.
<cycle column list> is used to check cycle. 
It stores <cycle mark value> or <non-cycle mark value> in <cycle mark column> according to whether cycle occurs. 
Only 1 byte character can be described in <cycle mark value> or <non-cycle mark value>. 
<cycle mark value> is stored in <cycle mark column> of the record where cycle occurred. In this case, it stops further recursion, and returns only until the record where cycle occurred
The recursion continuously proceeds for sibling rows where cycle does not occurred.
gSQL>
SELECT * FROM t1;

I1  I2 
--- ---
A   ---
AA  A  
AB  A  
AC  A  
AA  AA 
AAX AA 
ABX AB 
ACX AC 

8 rows selected.
gSQL> 
WITH w1( w_i1, w_i2 ) AS
     (     
          SELECT i1, i2
            FROM t1
           WHERE i1 = 'A'
          UNION ALL
          SELECT i1, i2
            FROM w1, t1
           WHERE w_i1 = i2
     )
SELECT w_i1, w_i2
  FROM w1;

ERR-42000(16511): cycle detected while executing recursive WITH query
gSQL> 
WITH w1( w_i1, w_i2 ) AS
     ( 
          SELECT i1, i2
            FROM t1
           WHERE i1 = 'A'
          UNION ALL
          SELECT i1, i2
            FROM w1, t1
           WHERE w_i1 = i2
     ) CYCLE w_i1, w_i2 SET c_cycle TO 'T' DEFAULT 'F'
SELECT w_i1, w_i2, c_cycle
  FROM w1;

W_I1 W_I2 C_CYCLE
---- ---- -------
A    ---  F      
AC   A    F      
AB   A    F      
AA   A    F      
ACX  AC   F      
ABX  AB   F      
AAX  AA   F      
AA   AA   F      
AAX  AA   F      
AA   AA   T      

10 rows selected.

Examples

The following is an example of SELECT statement which uses WITH clause.
gSQL>
WITH revenue ( supplier_no, total_revenue ) AS
      (
            SELECT
                   l_suppkey,
                   SUM(l_extendedprice * (1 - l_discount))
              FROM lineitem
             WHERE l_shipdate >= DATE '1996-01-01'
               AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
             GROUP BY
                   l_suppkey
      )
select
       s_suppkey,
       s_name,
       s_address,
       s_phone,
       ROUND( total_revenue, 2 ) as total_revenue
  from
       supplier,
       revenue
 where
       s_suppkey = supplier_no
   and total_revenue = (
                           select
                                  max(total_revenue)
                             from
                                  revenue
                       )
order by
      s_suppkey;

S_SUPPKEY S_NAME                    S_ADDRESS         S_PHONE         TOTAL_REVENUE
--------- ------------------------- ----------------- --------------- -------------
     8449 Supplier#000008449        Wp34zim9qYFbVctdW 20-469-856-8873    1772627.21

1 row selected.
gSQL> 
WITH GenerateRecord ( c1, c2 ) AS 
     (
          SELECT 1, 11
            FROM dual
          UNION ALL
          SELECT c1 + 1, c2 + 1
            FROM GenerateRecord
           WHERE c1 < 10
     )
SELECT c1, c2 FROM GenerateRecord;

C1 C2
-- --
 1 11
 2 12
 3 13
 4 14
 5 15
 6 16
 7 17
 8 18
 9 19
10 20

10 rows selected.
The following is the result of retrieving record in emp table which will be used in the example of WITH clause.
gSQL>
SELECT * FROM emp;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Jackson Kelly  
Joe     Kelly  
Scott   Bill   
Larry   Bill   
Paul    Jackson
Bill    Bill   

8 rows selected.
The following is an example of using SEARCH BREADTH FIRST BY.
gSQL> 
WITH w_emp( w_name, w_mgr ) AS
     (
         SELECT name, mgr
           FROM emp
          WHERE mgr IS NULL
         UNION ALL
         SELECT name, mgr
           FROM emp, w_emp
          WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
SELECT w_name, w_mgr, w_seq
  FROM w_emp;

ERR-42000(16511): cycle detected while executing recursive WITH query
gSQL> 
WITH w_emp( w_name, w_mgr ) AS
     (
         SELECT name, mgr
           FROM emp
          WHERE mgr IS NULL
         UNION ALL
         SELECT name, mgr
           FROM emp, w_emp
          WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

W_NAME  W_MGR   W_SEQ W_CYCLE
------- ------- ----- -------
Kelly   null        1 F      
Bill    Kelly       2 F      
Jackson Kelly       3 F      
Joe     Kelly       4 F      
Bill    Bill        5 T      
Larry   Bill        6 F      
Paul    Jackson     7 F      
Scott   Bill        8 F      

8 rows selected.
The following is an example of using SEARCH DEPTH FIRST BY.
gSQL>
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr IS NULL
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH DEPTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

W_NAME  W_MGR   W_SEQ W_CYCLE
------- ------- ----- -------
Kelly   null        1 F      
Bill    Kelly       2 F      
Bill    Bill        3 T      
Larry   Bill        4 F      
Scott   Bill        5 F      
Jackson Kelly       6 F      
Paul    Jackson     7 F      
Joe     Kelly       8 F      

8 rows selected.
The following is an example of using with clause in CREATE TABLE AS SELECT statement.
gSQL> 
CREATE TABLE new_emp AS
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr IS NULL
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

Table created.
The following is an example of using with clause in INSERT statement.
gSQL>
INSERT INTO new_emp
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr = 'Bill'
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

6 rows created.
The following is an example of using with clause in UPDATE statement.
gSQL>
UPDATE new_emp SET w_name = NULL
 WHERE ( w_name, w_mgr ) 
       IN ( WITH w_emp( w_name, w_mgr ) AS
                (
                    SELECT name, mgr
                      FROM emp
                     WHERE mgr = 'Bill'
                    UNION ALL
                    SELECT name, mgr
                      FROM emp, w_emp
                     WHERE mgr = w_emp.w_name
                ) SEARCH BREADTH FIRST BY w_name SET w_seq
                  CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
            SELECT w_name, w_mgr
              FROM w_emp );

9 rows updated.
The following is an example of using with clause in DELETE statement.
gSQL>
DELETE FROM new_emp
WHERE ( w_mgr ) 
      IN ( WITH w_emp( w_name, w_mgr ) AS
               (
                   SELECT name, mgr
                     FROM emp
                    WHERE mgr = 'Bill'
                   UNION ALL
                   SELECT name, mgr
                     FROM emp, w_emp
                    WHERE mgr = w_emp.w_name
               ) SEARCH BREADTH FIRST BY w_name SET w_seq
                 CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
           SELECT w_mgr
             FROM w_emp );

9 rows deleted.
The following is an example of using with clause in CREATE VIEW statement.
gSQL>
CREATE VIEW v_emp AS
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr IS NULL
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

View created.

query specification

Function

It specifies the table which is derived from the result of <table expression>.

Syntax

<query specification> ::=
    SELECT [ <hint clause> ] [ <set quantifier> ] <select list> <table expression>

<set quantifier> ::=
      ALL
    | DISTINCT

<table expression> ::=
      <from clause> [ <where clause> ] [ <hierarchical query clause> ] [ <group by clause> ] [ <having clause> ]

Invocation and Access Rules

The user should satisfy one of the following conditions to perform <query specification>.

Syntax Rules and Parameters

<hint clause>

It specifies the hint for query execution. 
For more information, refer to SQL Hint.

<set quantifier>

It specifies whether to remove a duplicate of the query result. 
If it is omitted, it operates in the same way as ALL.

<select list>

It specifies the column to be retrieved among query results. 
For more information, refer to select list.

<from clause>

It specifies the tables to be retrieved.
For more information, refer to from clause.

<where clause>

It specifies conditions for retrieving.
For more information, refer to where clause.

<hierarchical query clause>

It specifies to retrieve the hierarchical model data in a hierarchy.
For more information, refer to hierarchical query clause.

<group by clause>

It specifies grouping of the query result. 
For more information, refer to group by clause.

<having clause>

It specifies conditions for the grouping result.
For more information, refer to having clause.

Description

<hint clause>

<hint clause> is a comment which the user uses to directly command an optimizer how to execute SQL statement.

The optimizer of GOLDILOCKS preferentially applies <hint clause> specified by a user. 
If it is not applicable, the optimizer selects the best execution plan through the cost calculation.

Even when a syntactic error occurs in <hint clause>, GOLDILOCKS is set to ignore and perform it by default. Set HINT_ERROR property to on, then execute the query to check if a syntactic error exist in <hint clause>.

<set quantifier>

<set quantifier> sets whether to remove duplicates from the result set  consisting of the <select list> expressions.

<select list>

It specifies columns to be retrieved from the query result. 
They are listed by separating by a comma (,). 
An asterisk (*) is used to specify all columns in <from clause>.

<from clause>

<from clause> specifies the tables or views to be retrieved.

<where clause>

<where clause> specifies the conditions to get only the desired results from the result obtained from <from clause>.

<hierarchical query clause>

It specifies to retrieve the hierarchical model data in a hierarchy.
It returns table records in a hierarchy of depth-first sequence by using the launch condition and sub-connectivity condition.

<group by clause>

<group by clause> specifies the method of grouping the result set to which <where clause> was applied.
When <group by clause> is specified, the following expressions can be used in <select list>.

<having clause>

<having clause> specifies the retrieving condition for the grouped result set.
It is generally used together with <group by clause>.

Examples

The following is an example of SELECT statement which uses <hint clause>.

gSQL> SELECT /*+ INDEX_DESC(supplier, supplier_pk_index) */ s_name, s_nation FROM supplier;

S_NAME                    S_NATION
------------------------- -------------
Supplier#5                CANADA
Supplier#4                UNITED STATES
Supplier#3                GERMANY
Supplier#2                KOREA
Supplier#1                FRANCE

5 rows selected.

The following is an example of SELECT statement which uses <set quantifier>.

gSQL> SELECT ALL p_type FROM part;

P_TYPE
------
COPPER
NICKEL
STEEL
NICKEL
STEEL

5 rows selected.

gSQL> SELECT DISTINCT p_type FROM part;

P_TYPE
------
COPPER
STEEL
NICKEL

3 rows selected.

The following is an example of SELECT statement which uses <where clause>.

gSQL> SELECT p_name, p_brand, p_type, p_size FROM part where p_size < 10;

P_NAME P_BRAND    P_TYPE P_SIZE
------ ---------- ------ ------
Part#1 Brand#1    COPPER      7
Part#2 Brand#1    NICKEL      1

2 rows selected.

The following is an example of retrieving the hierarchy data of SELECT statement by using <hierarchical query clause>.

gSQL> SELECT *
        FROM emp
      START WITH mgr IS NULL
      CONNECT BY NOCYCLE mgr = PRIOR name
      ORDER SIBLINGS BY name;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Larry   Bill   
Scott   Bill   
Jackson Kelly  
Paul    Jackson
Joe     Kelly  

7 rows selected.

The following is an example of SELECT statement which uses <group by clause>.

gSQL> SELECT ps_partkey, SUM(ps_availqty) FROM partsupp GROUP BY ps_partkey;

PS_PARTKEY SUM(PS_AVAILQTY)
---------- ----------------
         1            11401
         2             8025
         3            13864
         4            11564
         5             8744

5 rows selected.

The following is an example of SELECT statement which uses <having clause>.

gSQL> SELECT ps_partkey, SUM(ps_availqty) FROM partsupp GROUP BY ps_partkey having SUM(ps_availqty) > 10000;

PS_PARTKEY SUM(PS_AVAILQTY)
---------- ----------------
         1            11401
         3            13864
         4            11564

3 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F801

Full set function

X

T051

Row types

X

T301

Functional dependencies

X

T325

Qualified SQL parameter references

X

T053

Explicit aliases for all-fields reference

O

T285

Enhanced derived column names

O

For More Information

Refer to query expression.

select list

Function

It specifies the columns to be retrieved from the query result.

Syntax

<select list> ::=
      <asterisk>
    | <select sublist> [ { <comma> <select sublist> } ... ]

<select sublist> ::=
      <derived column>
    | <qualified asterisk>

<qualified asterisk> ::=
      <asterisked identifier chain> <period> <asterisk>

<asterisked identifier chain> ::=
    <asterisked identifier> [ { <period> <asterisked identifier> } ... ]

<derived column> ::=
    <value expression> [ <as clause> ]

<as clause> ::=
    [ AS ] <column name>

Invocation and Access Rules

If columns or subqueries exist in <select list> statement, the user should satisfy the followings.

Syntax Rules and Parameters

<select list>

It has <asterisk> or <select sublist>.

<asterisk>

<select sublist>

Description

<select list>

<select list> specifies the columns to be included in the result set.

<asterisk>

<asterisk> sets all columns in <from clause> as a select list.

<select sublist>

<select sublist> has <derived column> or <qualified asterisk>.

If two or more <select sublist> are specified, each <select sublist> should be separated by a comma (',').

Names to Be Set in select list

Examples

The following is an example of SELECT statement which uses <asterisk>.

gSQL> SELECT * FROM supplier;

S_SUPPKEY S_NAME                    S_NATION      S_PHONE
--------- ------------------------- ------------- ---------------
        1 Supplier#1                FRANCE        27-918-335-1736
        2 Supplier#2                KOREA         15-679-861-2259
        3 Supplier#3                GERMANY       11-383-516-1199
        4 Supplier#4                UNITED STATES 25-843-787-7479
        5 Supplier#5                CANADA        21-151-690-3663

5 rows selected.

The following is an example of SELECT statement which uses <select sublist>.

gSQL> SELECT revenue.* FROM revenue;

SUPPLIER_NO TOTAL_REVENUE
----------- -------------
          1      11978.64
          2       20321.5
          3      41844.68

3 rows selected.

gSQL> SELECT supplier_no suppno, total_revenue AS TOTAL FROM revenue;

SUPPNO    TOTAL
------ --------
     1 11978.64
     2  20321.5
     3 41844.68

3 rows selected.

gSQL> SELECT 1, revenue.*, CAST( total_revenue AS NATIVE_INTEGER ) TOTAL FROM revenue;

1 SUPPLIER_NO TOTAL_REVENUE TOTAL
- ----------- ------------- -----
1           1      11978.64 11979
1           2       20321.5 20322
1           3      41844.68 41845

3 rows selected.

For More Information

Refer to query specification.

from clause

Function

It specifies the table which is derived from one or more tables.

Syntax

<from clause> ::=
    FROM <table reference list>

<table reference list> ::=
    <table reference> [ { , <table reference> } ... ]

<table reference> ::=
      <table factor>
    | <joined table>

<table factor> ::=
    <table primary>

<table primary> ::=
      <table name> [ <cluster domain> ] [ [ AS ] <correlation name> ]
    | <derived table> [ <cluster domain> ] [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]
    | <parenthesized joined table>

<derived table> ::=
    <table subquery>

<parenthesized joined table> ::=
      <left paren> <parenthesized joined table> <right paren>
    | <left paren> <joined table> <right paren>

<derived column list> ::=
    <column name list>

<cluster domain> ::=
    @ <cluster domain name>

<cluster domain name> ::=
      GLOBAL
    | LOCAL
    | LOCAL_OFFLINE
    | <identifier>

Invocation and Access Rules

The access privilege for the table or view specified in <table reference list> is required.

Syntax Rules and Parameters

<table reference list>

<table primary>

SELECT col1, col2 
FROM ( SELECT i1, i2 FROM t1 ) AS a( col1, col2 ) 
WHERE col1 = 1 AND col2 = 1;

<correlation name>

<derived column list>

The same <column name> should not exist two or more in <derived column list>.

<cluster domain>

<cluster domain name>

Only a cluster group name or a cluster member name can be <identifier> of <cluster domain name>.

Description

<table reference list>

Two or more tables can be specified in <table reference list> by using a comma (,).

<table reference>

A single table, or view, table subquery, joined table can be <table reference>. Others except for the joined table can have a correlation name.
For more information about joined table, refer to joined table.

<table primary>

The tables, views, table subqueries and <parenthesized joined table> can be <table primary>.

The table, view, table subquery can have a correlation name, and AS can be omitted. If the correlation name is specified, it should be used in everywhere referring to the table, view, table subquery such as <select list>, <where clause>.

The table subquery can specify <derived column list>. The name specified in <derived column list> should be used in everywhere referring to table subquery column as like correlation name. To use <derived column list> in the table subquery, the correlation name should be specified.

<parenthesized joined table> specifies the logical join order for the table participating in the join operation. At this time, if all join operations for the tables enclosed with parentheses are cross join, inner join, the join order can be changed by an optimizer.

<cluster domain>

When <cluster domain> is omitted, it means the same as using GLOBAL as <cluster domain name>.
For more information, refer to Cluster Domain.

<cluster domain name>

The reserved words defined in <cluster domain name> mean as follows.

If <identifier> is specified in <cluster domain name>, a cluster group or a cluster member with the corresponding name is selected as Cluster Domain.

Examples

The following is an example of SELECT statement to query a single table by using <table name>.

gSQL> SELECT c_name, c_nation FROM customer;

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.

The following is an example of SELECT statement which uses <derived table>.

gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer);

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.


gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer) AS CUST ("CUSTOMER_NAME", "CUSTOMER_NATION");

CUSTOMER_NAME CUSTOMER_NATION
------------- ---------------
Customer#1    KOREA          
Customer#2    CANADA         
Customer#3    KOREA          
Customer#4    GERMANY        
Customer#5    UNITED STATES  

5 rows selected.

The following is an example of SELECT statement for a joined table which uses parentheses.

gSQL> SELECT customer.c_name, o_totalprice FROM (customer INNER JOIN orders ON customer.c_custkey = orders.o_custkey);

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#2     46929.18
Customer#4    193846.25
Customer#3     32151.78
Customer#5     144659.2

5 rows selected.

The following is an example of SELECT statement which uses two table separated by a comma (,).

gSQL> SELECT c_name, o_totalprice FROM customer, orders;

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#1     46929.18
Customer#1    193846.25
Customer#1     32151.78
Customer#1     144659.2
Customer#2    173665.47
Customer#2     46929.18
Customer#2    193846.25
Customer#2     32151.78
Customer#2     144659.2
Customer#3    173665.47
Customer#3     46929.18
Customer#3    193846.25
Customer#3     32151.78
Customer#3     144659.2
Customer#4    173665.47
Customer#4     46929.18
Customer#4    193846.25
Customer#4     32151.78
Customer#4     144659.2

C_NAME     O_TOTALPRICE
---------- ------------
Customer#5    173665.47
Customer#5     46929.18
Customer#5    193846.25
Customer#5     32151.78
Customer#5     144659.2

25 rows selected.

The following is an example of SELECT statement which uses <cluster domain>.

gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer@GLOBAL);

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.
gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer)@LOCAL;

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA

2 rows selected.
gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer@G1);

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA

2 rows selected.
gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer@G2N1);

C_NAME     C_NATION
---------- -------------
Customer#3 KOREA
Customer#4 GERMANY

2 rows selected.

For More Information

Refer to subquery.

joined table

Function

It specifies the table derived from a cartesian product, inner join, outer join.

Syntax

<joined table> ::=
      <cross join>
    | <qualified join>
    | <natural join>

<cross join> ::=
    <table reference> CROSS JOIN <table factor>

<qualified join> ::=
    <table reference> [ <join type> ] JOIN <table reference> <join specification>

<natural join> ::=
    <table reference> NATURAL [ <join type> ] JOIN <table factor>

<join specification> ::=
      <join condition>
    | <named columns join>

<join condition> ::=
    ON <search condition>

<named columns join> ::=
    USING ( <join column list> )

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

<join column list> ::=
    <column name list>

Invocation and Access Rules

The access privilege for all tables and views specified in a joined table is required.

Syntax Rules and Parameters

<cross join>

<join specification> specifying the join condition does not appear at the location of <cross join>.
A single table, <table subquery> or <parenthesized joined table> can appear on the right of <cross join>.

<qualified join>

<natural join>

<join specification>

Description

<cross join>

<cross join> returns a result which combines each left row with all right rows.
T1 ( 1, 1 ), ( 2, 2 )
T2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 CROSS JOIN t2;
C1 C2 C1 C2
-- -- -- --
 1  1  2  2
 1  1  3  3
 2  2  2  2
 2  2  3  3
4 rows selected.
The explicit join condition can not be specified in <cross join>, but the join condition for the two tables can be specified in <where clause>. In this case, it performs inner join.
• SELECT * FROM t1 CROSS JOIN t2 WHERE t1.c1 = t2.c1;
• <=> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1;
T1 ( 1, 1 ), ( 2, 2 )
T2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 CROSS JOIN t2 WHERE t1.c1 = t2.c1;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
1 row selected.

<qualified join>

<qualified join> combines each left row with all right rows, then returns only the rows satisfying the join condition as a result.

If <where clause> exists in <table expression>, then conditions in <where clause> are applied to the result set of <qualified join>.

The result is same, even when inner join processes the conditions in <where clause> as join conditions. But result differs when outer join processes the conditions in <where clause> as join conditions.

INNER JOIN
t1 ( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ), ( 5, 5 )
t2 ( 2, 2 ), ( 3, 3 )
gSQL> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
 3  3  3  3
2 rows selected.
gSQL> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 = t2.c2;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
 3  3  3  3
2 rows selected.
( 2,  2,    2,    2 )                        ( 2,  2,    2,    2 )
  ( 3,  3,    3,    3 )                   →   ( 3,  3,    3,    3 )
OUTER JOIN
t1 ( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ), ( 5, 5 )
t2 ( 2, 2 ), ( 3, 3 )
gSQL> SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
C1 C2   C1   C2
-- -- ---- ----
 1  1 null null
 2  2    2    2
 3  3    3    3
 4  4 null null
 5  5 null null
5 rows selected.
gSQL> SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 = t2.c2;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
 3  3  3  3
2 rows selected.
( 1,  1, null, null )
  ( 2,  2,    2,    2 )                        ( 2,  2,    2,    2 )
  ( 3,  3,    3,    3 )                   →   ( 3,  3,    3,    3 ) 
  ( 4,  4, null, null )
  ( 5,  5, null, null )

Left outer join combines right rows satisfying the join condition for the left rows, then returns the combined rows as a result. If right rows satisfying the join condition does not exist, then it returns the result whose left row values are as they are and whose right row values are filled with NULL.

LEFT OUTER JOIN
t1 ( 1, 1 ), ( 2, 2 )
t2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c1 = t2.c1;
C1 C2   C1   C2
-- -- ---- ----
 1  1 null null
 2  2    2    2
2 rows selected.

Right outer join is operated in an opposite way of left outer join.

RIGHT OUTER JOIN

t1 ( 1, 1 ), ( 2, 2 )
t2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1;
  C1   C2 C1 C2
---- ---- -- --
   2    2  2  2
null null  3  3
2 rows selected.

Full outer join returns the left rows filled with NULL for all right rows which do not satisfy the join condition together with left outer join results.

FULL OUTER JOIN

t1 ( 1, 1 ), ( 2, 2 )
t2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.c1 = t2.c1;
  C1   C2   C1   C2
---- ---- ---- ----
   1    1 null null
   2    2    2    2
null null    3    3
3 rows selected.

<natural join>

<natural join> joins all columns with same names in two tables participating in join as equal. In other words, it is as same as specifying all columns with same names of two tables participating in join in USING clause of inner join.

t1 ( C1 INTEGER, C2 INTEGER )
t2 ( C1 INTEGER, C3 INTEGER )

t1 ( 1, 10 ), ( 2, 20 ), ( 3, 30 )
t2 ( 1, 100 ), ( 2, 200 ), ( 3, 300 )

gSQL> SELECT * FROM t1 NATURAL JOIN t2; 
C1 C2  C3
-- -- ---
 1 10 100
 2 20 200
 3 30 300
3 rows selected.

gSQL> SELECT * FROM t1 INNER JOIN t2 USING ( c1 );
C1 C2  C3
-- -- ---
 1 10 100
 2 20 200
 3 30 300
3 rows selected.

<join specification>

It specifies the join condition.
<join condition> specifies the condition for joining left rows and right rows of a join statement.
<named columns join> specifies the join condition by listing that <column name>, if the same <column name> exist in left rows and right rows.
t1 ( C1 INTEGER, C2 INTEGER )
t2 ( C1 INTEGER, C3 INTEGER )

t1 ( 1, 10 ), ( 2, 20 ), ( 3, 30 )
t2 ( 1, 100 ), ( 2, 200 ), ( 3, 300 )

• <join condition>
gSQL> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1;
C1 C2 C1  C3
-- -- -- ---
 1 10  1 100
 2 20  2 200
 3 30  3 300
3 rows selected.

• <named columns join>
gSQL> SELECT * FROM t1 INNER JOIN t2 USING ( c1 );
C1 C2  C3
-- -- ---
 1 10 100
 2 20 200
 3 30 300
3 rows selected.

Examples

The following is an example of SELECT statement which uses <cross join>.

gSQL> SELECT c_name, o_totalprice FROM customer CROSS JOIN orders;

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#1     46929.18
Customer#1    193846.25
Customer#1     32151.78
Customer#1     144659.2
Customer#2    173665.47
Customer#2     46929.18
Customer#2    193846.25
Customer#2     32151.78
Customer#2     144659.2
Customer#3    173665.47
Customer#3     46929.18
Customer#3    193846.25
Customer#3     32151.78
Customer#3     144659.2
Customer#4    173665.47
Customer#4     46929.18
Customer#4    193846.25
Customer#4     32151.78
Customer#4     144659.2

C_NAME     O_TOTALPRICE
---------- ------------
Customer#5    173665.47
Customer#5     46929.18
Customer#5    193846.25
Customer#5     32151.78
Customer#5     144659.2

25 rows selected.

The following is an example of SELECT statement which uses inner join.

gSQL> SELECT c_name, o_totalprice FROM customer INNER JOIN orders ON c_custkey = o_custkey;

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#2     46929.18
Customer#4    193846.25
Customer#3     32151.78
Customer#5     144659.2

5 rows selected.

The following is an example of SELECT statement which uses outer join.

gSQL> SELECT c_name, o_totalprice FROM customer LEFT OUTER JOIN orders ON c_custkey = o_custkey AND o_orderdate < '1996-01-01';

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1         null
Customer#2         null
Customer#3     32151.78
Customer#4    193846.25
Customer#5     144659.2

5 rows selected.

gSQL> SELECT c_name, o_totalprice FROM customer RIGHT OUTER JOIN orders ON c_custkey = o_custkey AND c_nation = 'KOREA';

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
null           46929.18
null          193846.25
Customer#3     32151.78
null           144659.2

5 rows selected.

gSQL> SELECT c_name, o_totalprice FROM customer FULL OUTER JOIN orders ON c_custkey = o_custkey AND c_nation = 'KOREA' AND o_orderdate < '1996-01-01';

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1         null
Customer#2         null
Customer#3     32151.78
Customer#4         null
Customer#5         null
null          173665.47
null           46929.18
null          193846.25
null           144659.2

9 rows selected.

The following is an example of SELECT statement which uses natural join.

gSQL> SELECT c_name, o_totalprice FROM (SELECT c_custkey custkey, c_name FROM customer) NATURAL JOIN (SELECT o_custkey custkey, o_totalprice FROM orders);

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#2     46929.18
Customer#4    193846.25
Customer#3     32151.78
Customer#5     144659.2

5 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F401

Extended joined table

O

F402

Named column joins for LOBs, arrays, and multisets

X

F403

Partitioned join tables

X

For More Information

Refer to from clause.

where clause

Function

It applies <search condition> to the result of <from clause>.

Syntax

<where clause> ::=
    WHERE <search condition>

Syntax Rules and Parameters

<where clause>

<search condition> which returns a boolean type is required after WHERE keyword.

Description

For more information about <where clause>, refer to Conditions.

Example

The following is an example of SELECT statement which uses <where clause>.

gSQL> SELECT s_name, s_nation FROM supplier WHERE s_nation = 'KOREA';

S_NAME                    S_NATION
------------------------- --------
Supplier#2                KOREA

1 row selected.

gSQL> SELECT s_name, ps_availqty, ps_supplycost FROM supplier, partsupp WHERE s_nation = 'KOREA' AND s_suppkey = ps_suppkey;

S_NAME                    PS_AVAILQTY PS_SUPPLYCOST
------------------------- ----------- -------------
Supplier#2                       8076        993.49
Supplier#2                       4069        357.84

2 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F441

Extended set function support

O

For More Information

Refer to query specification.

hierarchical query clause

Function

It specifies to retrieve the hierarchical model data in a hierarchy. 
It returns table records in a hierarchy of depth-first sequence by using the launch condition and sub-connectivity condition.

Syntax

<hierarchical query clause> ::= 
    <start with connect by clause> [ <order siblings by clause> ]

<start with connect by clause> ::= 
    <start with clause> <connect by clause>
    | <connect by clause> <start with clause>
    | <connect by clause>

<start with clause> ::=
    START WITH <start_with_condition>

<connect by clause> ::=
    CONNECT BY [NOCYCLE] <connect_by_condition>

<order siblings by clause> ::=
    ORDER SIBLINGS BY <ordering element> [ { <comma> <ordering element> }... ]

<ordering element> ::=
    <value expression> [ASC | DESC] [NULLS FIRST | NULLS LAST]

<hierarchy expression> ::=
    LEVEL
    | CONNECT_BY_ISCYCLE
    | CONNECT_BY_ISLEAF
    | PRIOR <value expression>
    | CONNECT_BY_ROOT <value expression>
    | SYS_CONNECT_BY_PATH <left paren> <value expression> <comma> <character string literal> <right paren>

Invocation and Access Rules

It is supported in <query specification> statement, and the user should satisfy the access privilege of <query specification> to perform it.
For more information, refer to query specification.

Syntax Rules and Parameters

<hierarchical query clause>

<connect by clause> should be described.
<start with clause> or <order siblings by clause> is described when it is required.

<start with clause>

It specifies the condition of root record in data hierarchy.
When it is not specified, then all records of from clause become targets of root records.
It can be described only once within SELECT statement.

<connect by clause>

It describes the relation between the parent record and the child record.
It expresses the relation between the parent record and the child record by using PRIOR operator which representing the column value of the parent record.
If the condition of connecting the parent record and the child record is not specified by using PRIOR operator, then an infinite loop may occur.
It can be described only once within SELECT statement.

<order siblings by clause>

It specifies the order of fetching sibling records of the same parent records.

<hierarchy expression>

Result type of <hierarchy expression>

Expression

Result DataType

LEVEL

NATIVE_BIGINT

CONNECT_BY_ISCYCLE

NATIVE_BIGINT

CONNECT_BY_ISLEAF

NATIVE_BIGINT

PRIOR expr

expr의 DataType

CONNECT_BY_ROOT expr

expr의 DataType

SYS_CONNECT_BY_PATH( expr, literal )

VARCHAR(4000 characters)

<hierarchy expression> can be described in the following statements.

Expression\clause

FROM

START WITH

CONNECT BY

ORDER SIBLINGS BY

WHERE/

GROUP BY/

HAVING

ORDER BY/

SELECT TARGET

LEVEL

X

O

O

X

O

O

CONNECT_BY_ISCYCLE

X

X

X

X

O

O

CONNECT_BY_ISLEAF

X

X

X

X

O

O

PRIOR

X

X

O

X

O

O

CONNECT_BY_ROOT

X

X

X

X

O

O

SYS_CONNECT_BY_PATH

X

X

X

X

O

O

Whether <hierarchy expression> can be used as an argument of <hierarchy expression> is described in the following table.

Expression\Argument(expr)

LEVEL

CONNECT_BY_ISCYCLE

CONNECT_BY_ISLEAF

PRIOR

CONNECT_BY_ROOT

SYS_CONNECT_BY_PARTH

PRIOR expr

X

X

X

X

X

X

CONNECT_BY_ROOT expr

X

X

X

X

X

X

SYS_CONNECT_BY_PARTH(expr,literal)

O

O

O

O

O

O

Description

<hierarchical query clause> retrieves the hierarchical model data in a hierarchy. 
It returns table records in a hierarchy of depth-first sequence by using the launch condition and sub-connectivity condition.
When <hierarchical query clause> is described in SELECT, then it is processed in the following order.

  1. ON condition in FROM clause

  2. START WITH

  3. CONNECT BY

  4. WHERE

SELECT *
 FROM r_region
WHERE r_population > 10000000             3 Condition in WHERE clause
START WITH r_name = 'EARTH'               1 START WITH
CONNECT BY r_domain = PRIOR r_name        2 CONNECT BY
SELECT *
  FROM r_region INNER JOIN s_region 
       ON r_id = s_id                      1 Join condition in ON clause
 WHERE r_population > 10000000             4 Condition in WHERE clause
START WITH r_name = 'EARTH'                2 START WITH
CONNECT BY r_domain = PRIOR r_name         3 CONNECT BY
SELECT *
  FROM r_region, s_region
 WHERE r_population > 10000000             3 Condition in WHERE clause
   AND r_id = s_id                         3 Join condition in WHERE clause
START WITH r_name = 'EARTH'                1 START WITH
CONNECT BY r_domain = PRIOR r_name         2 CONNECT BY
SELECT *
  FROM r_region INNER JOIN s_region
       ON r_name = s_name                   1 Join condition in ON clause
 WHERE r_population > 10000000              4 Condition in WHERE clause
   AND r_id = s_id                          4 Join condition in WHERE clause
START WITH r_name = 'EARTH'                 2 START WITH
CONNECT BY r_domain = PRIOR r_name          3 CONNECT BY

<order siblings by clause>

It specifies the order of fetching sibling records of the same parent records within <hierarchical query clause>.
<order siblings by clause> is a statement distinct from <order by clause>.
gSQL> 
SELECT * FROM t1;

I1  I2
--- ----
A   null
AA  A   
AB  A   
fAA AA  
eAA AA  
bAA AA  
dAB AB  
cAB AB  
aAB AB  

9 rows selected.
gSQL> 
SELECT LEVEL, i1, i2
  FROM t1
START WITH i1 = 'A' 
CONNECT BY i2 = PRIOR i1
ORDER SIBLINGS BY i1;

LEVEL I1  I2
----- --- ----
    1 A   null
    2 AA  A   
    3 bAA AA  
    3 eAA AA  
    3 fAA AA  
    2 AB  A   
    3 aAB AB  
    3 cAB AB  
    3 dAB AB  

9 rows selected.
gSQL> 
SELECT LEVEL, i1, i2
  FROM t1
START WITH i1 = 'A'
CONNECT BY i2 = PRIOR i1
ORDER SIBLINGS BY i1
ORDER BY LEVEL;

LEVEL I1  I2  
----- --- ----
    1 A   null
    2 AA  A   
    2 AB  A   
    3 bAA AA  
    3 eAA AA  
    3 fAA AA  
    3 aAB AB  
    3 cAB AB  
    3 dAB AB  

9 rows selected.

<hierarchy expression>

The followings are features of the hierarchy expression.

gSQL>
SELECT i1, i2
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = PRIOR i1;

I1    I2  
----- ----
X     null
XA    X   
XXA   XA  
XXXA  XXA 
XXXXA XXXA

5 rows selected.
gSQL> 
SELECT LEVEL, i1, i2
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1;

LEVEL I1    I2  
----- ----- ----
   1 X     null
   2 XA    X   
   3 XXA   XA  
   4 XXXA  XXA 
   5 XXXXA XXXA

5 rows selected.
gSQL> 
SELECT * FROM t1;

I1 I2  
-- ----
A  null
AA A   
AB A   
AC A   
AA AA  
AB AA  

6 rows selected.

gSQL> 
SELECT i1, i2, CONNECT_BY_ISCYCLE 
  FROM t1
START WITH i1 = 'A'
CONNECT BY NOCYCLE i2 = prior i1;

I1 I2   CONNECT_BY_ISCYCLE
-- ---- ------------------
A  null                  0
AA A                     1
AB AA                    0
AB A                     0
AC A                     0

5 rows selected.
gSQL> 
SELECT i1, i2, CONNECT_BY_ISLEAF
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1; 

I1    I2   CONNECT_BY_ISLEAF
----- ---- -----------------
X     null                 0
XA    X                    0
XXA   XA                   0
XXXA  XXA                  0
XXXXA XXXA                 1

5 rows selected.
gSQL> 
SELECT i1, i2, CONNECT_BY_ROOT i1
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1;

I1    I2   CONNECT_BY_ROOT I1
----- ---- ------------------
X     null X                 
XA    X    X                 
XXA   XA   X                 
XXXA  XXA  X                 
XXXXA XXXA X                 

5 rows selected.
gSQL> 
SELECT i1, i2, SYS_CONNECT_BY_PATH( i1, '/' )
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1;

I1    I2   SYS_CONNECT_BY_PATH( I1, '/' )
----- ---- ------------------------------
X     null /X                            
XA    X    /X/XA                         
XXA   XA   /X/XA/XXA                     
XXXA  XXA  /X/XA/XXA/XXXA                
XXXXA XXXA /X/XA/XXA/XXXA/XXXXA          

5 rows selected.

Examples

The following is the result of retrieving record in emp table which will be used in the example of hierarchical query clause.

gSQL> 
SELECT * FROM emp;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Jackson Kelly  
Joe     Kelly  
Scott   Bill   
Larry   Bill   
Paul    Jackson
Bill    Bill   

8 rows selected.

The following is an example of when cycle occurs.

gSQL> 
SELECT *
  FROM emp
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR name
ORDER SIBLINGS BY name;

ERR-42000(16511): cycle detected while executing recursive WITH query

The following is an example of executing the query by using CONNECT BY NOCYCLE statement.

gSQL> 
SELECT *
  FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE mgr = PRIOR name
ORDER SIBLINGS BY name;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Larry   Bill   
Scott   Bill   
Jackson Kelly  
Paul    Jackson
Joe     Kelly  

7 rows selected.

The following is an example of retrieving the information about the hierarchical data by using the hierarchy expression.

gSQL> 
SELECT name, 
       mgr, 
       PRIOR name AS prior_mgr,
       LEVEL,
       CONNECT_BY_ISCYCLE AS iscycle,
       CONNECT_BY_ISLEAF AS isleaf,
       CONNECT_BY_ROOT mgr AS root_mgr,
       SYS_CONNECT_BY_PATH( mgr, '/' ) AS path
  FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE mgr = PRIOR name
ORDER SIBLINGS BY name;

NAME    MGR     PRIOR_MGR LEVEL ISCYCLE ISLEAF ROOT_MGR PATH           
------- ------- --------- ----- ------- ------ -------- ---------------
Kelly   null    null          1       0      0 null     /              
Bill    Kelly   Kelly         2       1      0 null     //Kelly        
Larry   Bill    Bill          3       0      1 null     //Kelly/Bill   
Scott   Bill    Bill          3       0      1 null     //Kelly/Bill   
Jackson Kelly   Kelly         2       0      0 null     //Kelly        
Paul    Jackson Jackson       3       0      1 null     //Kelly/Jackson
Joe     Kelly   Kelly         2       0      1 null     //Kelly        

7 rows selected.

group by clause

Function

It specifies the grouped table of which <group by clause> was applied to the result processed by the previous statements.

Syntax

<group by clause> ::=
    GROUP BY <grouping element list>

<grouping element list> ::=
    <grouping element> [ { , <grouping element> } ... ]

<grouping element> ::=
      <ordinary grouping set>
    | <empty grouping set>

<ordinary grouping set> ::=
      <grouping column reference>

<grouping column reference> ::=
    <column reference>
    | <value_expression>

<empty grouping set> ::=
    <left paren> <right paren>

Invocation and Access Rules

Any separate access privilege is not required for a user to perform <group by clause>.

Syntax Rules and Parameters

<ordinary grouping set>

It consists of one or more <grouping column reference>.
It does not support LONG type (LONG VARCHAR, LONG VARBINARY).

• SELECT c1, sum(c2) FROM t1 GROUP BY c1;
• SELECT sum(c1) FROM t1 GROUP BY NULL;

<empty grouping set>

It can be specified by using only parentheses.

• SELECT sum(c1) FROM t1 GROUP BY ();

Description

<grouping element list>

It groups <grouping element list> specified in <group by clause> into a GROUPING SET. If all values of <grouping element> in GROUPING SET are matched, it is processed as the same group.

<grouping column reference>

<column reference> or <value expression> can appear in <grouping column reference>.

<empty grouping set>

All records in <empty grouping set> are configured into a single group.
• SELECT sum(c1), sum(c2) FROM t1 GROUP BY ();

Example

The following is an example of SELECT statement which uses GROUP BY clause.

gSQL> SELECT c_nation, COUNT(c_name) FROM customer GROUP BY c_nation;

C_NATION      COUNT(C_NAME)
------------- -------------
UNITED STATES             1
CANADA                    1
KOREA                     2
GERMANY                   1

4 rows selected.

gSQL> SELECT COUNT(c_name) FROM customer GROUP BY NULL;

COUNT(C_NAME)
-------------
            5

1 row selected.

gSQL> SELECT COUNT(c_name) FROM customer GROUP BY ();

COUNT(C_NAME)
-------------
            5

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T431

Extended grouping capabilities

X

T432

Nested and concatenated GROUPING SETS

X

T434

GROUP BY DISTINCT

X

For More Information

Refer to the followings.

having clause

Function

It specifies grouped tables having removed groups which do not satisfy <search condition>.

Syntax

<having clause> ::=
    HAVING <search condition>

Invocation and Access Rules

Any separate access privilege is not required for a user to perform <having clause>.

Syntax Rules and Parameters

<having clause>

Description

<having clause>

<having clause> specifies search conditions for the grouped data.

Generally, it is used together with <group by clause>. When <having clause> is used without <group by clause>, it is considered as if <empty grouping set> exists.

<grouping column reference> specified in <group by clause> can be specified in <having clause>. 
The columns which are not specified in <group by clause> can be specified by using aggregate functions.

Example

The following is an example of SELECT statement which uses <having clause>.

gSQL> SELECT c_nation, COUNT(c_name) FROM customer GROUP BY c_nation HAVING COUNT(c_name) > 1;

C_NATION COUNT(C_NAME)
-------- -------------
KOREA                2

1 row selected.

gSQL> SELECT COUNT(c_name) FROM customer HAVING COUNT(c_name) > 1;

COUNT(C_NAME)
-------------
            5

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T301

Functional dependencies

O

For More Information

Refer to the followings.

order by clause

Function

It specifies the sorting order of the query results.

Syntax

<order by clause> ::=
    ORDER BY <sort specification list>

<sort specification list> ::=
    <sort specification> [ { <comma> <sort specification> }... ]

<sort specification> ::=
    <sort key> [ <ordering specification> ] [ <null ordering> ]

<sort key> ::=
    <value expression>

<ordering specification> ::=
      ASC
    | DESC

<null ordering> ::=
      NULLS FIRST
    | NULLS LAST

Invocation and Access Rules

The access privilege for a column is required if the column exist in a sort key specified for sorting.

Syntax Rules and Parameters

<order by clause>

<sort specification list>

<sort key>

Description

<order by clause>

<order by clause> specifies a method to sort the query results.

<sort key> can be listed in <order by clause> by using a comma (,), and <sort key> of each records are compared and listed in order.

SELECT c1, c2 FROM t1 ORDER BY c1, c2;

<ordering specification> which specifies an ascending order or an descending order can be specified in <sort key>. If it is omitted, then they are sorted in an ascending order.

gSQL> SELECT c1 FROM t1;
C1
--
 2
 3
 1
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1;
C1
--
 1
 2
 3
3 rows selected.

gSQL> SELECT c1 FROM t1 ORDER BY c1 ASC;
C1
--
 1
 2
 3
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1 DESC;
C1
--
 3
 2
 1
3 rows selected.
<null ordering> specifies an order between the non-NULL values and NULL values in <sort key>. If it is omitted, then they are sorted as NULLS LAST.
gSQL> SELECT c1 FROM t1;
  C1
----
   2
null
   1
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1;    
  C1
----
   1
   2
null
3 rows selected.

gSQL> SELECT c1 FROM t1 ORDER BY c1 NULLS LAST;
  C1
----
   1
   2
null
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1 NULLS FIRST;
  C1
----
null
   1
   2
3 rows selected.

If a constant value is specified in <sort key>, the expression positioned in the location corresponding to the order of the corresponding value in <select list> is regarded as <sort key>. In this case, the constant is an integer bigger than 0, and it should be equal or smaller than the total number of expression in <select list>.

gSQL> SELECT c1 FROM t1 ORDER BY 1;
  C1
----
   1
   2
null
3 rows selected.

LONG (LONG VARCHAR, LONG VARBINARY) type can not be specified in <sort key>.

Comparison of Null Value

Sorting Rows Which Have Same Sort Key Value

Peers are rows which can not be distinguished by a sort key, and the peers are sorted according to the scan order.

<aggregation function> Which Is Used As <sort key>

If <aggregation function> is used in <query specification>, or <group by clause> is specified, <aggregation function> can be used as <sort key>.
However, the nested aggregation function can be used as <sort key> only when <group by clause> is specified.
gSQL> SELECT c1, c2 FROM t1;
C1 C2
-- --
 2  1
 3  5
 1  2
 2 10
 3 10
5 rows selected.

gSQL> SELECT sum(c1) FROM t1 ORDER BY sum(c1);
SUM(C1)
-------
     11
1 row selected.

gSQL> SELECT c1, sum(c2) FROM t1 GROUP BY c1 ORDER BY sum(c2);
C1 SUM(C2)
-- -------
 1       2
 2      11
 3      15
3 rows selected.

gSQL> SELECT sum(c1) FROM t1 GROUP BY c1 ORDER BY sum(sum(c1));
SUM(C1)
-------
      6
1 row selected.

Example

The following is an example of SELECT statement which uses ORDER BY clause.

gSQL> SELECT c_name, c_nation FROM customer ORDER BY c_nation;

C_NAME     C_NATION
---------- -------------
Customer#2 CANADA
Customer#4 GERMANY
Customer#1 KOREA
Customer#3 KOREA
Customer#5 UNITED STATES

5 rows selected.

gSQL> SELECT c_name, c_nation FROM customer ORDER BY c_nation DESC;

C_NAME     C_NATION
---------- -------------
Customer#5 UNITED STATES
Customer#1 KOREA
Customer#3 KOREA
Customer#4 GERMANY
Customer#2 CANADA

5 rows selected.

gSQL> SELECT c_name, c_nation FROM customer ORDER BY 2 DESC;

C_NAME     C_NATION     
---------- -------------
Customer#5 UNITED STATES
Customer#1 KOREA        
Customer#3 KOREA        
Customer#4 GERMANY      
Customer#2 CANADA       

5 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F850

Top-level <order by clause> in <query expression>

O

F851

<order by clause> in subqueries

O

F852

Top-level <order by clause> in views

O

F855

Nested <order by clause> in <query expression>

O

For More Information

Refer to query expression.

offset limit clause

Function

It specifies the number of rows to skip and the number of rows to fetch from the query results.

Syntax

<offset limit clause> ::=
      <result offset clause>
    | <fetch limit clause>
    | <result offset clause> <fetch limit clause>

<result offset clause> ::=
    OFFSET <offset row count> [ { ROW | ROWS } ]

<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>

<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ <fetch row count> ] [ ROW ONLY | ROWS ONLY ]

<limit clause> ::=
    LIMIT { <fetch row count> | <offset row count> , <fetch row count> | ALL }

Invocation and Access Rules

The access privilege for <offset limit clause> is not required.

Syntax Rules and Parameters

<result offset clause>

<fetch limit clause>

<fetch first clause>

<limit clause>

Description

<result offset clause>

It fetches rows from the <offset row count>th of the query results. If the result which <offset row count> queried is equal to or greater than the number of rows, the number of fetch rows is 0.

gSQL> SELECT c1 FROM t1;
C1
--
 1
 2
 3
3 rows selected.

gSQL> SELECT c1 FROM t1 OFFSET 1;
C1
--
 2
 3
2 rows selected.

gSQL> SELECT c1 FROM t1 OFFSET 3;
no rows selected.

<fetch first clause>

It fetches the query results as many as the number of <fetch row count>.

gSQL> SELECT c1 FROM t1;
C1
--
 1
 2
 3
3 rows selected.

gSQL> SELECT c1 FROM t1 FETCH FIRST 2 ROWS ONLY;
C1
--
 1
 2
2 rows selected.

<limit clause>

When LIMIT <fetch_row_count> is used, it fetches the query results as many as the number of <fetch row count>.

When LIMIT <offset row count> is used, <fetch row count>, it fetches the query results as many as the number of <fetch row count> from the <offset row count>th row.

When LIMIT ALL is used, it returns the query results to a user without limit of the number.

gSQL> SELECT c1 FROM t1;
C1
--
 1
 2
 3
3 rows selected.

• LIMIT <fetch_row_count>
gSQL> SELECT c1 FROM t1 LIMIT 2;
C1
--
 1
 2
2 rows selected.

• LIMIT <offset row count>, <fetch_row_count>
gSQL> SELECT c1 FROM t1 LIMIT 1, 1;
C1
--
 2
1 row selected.

• LIMIT ALL
gSQL> SELECT c1 FROM t1 LIMIT ALL;
C1
--
 1
 2
 3
3 rows selected.

Examples

The following is an example of SELECT statement which uses <result offset clause>.

gSQL> SELECT c_name, c_nation FROM customer OFFSET 1;

C_NAME     C_NATION
---------- -------------
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

4 rows selected.

The following is an example of SELECT statement which uses <fetch first clause>.

gSQL> SELECT c_name, c_nation FROM customer FETCH FIRST ROW ONLY;

C_NAME     C_NATION
---------- --------
Customer#1 KOREA

1 row selected.

gSQL> SELECT c_name, c_nation FROM customer FETCH FIRST 2 ROW ONLY;

C_NAME     C_NATION
---------- --------
Customer#1 KOREA
Customer#2 CANADA

2 rows selected.

The following is an example of SELECT statement which uses <limit clause>.

gSQL> SELECT c_name, c_nation FROM customer LIMIT 1;

C_NAME     C_NATION
---------- --------
Customer#1 KOREA

1 row selected.

gSQL> SELECT c_name, c_nation FROM customer LIMIT 1, 2;

C_NAME     C_NATION
---------- --------
Customer#2 CANADA
Customer#3 KOREA

2 rows selected.

gSQL> SELECT c_name, c_nation FROM customer LIMIT ALL;

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.

The following is an example of SELECT statement which uses <result offset clause> and <fetch limit clause>.

gSQL> SELECT c_name, c_nation FROM customer OFFSET 1 FETCH 2;

C_NAME     C_NATION
---------- --------
Customer#2 CANADA
Customer#3 KOREA

2 rows selected.

gSQL> SELECT c_name, c_nation FROM customer OFFSET 1 LIMIT 2;

C_NAME     C_NATION
---------- --------
Customer#2 CANADA
Customer#3 KOREA

2 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F861

Top-level <result offset clause> in <query expression>

O

F862

<result offset clause> in subqueries

O

F863

Nested <result offset clause> in <query expression>

O

F864

Top-level <result offset clause> in views

O

F865

dynamic <offset row count> in <result offset clause>

X

set operator

Function

It performs a set operation for results of the subquery.

Syntax

<set operator> ::=
      <set operator term>
    | <query expression body> UNION [ ALL | DISTINCT ] <set operator term>
    | <query expression body> EXCEPT [ ALL | DISTINCT ] <set operator term>
    | <query expression body> MINUS [ ALL | DISTINCT ] <set operator term>

<set operator term> ::=
      <query term>
    | <set operator term> INTERSECT [ ALL | DISTINCT ] <set operator term>

Invocation and Access Rules

All access privileges for the <query expression> in each <set operator term> are required for using <set operator> statement.

Syntax Rules and Parameters

<set operator>

<query term>

It specifies the single subquery.
For more information, refer to query expression.

Description

The Differences between ALL and DISTINCT in <set operator>

For example, if the data of the table R1 and R2 is given as follows, the result of each <set operator > is as follows.

SET operation results

SET operation results

Operator Precedence

The operator precedence of <set operator> is as follows.

Result Type of <set operator>

The i-th column of all subqueries in <set operator> should be a data type of the same family, and its result type is determined by  Result Type Combination Rule.
However, LONG VARCHAR and LONG VARBINARY types can only use UNION ALL.

ORDER BY Clause

When <set operator> is used together with ORDER BY, and the column names are different among subqueries, then it can be used as follows.

Examples

The following is an example of SELECT statement which uses UNION operator.

gSQL> SELECT s_nation nation FROM supplier UNION ALL SELECT c_nation FROM customer;

NATION
-------------
FRANCE
KOREA
GERMANY
UNITED STATES
CANADA
KOREA
CANADA
KOREA
GERMANY
UNITED STATES

10 rows selected.

gSQL> SELECT s_nation nation FROM supplier UNION DISTINCT SELECT c_nation FROM customer;

NATION
-------------
UNITED STATES
CANADA
KOREA
GERMANY
FRANCE

5 rows selected.

The following is an example of SELECT statement which uses EXCEPT operator.

gSQL> SELECT c_nation nation FROM customer EXCEPT ALL SELECT s_nation FROM supplier;

NATION
------
KOREA

1 row selected.

gSQL> SELECT c_nation nation FROM customer EXCEPT DISTINCT SELECT s_nation FROM supplier;

no rows selected.

The following is an example of SELECT statement which uses INTERSECT operator.

gSQL> SELECT c_nation nation FROM customer INTERSECT ALL SELECT s_nation FROM supplier;

NATION
-------------
UNITED STATES
CANADA
KOREA
GERMANY

4 rows selected.

gSQL> SELECT c_nation nation FROM customer INTERSECT DISTINCT SELECT s_nation FROM supplier;

NATION
-------------
UNITED STATES
CANADA
KOREA
GERMANY

4 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F302

INTERSECT table operator

O

F301

CORRESPONDING

X

T551

Optional key words for default syntax

O

F304

EXCEPT ALL table operator

O

For More Information

Refer to query expression.

subquery

Function

It specifies the scalar value, row, table which are derived from <query expression>.

Syntax

<scalar subquery> ::=
    <subquery>

<row subquery> ::=
    <subquery>

<table subquery> ::=
    <subquery>

<subquery> ::=
    ( <query expression> )

Invocation and Access Rules

The access privilege for <query expression> in <subquery> is required.

Syntax Rules and Parameters

<scalar subquery>

<row subquery>

<table subquery>

Description

<scalar subquery>

<scalar subquery> returns one row which has one column as a result. The target of <scalar subquery> should be only one, and the result data type depends on the data type of the target.

<scalar subquery> can be used alone in the target of <select list>, and it can be used in the operator which has a single column.

<row subquery>

<row subquery> returns one row which has two or more columns as a result. The targets of <row subquery> should be two or more, and the result data type depends on the data type of each target.

<row subquery> can not be used alone in the target of <select list>, and it can only be used in the row operator which has two or more columns.

<table subquery>

<table subquery> returns one or more rows which have one or more columns as a result. The targets of <table subquery> should be one or more, and the result data type depends on the data type of each target.

<table subquery> can not be used alone in the target of <select list>, but it can be used in the operators such as IN, NOT IN, EXISTS, NOT EXISTS, quantify operator.

Examples

The following is an example of SELECT statement which uses <scalar subquery>.

gSQL> SELECT (SELECT c_name FROM dual)  FROM customer;

(SELECT C_NAME FROM DUAL)
-------------------------
Customer#1
Customer#2
Customer#3
Customer#4
Customer#5

5 rows selected.

gSQL> SELECT c_name, c_nation FROM customer WHERE c_nation = (SELECT 'CANADA' FROM dual);

C_NAME     C_NATION
---------- --------
Customer#2 CANADA

1 row selected.

The following is an example of SELECT statement which uses <row subquery>.

gSQL> SELECT p_name, p_brand, p_type FROM part WHERE (p_brand, p_type) = (SELECT 'Brand#1', 'NICKEL' FROM dual);

P_NAME P_BRAND    P_TYPE
------ ---------- ------
Part#2 Brand#1    NICKEL

1 row selected.

The following is an example of SELECT statement which uses <table subquery>.

gSQL> SELECT s_name, s_nation FROM supplier WHERE s_nation IN (SELECT c_nation FROM customer);

S_NAME                    S_NATION
------------------------- -------------
Supplier#2                KOREA
Supplier#3                GERMANY
Supplier#4                UNITED STATES
Supplier#5                CANADA

4 rows selected.

gSQL> SELECT * FROM (SELECT s_name, s_nation FROM supplier);

S_NAME                    S_NATION
------------------------- -------------
Supplier#1                FRANCE
Supplier#2                KOREA
Supplier#3                GERMANY
Supplier#4                UNITED STATES
Supplier#5                CANADA

5 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F471

Scalar subquery values

O

F641

Row and table constructors

X

T501

Enhanced EXISTS predicate

O

E061-11

Subqueries in IN predicate

O

E061-12

Subqueries in quantified comparison predicate

O

E061-12

Correlated subqueries

O

For More Information

Refer to the followings.

hint clause

It specifies a hint to be used for a query execution.
For more information, refer to SQL Hint.

SELECT .. FOR UPDATE

Function

It sets whether or not to update the result set of SELECT statement.

Syntax

<select for update statement> ::=
    <query expression>  <updatability clause>
    ;

<updatability clause> ::=
      FOR READ ONLY 
    | FOR UPDATE [ OF <column name list> ] [ <lock wait mode> ]

<lock wait mode> ::=
    | WAIT
    | WAIT second
    | NOWAIT

Invocation and Access Rules

The user should satisfy the following conditions to perform <select for update statement>.

Syntax Rules and Parameters

<query expression>

INTO clause should not exist in SELECT statement.

To use FOR UPDATE, the query should identify the row updates of the base table, or it should be an updatable query which can acquire the lock into the row.

The updatable query should satisfy all of following conditions.

For more information about SELECT statement, refer to query expression.

<updatability clause>

It specifies whether or not to update the row for the result set.

FOR UPDATE OF …

It lists the columns relating to acquiring lock when executing the query.

<lock wait mode>

It is used together with FOR UPDATE statement, and it specifies the lock acquisition method.

Description

SELECT statement keep fetching the rows regardless of whether the transaction ends. However, SELECT .. FOR UPDATE statement can not fetch the rows when the transaction ends because the statement acquires the lock for the rows.

Cursor holdability



Examples

The following is an example of acquiring a lock for the row by using FOR UPDATE statement.

gSQL> SELECT id, data FROM t1 WHERE id = 3 FOR UPDATE;

ID DATA  
-- ------
 3 data_3

1 row selected.

The following uses join and ORDER BY clause but it is an updatable query, so FOR UPDATE statement can be used.

gSQL> SELECT t1.id, t1.name, t2.addr 
        FROM t1, t2
       WHERE t1.id = t2.id
       ORDER BY 1
         FOR UPDATE;

ID NAME    ADDR         
-- ------- -------------
 1 someone somewhere    
 2 anyone  anywhere     
 3 unknown N/A          
 4 leekmo  leekmo's home
 5 mkkim   seoul        

5 rows selected.

The following is a non-updatable query, so FOR UPDATE statement can not be used.

gSQL> SELECT id, COUNT(*)
        FROM t1
       GROUP BY id
         FOR UPDATE;

ERR-42000(16112): query expression is not updatable

Compatibility

In the SQL standard, <select for update statement> is not defined, but it can be defined by using DECLARE cursor_name statement.

SELECT .. INTO

Function

It retrieves a single row by using a query, then obtains the value of retrieved row into the host variable.

Syntax

<select statement: single row> ::=
    SELECT [ <hint clause> ] [ <set quantifier> ] <select list>
        INTO <select target list>
        <table expression>
    ;

<select target list> ::=
    variable_name [, ...]

Invocation and Access Rules

One of the following privileges for all tables used in the statement is required for a user to perform <select statement: single row>.

Syntax Rules and Parameters

<hint clause>

It specifies hints for query execution.
For more information, refer to hint clause of SELECT statement.

<set quantifier>

It specifies whether to remove duplicates from the query result.
For more information, refer to query specification clause.

<select list>

It specifies the columns to be retrieved from the query result.
For more information, refer to select list clause.

INTO <select target list>

The number of the variable specified in INTO clause should be equal to the number of the expression specified in <select list>.

<table expression>

It specifies the query information such as a search condition.
For more information, refer to query specification clause.

Description

The rows to be retrieved should be one or less.
If two or more rows are retrieved, an error occurs.

Differences among SELECT-related Statements

Example

The following is an example of obtaining the value into the host variable by using interactive SQL (gsql).

gSQL> \var v_id   INTEGER
gSLQ> \var v_data VARCHAR(128)

gSQL> SELECT id, data INTO :v_id, :v_data FROM t1 WHERE id = 3;

V_ID V_DATA
---- ------
   3 data_3

1 row selected.

SELECT .. INTO .. FOR UPDATE

Function

It sets whether to update the row by retrieving a single row through the query, then obtains the value of retrieved row into the host variable.

Syntax

<select for update statement: single row> ::=
    SELECT [ <hint clause> ] [ <set quantifier> ] <select list>
        INTO <select target list>
        <table expression>  <updatability clause>
    ;

<select target list> ::=
    variable_name [, ...]

<updatability clause> ::=
      FOR READ ONLY 
    | FOR UPDATE [ OF <column name list> ] [ <lock wait mode> ]

<lock wait mode> ::=
    | WAIT
    | WAIT second
    | NOWAIT

Invocation and Access Rules

One of the following privileges for all tables used in the statement is required for a user to perform <select statement: single row>.

If FOR UPDATE clause is used, one of the following privileges for the tables to be locked is required.

Syntax Rules and Parameters

<select for update statement: single row>

To use FOR UPDATE, the query should identify the row updates of the base table, or it should be an updatable query which can acquire the lock into the row.

The updatable query should satisfy all of following conditions.

<updatability clause>

It specifies whether or not to update the row for the result set.

FOR UPDATE OF …

It lists the columns relating to acquiring lock when executing the query.

<lock wait mode>

It is used together with FOR UPDATE statement, and it specifies the lock acquisition method.

<hint clause>

It specifies hints for query execution.
For more information, refer to hint clause of SELECT statement.

<set quantifier>

It specifies whether to remove duplicates from the query result.
For more information, refer to query specification clause.

<select list>

It specifies the columns to be retrieved from the query result.
For more information, refer to select list clause.

INTO <select target list>

The number of the variable specified in INTO clause should be equal to the number of the expression specified in <select list>.

<table expression>

It specifies the query information such as a search condition.
For more information, refer to query specification clause.

Description

The rows to be retrieved should be one or less.
If two or more rows are retrieved, an error occurs.

SELECT statement keep fetching the rows regardless of whether the transaction ends. However, SELECT .. FOR UPDATE statement can not fetch the rows when the transaction ends because the statement acquires the lock for the rows.

Cursor holdability



Differences among SELECT-related Statements

Examples

The following is an example of acquiring a lock for the row by using FOR UPDATE statement, and obtaining the value into the host variable by using interactive SQL (gsql).

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> SELECT id, data INTO :v_id, :v_data FROM t1 WHERE id = 3 FOR UPDATE;

V_ID V_DATA
---- ------
   3 data_3

1 row selected.

The following uses join and ORDER BY clause but it is an updatable query, so FOR UPDATE statement can be used.

gSQL> \var v_id   INTEGER
gSQL> \var v_name VARCHAR(128)
gSQL> \var v_addr VARCHAR(128)


gSQL> SELECT t1.id, t1.name, t2.addr 
        INTO :v_id, :v_name, :v_addr
        FROM t1, t2
       WHERE t1.id = t2.id
       ORDER BY 1
       LIMIT 1
         FOR UPDATE;

ID NAME    ADDR         
-- ------- -------------
 1 someone somewhere    

1 row selected.

The following is a non-updatable query, so FOR UPDATE statement can not be used.

gSQL> \var v_id    INTEGER
gSQL> \var v_count INTEGER

gSQL> SELECT id, COUNT(*)
        INTO :v_id, :v_count
        FROM t1
       GROUP BY id
         FOR UPDATE;

ERR-42000(16112): query expression is not updatable

For More Information

Refer to the followings.

SET CONSTRAINTS

Function

It sets the check point of deferrable constraint in a transaction to IMMEDIATE or DEFERRED.

Syntax

<set constraints mode statement> ::=
    SET { CONSTRAINT | CONSTRAINTS } <constraint name list> { DEFERRED | IMMEDIATE }
    ;

<constraint name list> ::=
      ALL
    | <constraint name> [, ...]

Invocation and Access Rules

Any separate access privilege is not required for a user to perform SET CONSTRAINTS.

It is not supported in the cluster system.

Syntax Rules and Parameters

CONSTRAINT | CONSTRAINTS

CONSTRAINT and CONSTRAINTS are the keywords of the same meaning, and the SQL standard uses CONSTRAINTS.

<constraint name list>

It specifies the list of constraint names, or specifies ALL to set all deferrable constraints.
When specifying <constraint name>, it should be the name of the deferrable constraint.
ALL means all deferrable constraints.

DEFERRED | IMMEDIATE

It sets the check point of specified deferrable constraints.

If the transaction is in progress, the check point of the constraint is set in the current transaction. If the transaction is not in progress, it is set in the next transaction.
After the transaction ends, it does not affect the next transaction.

Description

Deferrable Constraint

DEFERRABLE constraint can change its check point.
The following is an example of creating a table with a deferrable constraint, and inserting data to the table.
gSQL> CREATE TABLE t1 
( 
    id   INTEGER, 
    name VARCHAR(128) CONSTRAINT t1_uk UNIQUE 
                      DEFERRABLE INITIALLY IMMEDIATE
);

Table created.

gSQL> COMMIT;

Commit complete.

gSQL> INSERT INTO t1 VALUES ( 1, 'leekmo' );

1 row created.

gSQL> INSERT INTO t1 VALUES ( 2, 'mkkim' );

1 row created.

gSQL> COMMIT;

Commit complete.

In the example above, UNIQUE constraint which is deferrable is created on a name column, and the initial check point is set as INITIALLY IMMEDIATE. Therefore, the constraint is checked whenever DML statement is executed.

In this case, if the user tries to exchange the name value of two rows as follows, it violates the constraint because the check point is IMMEDIATE.

gSQL> UPDATE t1 SET name = 'mkkim' WHERE id = 1;

ERR-23000(16057): unique constraint (PUBLIC.T1_UK) violated

gSQL> UPDATE t1 SET name = 'leekmo' WHERE id = 2;

ERR-23000(16057): unique constraint (PUBLIC.T1_UK) violated

If the check point is changed to DEFERRED as follows, the operation as same as above succeeds because the constraint is checked when the transaction is committed.

gSQL> SET CONSTRAINTS t1_uk DEFERRED;

Constraints set.

gSQL> UPDATE t1 SET name = 'mkkim' WHERE id = 1;

1 row updated.

gSQL> UPDATE t1 SET name = 'leekmo' WHERE id = 2;

1 row updated.

gSQL> COMMIT;

Commit complete.

If the check point is set to DEFERRED, then the constraint is checked when the transaction is committed. Therefore, if the transaction is committed when the constraint is violated, then the transaction fails and it is rolled back as follows.

gSQL> SET CONSTRAINTS t1_uk DEFERRED;

Constraints set.

gSQL> INSERT INTO t1 VALUES ( 3, 'leekmo' );

1 row created.

gSQL> COMMIT;

ERR-40002(16291): transaction rollback: integrity constraint violation : PUBLIC.T1_UK(1)

Violation of a Deferred Constraint

Executing the following statements when the transaction violates the constraints set to DEFFFERED, then an error occurs as follows.

An unexpected ROLLBACK can occur when COMMIT, it is necessary to ensure whether the transaction violates the constraint by using SET CONSTRAINTS ALL IMMEDIATE statement.

gSQL> SET CONSTRAINTS t1_uk DEFERRED;

Constraints set.

gSQL> INSERT INTO t1 VALUES ( 3, 'leekmo' );

1 row created.

gSQL> SET CONSTRAINTS ALL IMMEDIATE;

ERR-23000(16038): integrity constraint violation : PUBLIC.T1_UK(1)

gSQL> SELECT * FROM t1 ORDER BY id;

ID NAME  
-- ------
 1 mkkim 
 2 leekmo
 3 leekmo

3 rows selected.

gSQL> UPDATE t1 SET name = 'xcom73' WHERE id = 3;

1 row updated.

gSQL> SET CONSTRAINTS ALL IMMEDIATE;

Constraints set.

gSQL> COMMIT;

Commit complete.

Transaction Control Language

SET CONSTRAINTS statement is a transaction control language which is used when the transaction is in progress such as SAVEPOINT savepoint_specifier.
The transaction control such as COMMIT, ROLLBACK, ROLLBACK TO SAVEPOINT statement is applied to SET CONSTRAINTS statement.
The following is an example of a table with multiple deferrable constraints.
CREATE TABLE t1
(
   id1 INTEGER CONSTRAINT t1_uk1 UNIQUE DEFERRABLE INITIALLY IMMEDIATE,
   id2 INTEGER CONSTRAINT t1_uk2 UNIQUE DEFERRABLE INITIALLY IMMEDIATE,
   id3 INTEGER CONSTRAINT t1_uk3 UNIQUE DEFERRABLE INITIALLY IMMEDIATE
);

If <set constraints mode statement> statement is performed when the transaction is in progress, the check point of deferrable constraints is changed depending on each point as follows.

INSERT INTO t1 VALUES ( 1, 1, 1 );

1 row created.

COMMIT;

Commit complete.
SAVEPOINT sp1;

Savepoint created.
SET CONSTRAINTS t1_uk1 DEFERRED;

Constraints set.
SAVEPOINT sp2;

Savepoint created.
SET CONSTRAINTS t1_uk2 DEFERRED;

Constraints set.
SAVEPOINT sp3;

Savepoint created.
SET CONSTRAINTS ALL DEFERRED;

Constraints set.
SAVEPOINT sp4;

Savepoint created.
SET CONSTRAINTS ALL IMMEDIATE;

Constraints set.

When the transaction is partially rolled back by using ROLLBACK TO SAVEPOINT statement as follows, SET CONSTRAINTS statement is also partially rolled back and the check point is changed.

INSERT INTO t1 VALUES ( 1, 2, 2 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK1) violated
INSERT INTO t1 VALUES ( 3, 1, 3 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK2) violated
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
ROLLBACK TO SAVEPOINT sp4;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

1 row created.
INSERT INTO t1 VALUES ( 3, 1, 3 );

1 row created.
INSERT INTO t1 VALUES ( 4, 4, 1 );

1 row created.
ROLLBACK TO SAVEPOINT sp3;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

1 row created.
INSERT INTO t1 VALUES ( 3, 1, 3 );

1 row created.
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
ROLLBACK TO SAVEPOINT sp2;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

1 row created.
INSERT INTO t1 VALUES ( 3, 1, 3 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK2) violated
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
ROLLBACK TO SAVEPOINT sp1;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK1) violated
INSERT INTO t1 VALUES ( 3, 1, 3 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK2) violated
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
SELECT * FROM t1;

ID1 ID2 ID3
--- --- ---
  1   1   1

1 row selected.

When the transaction is committed or rolled back, the effects of SET CONSTRAINTS statement is also terminated, and all deferrable constraints follows the constraints property which is INITIALLY IMMEDIATE or INITIALLY DEFERRED value.

Examples

The following is an example of changing the check point by specifying the constraint name.

gSQL> SET CONSTRAINTS t1_uk1 DEFERRED;

Constraints set.

The following is an example of changing the check point of all deferrable constraints.

gSQL> SET CONSTRAINTS ALL DEFERRED;

Constraints set.

Compatibility

The SQL standard does not define CONSTRAINT keyword clause.

SQL standard compatibility

Feature ID

Description

Compatibility

F721

Deferrable constraints

O

For More Information

Refer to the followings.

SET SCHEMA schema_name

Function

It sets the default schema name to be used in the current session.

Syntax

<set schema statement> ::=
    SET SCHEMA schema_name
    ;

Invocation and Access Rules

N/A

Syntax Rules and Parameters

schema_name

It is the default schema name to be set in the current session.

Description

It sets the default schema name to be used in the current session.
If the schema name of the object is not specified, then it becomes the default schema name to be used in the current session.
% gsql u1 u1
gsql> SELECT * FROM r;
gsql> SET SCHEMA new_schema;

gsql> SELECT * FROM r;

Examples

The following is an example of when user u1 has schema s1 and s2.

CREATE USER u1 IDENTIFIED BY u1 WITHOUT SCHEMA;
CREATE SCHEMA s1 AUTHORIZATION u1;
CREATE SCHEMA s2 AUTHORIZATION u1;
COMMIT;

ALTER USER u1 SCHEMA PATH ( s1, s2 );
GRANT ALL PRIVILEGES TO u1;
COMMIT;

CREATE TABLE s1.t1 ( c1 VARCHAR(32) );
INSERT INTO s1.t1 VALUES ( 'S1.T1' );
COMMIT;

CREATE TABLE s2.t1 ( c1 VARCHAR(32) );
INSERT INTO s2.t1 VALUES ( 'S2.T1' );
COMMIT;

It retrieves table S1.T1 by interpreting table t1 using schema path of user u1 when accessing for the first time.

% gsql u1 u1

gSQL> SELECT current_schema FROM dual;

CURRENT_SCHEMA
--------------
S1            

1 row selected.


gSQL> SELECT * FROM t1;

C1   
-----
S1.T1

1 row selected.

It retrieves table S2.T1 by interpreting table t1 using schema name of the session after SET SCHEMA statement is used.

gSQL> SET SCHEMA s2;

Session set.


gSQL> SELECT current_schema FROM dual;

CURRENT_SCHEMA
--------------
S2            

1 row selected.


gSQL> SELECT * FROM t1;

C1   
-----
S2.T1

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F761

Session management

O

SET SESSION AUTHORIZATION user_identifier

Function

It sets the session user and current user.

Syntax

<set session user identifier statement> ::=
    SET SESSION AUTHORIZATION user_identifier
    ;

Invocation and Access Rules

ACCESS CONTROL ON DATABASE privilege is required for a logon user to perform <set session user identifier statement>.

The user information is managed in three types as follows.

Syntax Rules and Parameters

user_identifier

It is the username to be altered.

Description

After performing SET SESSION AUTHORIZATION statement, all statements is performed based on the session user. Therefore, the privilege for the session user is checked and the owner of when creating objects also is the session user.

Example

The following is an example that the user test with ACCESS CONTROL ON DATABASE privilege sets the user u1 to the session user.

gSQL> SET SESSION AUTHORIZATION u1;

Session set.

gSQL> SELECT LOGON_USER(), SESSION_USER(), CURRENT_USER FROM dual;

LOGON_USER() SESSION_USER() CURRENT_USER
------------ -------------- ------------
TEST         U1             U1          

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F321

User authorization

O

SET SESSION CHARACTERISTICS AS transaction_mode

Function

It sets the transaction property of a session.

Syntax

<set session characteristics statement> ::=
    SET SESSION CHARACTERISTICS AS TRANSACTION <transaction_mode>
    ;

<transaction_mode> ::=
    { <transaction_access_mode> | ISOLATION LEVEL < isolation_level > }

<transaction_access_mode> ::=
    READ { ONLY | WRITE }

< isolation_level > ::=
    { READ COMMITTED | SERIALIZABLE }

Syntax Rules and Parameters

<transaction_access_mode>

It is ACCESS MODE of the following transactions.

<isolation_level>

It is ISOLATION LEVEL of the following transactions.

Description

SET SESSION CHARACTERISTICS sets the transaction property of a session. In other words, properties of all transactions created within the session follows these properties.
However, SET TRANSACTION transaction_mode statement sets only the property of a single transaction which is performed next.

Examples

The following is an example that all transactions to be created within the session are set to READ ONLY.

gSQL> SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

Session set.

The following is an example that the isolation level of all transactions to be created within the session is set to READ COMMITTED.

gSQL> SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

Session set.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F761

Session management

O

For More Information

Refer to SET TRANSACTION transaction_mode.

SET TIME ZONE

Function

It sets the TIMEZONE of a session.

Syntax

<set local time zone statement> ::=
    SET TIME ZONE <set time zone value>
    ;

<set time zone value> ::= 
    { '[+|-]hh:mm' | LOCAL }

Syntax Rules and Parameters

<set time zone value>

It is the TIMEZONE value to be set.

Description

Altering the time zone of the session affects the result value of function such as CURRENT_TIME, CURRENT_TIMESTAMP.

Example

The following is an example of altering the session time zone to '+09: 00'.

gSQL> SET TIME ZONE '+09:00';

Session set.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F411

Time zone specifications

O

SET TRANSACTION transaction_mode

Function

It sets the transaction property.

Syntax

<set transaction statement> ::=
    SET TRANSACTION <transaction_mode>
    ;

<transaction_mode> ::=
    { <transaction_access_mode> | ISOLATION LEVEL < isolation_level > }

<transaction_access_mode> ::=
    READ { ONLY | WRITE }

< isolation_level > ::=
    { READ COMMITTED | SERIALIZABLE }

Syntax Rules and Parameters

<transaction_access_mode>

It is ACCESS MODE of the following transactions.

<isolation_level>

It is ISOLATION LEVEL of the following transactions.

Description

SET TRANSACTION sets property of the next transaction, and the property is reset to the default value after the next transaction ends.

Example

The following is an example of setting the next transaction to READ ONLY.

gSQL> SET TRANSACTION READ ONLY;

Transaction set.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T251

SET TRANSACTION statement: LOCAL option

X

For More Information

Refer to SET SESSION CHARACTERISTICS AS transaction_mode.

TRUNCATE TABLE

Function

It truncates all rows from a table.

Syntax

<truncate table statement> ::= 
    TRUNCATE TABLE table_name 
        [ RESTART IDENTITY | CONTINUE IDENTITY ] 
        [ DROP STORAGE | DROP ALL STORAGE ] 
    ;

Invocation and Access Rules

One of the following privileges is required for a user to perform <truncate table statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be truncated.
It can define schema to which the table belongs such as schema_name.table_name and if schema_name is omitted, the default schema name of the user performing the statement is used.

[ RESTART IDENTITY | CONTINUE IDENTITY ]

[ DROP STORAGE | DROP ALL STORAGE ]

Description

Data Definition Language (DDL) statement such as TRUNCATE TABLE can be rolled back if it is before when the transaction is committed.

Examples

The following is an example of performing TRUNCATE TABLE statement.

gSQL> TRUNCATE TABLE t1;

Table truncated.

The following is an example of restarting the value of the identity column when performing TRUNCATE TABLE.

TRUNCATE TABLE t1 RESTART IDENTITY;

Table truncated.

Compatibility

The SQL standard does not define [ DROP STORAGE | DROP ALL STORAGE ] clause.

SQL standard compatibility

Feature ID

Description

Compatibility

F200

TRUNCATE TABLE statement

O

F202

TRUNCATE TABLE: identity column restart option

O

UPDATE

Function

It updates rows in a table.

Syntax

<update statement: searched> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
    ;

<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )


<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]


<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>


<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]


<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }

Invocation and Access Rules

One of the following privileges is required for a user to perform <update statement: searched>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be updated.
It can define schema to which the table belongs such as schema_name.table_name and if schema_name is omitted, the default schema name of the user performing the statement is used.

[ AS alias_name ]

It is the alias of table_name.

<set clause>

It defines the columns to be updated and its values to be assigned. The number of columns in <set clause> should be as same as the number of values.
It can be defined as follows.
UPDATE table_name 
   SET column1 = value1, column2 = value2, column3 = value3
UPDATE table_name 
   SET ( column1, column2, column3 ) = ( value1, value2, value3 )
UPDATE table_name 
   SET column1 = ( SELECT max(value1) FROM other_table_name )
<query expression> should be a query which creates a single row.
If DEFAULT is defined as a column value, the default values (refer to <default clause>) defined when executing CREATE TABLE is used. If it is not defined, NULL value is assigned.

WHERE <search condition>

It updates the rows which satisfy WHERE condition.
If WHERE condition is not specified, all rows are updated.
For more information about WHERE condition, refer to where clause of SELECT.

<result offset clause>

It specifies the number of rows to skip from the query result.
For more information, refer to <result offset clause> of SELECT.

<fetch limit clause>

It specifies the number of rows to fetch in two ways, which are <fetch first clause> and <limit clause>.

Description

Differences among UPDATE-related Statements

Examples

The following is an example of updating multiple rows which satisfy the condition.

gSQL> UPDATE lineitem
         SET l_shipdate = CURRENT_DATE
       WHERE l_returnflag = 'R';

5 rows updated.

The following is an example of updating the value of multiple columns.

gSQL> UPDATE lineitem
         SET l_shipdate   = CURRENT_DATE
           , l_returnflag = 'A'
       WHERE l_returnflag = 'R';

5 rows updated.

The following is an example of updating multiple columns by enclosing them with parentheses.

gSQL> UPDATE lineitem
         SET ( l_shipdate  , l_returnflag )
           = ( CURRENT_DATE, 'A' )
       WHERE l_returnflag = 'R';

5 rows updated.

The following is an example of updating the column value by using the subquery.

gSQL> UPDATE lineitem
         SET l_discount = ( SELECT MAX(l_discount) + 0.01 FROM lineitem )
       WHERE l_returnflag = 'R';

5 rows updated.

The following is an example of updating part of the rows which satisfy the condition by using OFFSET and FETCH clauses.

gSQL> UPDATE lineitem
         SET l_discount = l_discount + 0.01
       WHERE l_returnflag = 'R'
      OFFSET 3
      FETCH 2;

2 rows updated.

Compatibility

The SQL standard does not define the following clauses in UPDATE statement.

SQL standard compatibility

Feature ID

Description

Compatibility

F781

Self-referencing operations

X

T111

Updatable joins, unions, and columns

X

UPDATE name RETURNING

Function

It updates rows in a table, and retrieves the rows of before or after the update.

Syntax

<update statement: searched> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        <returning clause>

<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )


<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]


<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>


<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]


<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }


<returning clause> ::=
    { RETURN | RETURNING } [ NEW | OLD ] { * | { <value expression> [ [AS] alias_name] } [, ...] }

Invocation and Access Rules

The user should satisfy the following conditions to perform <update returning query statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be updated.

[ AS alias_name ]

It is the alias of table_name.

<set clause>

It defines the columns to be updated and its values to be assigned. The number of columns in <set clause> should be as same as the number of values.
For more information, refer to UPDATE.

WHERE <search condition>

It updates the rows which satisfy WHERE condition.
If WHERE condition is not specified, all rows are updated.
For more information about WHERE condition, refer to where clause of SELECT.

<result offset clause>

It specifies the number of rows to skip from the query result.
For more information, refer to <result offset clause> of SELECT.

<fetch limit clause>

It specifies the number of rows to fetch in two ways, which are <fetch first clause> and <limit clause>.

<returning clause>

It defines the updated rows as a result set, and specifies columns to be retrieved from the result set.

Description

For more information, refer to Differences among UPDATE-related Statements.

Examples

The following is an example of obtaining values of the updated rows by using RETURNING clause.

gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01
       WHERE l_returnflag = 'R'
   RETURNING l_orderkey, l_linenumber, l_discount;

L_ORDERKEY L_LINENUMBER L_DISCOUNT
---------- ------------ ----------
         8            1        .07
         9            2        .11
        12            5        .05
        15            1        .03
        16            2        .08

5 rows updated.

The following is an example of obtaining values before the update for the updated rows by using RETURNING OLD clause.

gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01
       WHERE l_returnflag = 'R'
   RETURNING OLD l_orderkey, l_linenumber, l_discount;

L_ORDERKEY L_LINENUMBER L_DISCOUNT
---------- ------------ ----------
         8            1        .06
         9            2         .1
        12            5        .04
        15            1        .02
        16            2        .07

5 rows updated.

Compatibility

The SQL standard does not define <update returning query statement>.

UPDATE name RETURNING .. INTO

Function

It updates a single row of a table, and the updated value is obtained into the host variable.

Syntax

<update statement: searched> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        <returning into clause>
    ;


<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )


<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]


<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>


<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]


<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }


<returning into clause> ::=
    { RETURN | RETURNING } [ NEW | OLD ] { * | { <value expression> [ [AS] alias_name] } [, ...] } INTO variable_name [, ...]

Invocation and Access Rules

The user should satisfy the following conditions to perform <update returning query statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be updated.

[ AS alias_name ]

It is the alias of table_name.

<set clause>

It defines the columns to be updated and its values to be assigned. The number of columns in <set clause> should be as same as the number of values.
For more information, refer to UPDATE.

WHERE <search condition>

It updates the rows which satisfy WHERE condition.
If WHERE condition is not specified, all rows are updated.
For more information about WHERE condition, refer to where clause of SELECT.

<result offset clause>

It specifies the number of rows to skip in the query result.
For more information, refer to <result offset clause> of SELECT.

<fetch limit clause>

It specifies the number of rows to fetch in two ways, which are <fetch first clause> and <limit clause>.

RETURNING .. AS ..

It defines the updated rows as a result set, and specifies columns to be retrieved from the result set.
For more information, refer to <returning clause> of UPDATE name RETURNING.

INTO variable_name [, ...]

The number of variables specified in INTO clause should be equal to the number of the expressions specified in RETURNING clause.
The row to be updated should be one or less. If two or more rows are updated, an error occurs.

Description

For more information, refer to Differences among UPDATE-related Statements.

Example

The following is an example of obtaining column values of the updated rows into the host variables.
• Declare the host variable.
gSQL> \VAR v_discount NUMBER

gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01
       WHERE l_orderkey = 12 AND l_linenumber = 5
   RETURNING l_discount INTO :v_discount;

V_DISCOUNT
----------
       .05

1 row updated.

Compatibility

In the SQL standard, <update returning into statement> statement does not exist.

UPDATE name WHERE CURRENT OF cursor_name

Function

It updates a single row which the current cursor indicates.

Syntax

<update statement: positioned> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        WHERE CURRENT OF cursor_name
    ;

Invocation and Access Rules

The user should satisfy the following conditions to perform <update statement: positioned>.

Syntax Rules and Parameters

table_name

It is the name of a table whose rows are to be updated.

[ AS alias_name ]

It is the alias of table_name.

<set clause>

It defines the columns to be updated and its values to be assigned. The number of columns in <set clause> should be as same as the number of values.
For more information, refer to UPDATE.

cursor_name

The cursor corresponding to cursor_name should satisfy the following conditions.

Description

For more information, refer to Differences among UPDATE-related Statements.

Examples

The following is an example that <update statement: positioned> is performed in interactive SQL (gsql) using the cursor.

gSQL> \VAR v_discount NUMBER
gSQL> DECLARE update_cursor CURSOR FOR 
        SELECT l_discount
          FROM lineitem
         WHERE l_orderkey = 8 AND l_linenumber = 1
           FOR UPDATE;

Cursor declared.
gSQL> OPEN update_cursor;

Cursor is open.
gSQL> FETCH update_cursor INTO :v_discount;

V_DISCOUNT
----------
       .06

1 row fetched.
gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01 
       WHERE CURRENT OF update_cursor;

1 row updated.
gSQL> CLOSE update_cursor;

Cursor closed.

gSQL> COMMIT;

Commit complete.

The following is an example of performing <update statement: positioned> by using the cursor in embedded SQL program.

{
    ...
    EXEC SQL BEGIN DECLARE SECTION;
        ...    
        double v_discount;  
        ...   
    EXEC SQL END DECLARE SECTION;
    ...
    EXEC SQL DECLARE update_cursor CURSOR FOR
              SELECT l_discount
                FROM lineitem
               WHERE l_orderkey = 8 AND l_linenumber = 1
                 FOR UPDATE;
    ...
    EXEC SQL OPEN update_cursor;
    ...
    EXEC SQL FETCH NEXT update_cursor INTO :v_discount;
    ...
    EXEC SQL UPDATE lineitem 
                SET l_discount = l_discount + 0.01 
              WHERE CURRENT OF update_cursor;
    ...
    EXEC SQL CLOSE update_cursor;
    ...
    EXEC SQL COMMIT WORK;
    ...
}

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F831

Full cursor update

O

B031

Basic dynamic SQL

O

For More Information

Refer to CLOSE cursor_name.