On this page

Show all

Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ SQL Reference/ SQL Syntax/ S/ SELECT

SELECT

Updated on 2025-05-29 GMT+08:00

View PDF

Description

SELECT retrieves data from a table or view.

Serving as an overlaid filter on database tables, the SELECT statement uses SQL keywords to filter data tables and extract the required data.

Precautions

The owner of the table, users with the SELECT permission on the table, and users with the SELECT ANY TABLE permission can read data in the table or view. When the separation of duties is disabled, system administrators have this permission by default.
SELECT can join ordinary tables, but cannot join ordinary and GDS foreign tables. That is, the SELECT statement cannot contain both an ordinary table and a GDS foreign table.
You must have the SELECT permission on each field used in the SELECT statement.

Using FOR UPDATE or FOR SHARE requires the UPDATE permission in addition to the SELECT permission.

Syntax

Query data.

    
     
       
       [ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    { * | {expression [ [ AS ] output_name ]} [, ...] }
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ [ START WITH condition ] CONNECT BY [NOCYCLE] 
    condition [ ORDER SIBLINGS BY expression ] ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW {window_name AS ( window_definition )} [, ...] ]
    [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
    [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
    [ LIMIT { [offset,] count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT n | SKIP LOCKED ]} [...] ];

      

    
   

NOTE:

In condition and expression, you can use the aliases of expressions in targetlist in compliance with the following rules:

Reference only within the same level.
Only reference aliases in targetlist.
Reference a prior expression in a subsequent expression.
The volatile function cannot be used.
The Window function cannot be used.
Aliases cannot be referenced in the JOIN ON condition.
An error is reported if the target list contains multiple aliases to be referenced.

NOTICE:

In the scenario where the SELECT statement plan is cached, the WHERE IN candidate subset cannot be too large. It is recommended that the number of conditions be less than or equal to 100 to prevent high dynamic memory.

If the WHERE IN candidate subset is too large, the memory usage of the generated plan increases.
If the WHERE IN subsets constructed by concatenated SQL statements are different, the SQL template of the cache plan cannot be reused. A large number of different plans are generated, and the plans cannot be shared. As a result, a large amount of memory is occupied.

The subquery with_query is as follows:

      
         with_query_name [ ( column_name [, ...] ) ]
    AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )

The specified query source from_item is as follows:

      
         {[ ONLY ] table_name [ * ] [ slice_clasuse ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
[ TIMECAPSULE {TIMESTAMP | CSN} expression ]
|( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
|with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
|function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
|function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
|xmltable_clause
|from_item unpivot_clause
|from_item pivot_clause
|from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}

The GROUP BY clause is as follows:

      
         ( )
| expression
| ( expression [, ...] )
| ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
| CUBE ( { expression | ( expression [, ...] ) } [, ...] )
| GROUPING SETS ( grouping_element [, ...] )

The slice_clause for specifying partitions in from_item is as follows:
1

SLICE BY ( slice_name [, ...] )
NOTE:

Specifies that SLICE applies only to the range bucket table.

The partition_clause for specifying partitions in from_item is as follows:

1	PARTITION { ( partition_name [, ...] ) \| FOR ( partition_value [, ...] ) }

NOTE:

The specified partition applies only to partitioned tables.
If PARTITION specifies multiple partition names, the partition names can be the same. The union set of the partition ranges is used.

The sorting order nlssort_expression_clause is as follows:

1	NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M \| generic_m_ci } ' )

The second parameter can be generic_m_ci, which supports only the case-insensitive order for English characters.

Simplified query syntax, equivalent to select * from table_name.

1	TABLE { ONLY {(table_name)\| table_name} \| table_name [ * ]};

For xmltable_clause:

XMLTABLE(
xmlnamespaces_clause
row_expression
passing_clause
columns_clause
)

For xmlnamespaces_clause:

[
    XMLNAMESPACES(
    {string AS identifier }
    |
    { DEFAULT string }
    [, { string AS identifier } | { DEFAULT string } ]...
    ),
]

For passing_clause:

PASSING [BY { REF | VALUE }] document_expression [BY { REF | VALUE }]

For columns_clause:

[
    COLUMNS
    name { type [PATH column_expression] [DEFAULT default_expression] [NOT NULL | NULL] | FOR ORDINALITY }
    [, ...]
]

Parameters

WITH [ RECURSIVE ] with_query [, ...]
Specifies one or more subqueries that can be referenced by name in the main query, which is equivalent to a temporary table. This subquery statement structure is called the common table expression (CTE) structure. When this structure is used, the execution plan contains the CTE SCAN content.

If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name.

The detailed format of with_query is as follows: with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )
- with_query_name specifies the name of the result set generated by a subquery. Such names can be used to access the result sets of subqueries in a query.
- column_name specifies the column name displayed in the subquery result set.
- Each subquery can be a SELECT, VALUES, INSERT, UPDATE or DELETE statement.
- RECURSIVE can appear only after WITH. In the case of multiple CTEs, you only need to declare RECURSIVE at the first CTE.
- You can use MATERIALIZED or NOT MATERIALIZED to modify the CTE. Currently, only inline execution is supported for stream plans. In this case, this syntax does not take effect.
  - If MATERIALIZED is specified, the WITH query will be materialized, and a copy of the subquery result set is generated. The copy is directly queried at the reference point. Therefore, the WITH subquery cannot be jointly optimized with the SELECT statement trunk (for example, predicate pushdown and equivalence class transfer). In this scenario, you can use NOT MATERIALIZED for modification. If the WITH query can be executed as a subquery inline, the preceding optimization can be performed.
  - If the user does not explicitly declare the materialized attribute, comply with the following rules: If the CTE is referenced only once in the SELECT statement trunk to which it belongs and semantically supports inline execution, it will be rewritten as subquery inline execution. Otherwise, the materialized execution will be performed in CTE Scan mode.
plan_hint clause
Follows the SELECT keyword in the /*+<Plan hint> */ format. It is used to optimize the plan of a SELECT statement block. For details, see Hint-based Optimization. In each statement, only the first /*+ plan_hint */ comment block takes effect as a hint. Multiple hints can be written.
ALL
Specifies that all rows that meet the conditions are returned. This is the default behavior and can be omitted.
DISTINCT [ ON ( expression [, ...] ) ]
Removes all duplicate rows from the result set of the SELECT statement so that each row in the result set is unique.

Only the first row in the set of rows that have the same result calculated on the given expression ON ( expression [, ...] ) is retained.

NOTICE:

DISTINCT ON expression is explained with the same rule of ORDER BY. Unless you use ORDER BY to guarantee that the required row appears first, you cannot know what the first row is.
SELECT list
Specifies the name of a column in the table to be queried. The value can be a part of the column name or all of the column names. The wildcard (*) is used to represent the column name.

You may use the AS output_name clause to give an alias for an output column. The alias is used for the displaying of the output column. The name, value, and type keywords can be used as column aliases.

Column names can be expressed in the following formats:
- Manually input column names which are separated by commas (,)
- Columns computed in the FROM clause.

FROM clause

Specifies one or more source tables for SELECT.

The FROM clause can contain the following elements:

table_name
Specifies the name of a table or view. The schema name can be added before the table name or view name, for example, schema_name.table_name.

NOTE:

You can use database link to perform operations on remote tables and synonyms. For details, see DATABASE LINK.
alias
Gives a temporary alias to a table to facilitate the quotation by other queries.

An alias is used for brevity or to eliminate ambiguity for self-joins. If an alias is provided, it completely replaces the actual name of the table.

NOTICE:

If an alias is specified for the joined_table table created by JOIN and joined_table is wrapped with "()", that is, (joined_table), non-reserved keywords UNPIVOT and PIVOT cannot be used as aliases.
TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]
The TABLESAMPLE clause following table_name specifies that the specified sampling_method should be used to retrieve the subset of rows in the table.

The optional REPEATABLE clause specifies the number of seeds used to generate random numbers in the sampling method. The seed value can be any non-null constant value. If the table was not changed during the query, the two queries having the same seed and argument values will select the same sampling in this table. However, different seed values usually generate different samples. If REPEATABLE is not specified, a new random sample will be selected for each query based on the seed generated by the system.
TIMECAPSULE { TIMESTAMP | CSN } expression
Queries the table data of a specified CSN or at a specified time point.

Currently, the following tables do not support flashback query: system catalogs, DFS tables, global temporary tables, local temporary tables, unlogged tables, views, sequence tables, hash bucket tables, shared tables, and heritance tables.
- TIMECAPSULE TIMESTAMP
  Searches for the result set of a specified time point based on the date as the flashback query flag. date must be a valid past timestamp.
- TIMECAPSULE CSN
  Searches for the result set of a specified CSN based on the CSN flashback of the table as the flashback query flag. The CSN can be obtained from snpcsn recorded in gs_txn_snapshot.
- expression
  Constants, functions, or SQL expressions.
  NOTE:
  - A flashback query cannot span statements that affect the table structure or physical storage. Otherwise, an error is reported. Between the flashback point and the current point, if a statement (TRUNCATE, DDL, DCL, or VACUUM FULL) has been executed to modify the table structure or affect physical storage, the flashback fails. The error message "ERROR: The table definition of %s has been changed." is displayed when flashback is performed on a table where DDL operations have been performed. The error message "ERROR: recycle object %s desired does not exist" is displayed when flashback is performed on a table where DDL operations, such as changing namespaces and table names, have been performed.
  - Flashback query supports scanning PCR UB-tree indexes. If no such indexes, flashback query supports only seq scan for full table scan.
  - When the flashback point is too old, the source version cannot be obtained because the flashback version is recycled. As a result, the flashback fails and the error message "Restore point too old" is displayed.
  - The flashback point is specified by time. The maximum difference between the flashback point and the actual time is 3 seconds.
  - After truncating a table, perform a flashback query or flashback on the table. The error message "Snapshot too old" is displayed when a flashback is performed at a specified time point. Data cannot be found or the error message "Snapshot too old" is reported during the CSN-based flashback.
  - In the GTM-free scenario, each node uses the local CSN and does not have a globally unified CSN. Therefore, flashback in CSN mode is not supported.
column_alias
Specifies the column alias.
slice_name
Specifies the user-defined slice name in the range bucket table.
PARTITION
Queries data in the specified partition in a partitioned table.
partition_name
Specifies the name of a partition.
partition_value
Specifies the value of the specified partition key. If there are many partition keys, use the PARTITION FOR clause to specify the value of the only partition key you want to use.
subquery
Performs a subquery in the FROM clause. A temporary table is created to save subquery results.
with_query_name
Specifies that the WITH clause can also be used as the source of the FROM clause and can be referenced by the name of the WITH query.
function_name
Function name. Function calls can appear in the FROM clause.
join_type
The options are as follows:
- [ INNER ] JOIN
  A JOIN clause combines two FROM items. You can use parentheses to determine the order of nesting. In the absence of parentheses, JOIN nests left-to-right.
- LEFT [ OUTER ] JOIN
  Returns all rows that meet join conditions in the Cartesian product, plus those rows that do not match the right table rows in the left table by join conditions. This left-hand row is extended to the full width of the joined table by inserting NULL values for the right-hand columns. Note that only the JOIN clause's own condition is considered while the system decides which rows have matches. Outer conditions are applied afterward.
- RIGHT [ OUTER ] JOIN
  Returns all the joined rows, plus one row for each unmatched right-hand row (extended with NULL on the left).
  
  This is just a notational convenience, since you could convert it to a LEFT OUTER JOIN by switching the left and right inputs.
- FULL [ OUTER ] JOIN
  Returns all the joined rows, pluses one row for each unmatched left-hand row (extended with NULL on the right), and pluses one row for each unmatched right-hand row (extended with NULL on the left).
- CROSS JOIN
  Is equivalent to INNER JOIN ON (TRUE), which means no rows are removed by qualification. These join types are just a notational convenience, since they do nothing you could not do with plain FROM and WHERE.
  
  NOTE:
  
  You must specify a join condition for INNER JOIN and OUTER JOIN, that is, one of the following: NATURAL ON, join_condition, and USING (join_column [, ...]). For CROSS JOIN, none of these clauses can appear.
CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same result as you get from listing the two items at the top level of FROM.
ON join_condition
Defines which rows have matches in joins. Example: ON left_table.a = right_table.a You are advised not to use numeric types such as int for join_condition, because such types can be implicitly converted to bool values (non-zero values are implicitly converted to true and 0 is implicitly converted to false), which may cause unexpected results.
USING(join_column[, ...])
ON left_table.a = right_table.a AND left_table.b = right_table.b ... abbreviation. Corresponding columns must have the same name.
NATURAL
Is a shorthand for a USING list that mentions all columns in the two tables that have the same names.
from item
Specifies the name of the query source object connected.

xmltable_clause

Currently, xmltable_clause can be used only in ORA- or PG-compatible mode. xmltable_clause produces a virtual table based on data of the given XMLTYPE (in ORA-compatible mode) or XML (in PG-compatible mode) type.

xmlnamespaces_clause
The optional xmlnamespaces_clause is a clause that starts with XMLNAMESPACES followed by a comma-separated XML namespace declaration. string indicates the full name of the namespace for which single quotation marks are used as boundary characters. identifier indicates the namespace alias for which double quotation marks are used as boundary characters. The alias can be used in row_expression and columns_clause. In the current version, DEFAULT cannot be used to declare the default namespace.
row_expression
The required row_expression is an XPath 1.0 expression with English single quotation marks as boundary characters. The document_expression in the subsequent passing_clause is used as its context to obtain a group of XML nodes. These nodes are processed by columns_clause to generate each row of the virtual table.
passing_clause
The required passing_clause starts with PASSING, where document_expression is the data of the XMLTYPE (in ORA-compatible mode) or XML (in PG-compatible mode) type that is passed to XMLTABLE for processing. Currently, only single-root data is supported. In ORA-compatible mode, an error is reported regardless of whether BY VALUE or BY REF in the first or second place is used. In PG-compatible mode, although BY VALUE or BY REF in the first and second places can be identified and received, no functional processing is performed.
columns_clause
The optional columns_clause is used to specify the information required for the columns to be generated in the virtual table.

name: indicates the name of a column.

type: indicates the column type.

PATH: column_expression in the optional PATH part is an expression of XPath 1.0. A node in the node set obtained by row_expression is used as the context of the node, and the data required by the value of the corresponding column in the row data generated by the node is obtained through processing by the node. There is an implicit conversion when it is converted to a result of the TYPE type. If the PATH part is not provided, name is regarded as column_expression.

DEFAULT: default_expression in the optional DEFAULT part is an expression. If a NULL value is obtained after column_expression processing, the result obtained by calculating default_expression is used to generate the value of the column. Note that default_expression is calculated only when necessary. If the expression is stable, default_expression is calculated only once when necessary.

NOT NULL | NULL: In PG-compatible mode, the optional NOT NULL or NULL is used to indicate whether the column value can be NULL. If the column value is inconsistent with the specified option, an error is reported.

FOR ORDINALITY: indicates that the column is a row number column. Row numbers starting from 1 are filled for generated rows. Only one column can be marked as FOR ORDINALITY.

In ORA-compatible mode, columns_clause can be omitted. After columns_clause is omitted, the default columns_clause: COLUMNS column_value XMLTYPE PATH '.' is generated internally for subsequent processing. In PG-compatible mode, columns_clause cannot be omitted.

NOTICE:

Currently, xmltable_clause has the following restrictions:

The involved XPath expressions are XPath 1.0.
Currently, XPath expressions such as '..' cannot be used in column_expression.
When the data obtained through column_expression is converted to the data of the TYPE type, in ORA-compatible mode, if the obtained data is too long and exceeds the typmod of the type, the data will be truncated. Currently, long data is truncated only when the typmod of the CHAR, CHARACTER, NCHAR, BPCHAR, VARCHAR, CHARACTER VARYING, VARCHAR2 and NVARCHAR2 data types (including those attached with (n)) is greater than 0. In PG-compatible mode, an error is reported.
Currently, the maximum size of the input XMLTYPE (in ORA-compatible mode) or XML (in PG-compatible mode) type data is 1 GB.
For upgrade from a version that does not support XMLTABLE to a version that supports XMLTABLE, the XMLTABLE syntax cannot be used during the upgrade observation period.
The following expression is supported in XPath only in ORA-compatible mode: *:nodename, where nodename indicates the node name. This expression indicates that the namespace of the node specified by nodename is ignored when the node is selected.
The non-reserved keyword XMLTABLE cannot be used as the name of a function of the Functions as Table Sources type.

NOTE:

When non-constructed XMLTYPE (in ORA-compatible mode) data is entered, spaces and carriage return characters that are at the same level as the non-constructed part and are used to control the writing format between nodes are parsed as text nodes. For example, in the following text, the space and carriage return between the end label of node2 and the start label of node3 are parsed as text nodes, which is different from that in database ORA.

Input:

<root>
<node1>node1</node1>
malform
<node2>node2</node2>
<node3>node3</node3>
</root>

Actual situation:

<root><node1>node1</node1>
malform
<node2>node2</node2>
<node3>node3</node3>
</root>

Currently, the functions implemented by xmltable_clause are different from those implemented by databases ORA and PG. Pay attention to the differences. For details, see Table 1.

**Table 1** Comparison between GaussDB and databases ORA and PG
GaussDB	PG	ORA
XPath 1.0 expressions are used at row_expression and PATH in columns_clause.	Same as that in GaussDB.	XQuery 1.0 expressions are used at row_expression and PATH in columns_clause.
The default namespace function is not supported.	Same as that in GaussDB.	The default namespace function is supported.
A single data record can be passed through passing_clause, but the alias cannot be used.	Same as that in GaussDB. (Note that the input data is of the XML type.)	Multiple data records can be passed through the corresponding clause and aliases can be used.
The passing_clause cannot be omitted.	Same as that in GaussDB.	The corresponding clause can be omitted.
The RETURNING SEQUENCE BY REF clause cannot be used after passing_clause.	Same as that in GaussDB.	The RETURNING SEQUENCE BY REF clause can be used after the corresponding clause.
In columns_clause, you cannot use ( SEQUENCE ) BY REF to modify the returned data of the XMLType type.	Same as that in GaussDB.	You can use ( SEQUENCE ) BY REF in the corresponding clause to modify the returned data of the XML type.
If the PATH part in columns_clause is omitted and the column name is not enclosed using double quotation marks (""), the node can be correctly found for subsequent operations when the node name in the data passed to XMLTABLE is in lowercase.	Same as that in GaussDB.	If the corresponding part is omitted and the column name is not enclosed using double quotation marks (""), the node can be correctly found for subsequent operations when the node name in the data passed to XMLTABLE is in uppercase.
The declaration of the column type in columns_clause cannot be omitted.	Same as that in GaussDB.	The declaration of the column type in the corresponding clause can be omitted.
In ORA-compatible mode, the columns_clause can be omitted. In PG-compatible mode, the columns_clause cannot be omitted.	The corresponding clause cannot be omitted.	The corresponding clause can be omitted.
In ORA-compatible mode, if the returned data length exceeds the typmod of the type, the data is truncated. In PG-compatible mode, if the returned data length exceeds the typmod of the type, an error is reported.	An error is reported when the length of the returned data exceeds the typmod of the type.	If the length of the returned data exceeds the value of typmod, the data is truncated.

Example:

gaussdb=# CREATE DATABASE test DBCOMPATIBILITY = 'ORA';
gaussdb=# \c test
test=# SELECT * FROM XMLTABLE(
test(# XMLNAMESPACES('nspace1' AS "ns1", 'nspace2' AS "ns2"), -- Declare two XML namespaces 'nspace1' and 'nspace2' and the corresponding aliases "ns1" and "ns2".
test(# '/ns1:root/*:child' -- Select the root node whose namespace is 'nspace1' from the passed data through row_expression, select all child nodes under the root node, and ignore the child namespaces. ns1 is the alias of 'nspace1'.
test(# PASSING xmltype(
test(# '<root xmlns="nspace1">
test'#     <child>
test'#         <name>peter</name>
test'#         <age>11</age>
test'#     </child>
test'#     <child xmlns="nspace1">
test'#         <name>qiqi</name>
test'#         <age>12</age>
test'#     </child>
test'#     <child xmlns="nspace2">
test'#         <name>hacker</name>
test'#         <age>15</age>
test'#     </child>
test'# </root>')
test(# COLUMNS
test(# columns FOR ORDINALITY, -- Row number column
test(# name varchar(10) path 'ns1:name', -- Select the name node whose namespace is 'nspace1' from each child node obtained by row_expression, convert the value in the node to varchar(10), and return the value. ns1 is the alias of nspace1.
test(# age int); -- Select the age node from each child node obtained by row_expression, convert the value in the node to int, and return the value. The first child node does not explicitly specify the namespace. Therefore, the namespace of the parent node root is followed. Therefore, no value in this column is returned.
 column | name  | age 
--------+-------+-----
      1 | peter |    
      2 | qiqi  |    
      3 |       |    
(3 rows)

-- Switch to the default database and delete the test database. Change the database name as required.
gaussdb=# \c postgres 
gaussdb=# DROP DATABASE test;

unpivot_clause
Converts a column to a row. The syntax format is as follows:
```
 UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
    (   
        unpivot_col_clause
        unpivot_for_clause
        unpivot_in_clause
    )
```
- {INCLUDE | EXCLUDE} NULLS
  Controls whether the converted result contains NULL rows. INCLUDE NULLS indicates that the converted results contain NULL rows. EXCLUDE NULLS indicates that the converted results do not contain NULL rows. If this clause is ignored, the unpivot operation removes NULL rows from the converted results by default.
- unpivot_col_clause
```
unpivot_col_element 
```
  unpivot_col_element specifies the output column names. These columns store the values to be converted.
- unpivot_col_element
```
{ column | ( column [, column]... ) }
```
  unpivot_col_element has two forms: column and ( column [, column]... ).
- unpivot_for_clause
```
FOR { unpivot_col_element }
```
  unpivot_col_element contained in the unpivot_for_clause is used to specify the output column names. These columns store the alias and names of the column to be converted.
- unpivot_in_clause
```
IN ( unpivot_in_element [,unpivot_in_element...] )
```
  unpivot_in_clause specifies the columns to be converted. The column names and column values are saved in the previously specified output columns.
  unpivot_in_element
```
{ unpivot_col_element }[  AS { unpivot_alias_element } ]
```
  unpivot_col_element specifies the column to be converted. If ( column [, column]...) is used to specify the column to be converted, the column names are connected by underscores (_) and saved in the output columns. For example, IN ((col1, col2)) generates the column name col1_col2 and saves it in the output column specified by unpivot_for_clause. In addition, the AS keyword can be used to specify an alias for the column to be converted. Once an alias is specified, the alias is saved in the output column instead of the name of column to be converted.
- unpivot_alias_element
```
{ alias | ( alias [, alias]... )}
```
  Similar to unpivot_col_element, unpivot_alias_element has two forms. alias indicates the specified alias.
NOTICE:
Currently, unpivot_clause has the following restrictions:
- This parameter can be used only in ORA-compatible mode.
- The unpivot_clause clause cannot be used with hints.
- For unpivot_col_clause, the number of output columns specified by unpivot_col_element must be the same as that of unpivot_col_element contained in unpivot_in_clause.
- For unpivot_for_clause, the number of output columns specified by unpivot_col_element must be the same as the number of aliases specified by unpivot_alias_element contained in unpivot_in_clause.
- For unpivot_in_clause, the alias must be a constant or an expression that can be converted to a constant.
- For unpivot_in_clause, constant expressions support only IMMUTABLE functions.
- For all unpivot_col_element parameters contained in unpivot_in_clause, if the column types in the same position of these unpivot_col_element parameters are different, UNPIVOT attempts to convert the column types in order to convert the values of columns to be converted to a public type. Similarly, for all unpivot_alias_element parameters, if the alias types of these unpivot_alias_element parameters in the same position are different, UNPIVOT attempts to do the similar type conversion.
  For example, assume that there is an unpivot_in_clause in the form of IN (col1, col2), where col1 is of the int type and col2 is of the float type, UNPIVOT attempts to convert the column value of col1 to the public type float during the calculation.
pivot_clause
Converts a row into column. The syntax format is as follows:
```
PIVOT [ XML ]
( aggregate_function ( expr ) [[AS] alias ]
    [, aggregate_function ( expr ) [[AS] alias ] ]...
    pivot_for_clause
    pivot_in_clause
)
```
- aggregate_function ( expr ) [[AS] alias ]
  Aggregates calculation on a given expression. The calculation result is saved in the output column specified by pivot_in_clause. [AS] alias (the AS keyword can be omitted) can be used to specify an alias for aggregate_function. The alias is added to the end of output column name specified by pivot_in_clause in the format of "_alias".
- pivot_for_clause
```
FOR { column
    | ( column [, column]... )
    }
```
  Specifies the row to be converted. The column indicates a column of the row to be converted.
- pivot_in_clause
```
IN ( { { { expr
         | ( expr [, expr]... )
         } [ [AS] alias]
       }...
     }
  )
```
  Specifies the name of the output column. The column name can consist of one or more expressions, for example, (expr1, expr2). When a column name consists of multiple expressions, these expressions are connected by underscores (_) in sequence. That is, the output column name corresponding to (expr1, expr2) is "expr1_expr2". These expressions not only generate output column names, but also determine the time when the aggregate function is triggered. If the values of row to be converted is the same as the value of these expressions, the results calculated by aggregate_function are saved in the output column names that consist of these expressions. Assume that expr1 is "1" and expr2 is "2". For row "1 2", aggregate_function is used for calculation. For row "1 1", the calculation is not triggered.
NOTICE:
Currently, pivot_clause has the following restrictions:
- This parameter can be used only in ORA-compatible mode.
- The pivot_clause clause cannot be used with hints.
- If more than one aggregate_function is specified, at most one aggregate_function is allowed to have no alias, the rest of the aggregate_function functions are required to specify an alias.
- XML supports only syntax but does not support functions.
- The expression in pivot_in_clause can be a constant or an expression that can be converted to a constant. If the expression is not a unary expression, specify an alias for the expression.
- For pivot_in_clause, constant expressions support only IMMUTABLE functions.
- For the expression in pivot_in_clause, when the keyword AS is used to specify an alias for the expression, only the non-reserved keywords can be used as aliases.
- If the length of an output column name exceeds 63 characters, an error is reported.

WHERE clause
The WHERE clause forms an expression for row selection to narrow down the query range of SELECT. condition indicates any expression that returns a value of Boolean type. Rows that do not meet this condition will not be retrieved. You are advised not to use numeric types such as int for condition, because such types can be implicitly converted to bool values (non-zero values are implicitly converted to true and 0 is implicitly converted to false), which may cause unexpected results.

In the WHERE clause, you can use the operator (+) to convert a table join to an outer join. However, this method is not recommended because it is not the standard SQL syntax and may raise syntax compatibility issues during platform migration. There are many restrictions on using the operator (+):
1. It can appear only in the WHERE clause.
2. If a table join has been specified in the FROM clause, the operator (+) cannot be used in the WHERE clause.
3. The operator (+) can work only on columns of tables or views, instead of on expressions.
4. If table A and table B have multiple join conditions, the operator (+) must be specified in all the conditions. Otherwise, the operator (+) will not take effect, and the table join will be converted into an inner join without any prompt information.
5. Tables specified in a join condition where the operator (+) works cannot cross queries or subqueries. If tables where the operator (+) works are not in the FROM clause of the current query or subquery, an error will be reported. If a peer table for the operator (+) does not exist, no error will be reported and the table join will be converted into an inner join.
6. Expressions where the operator (+) is used cannot be directly connected through OR.
7. If a column where the operator (+) works is compared with a constant, the expression becomes a part of the JOIN condition.
8. A table cannot have multiple foreign tables.
9. The operator (+) can appear only in the following expressions: comparison, NOT, ANY, ALL, IN, NULLIF, IS DISTINCT FROM, and IS OF. It is not allowed in other types of expressions. In addition, these expressions cannot be connected through AND or OR.
10. The operator (+) can be used to convert a table join only to a left or right outer join, instead of a full join. That is, the operator (+) cannot be specified on both tables of an expression.
NOTICE:
- For the WHERE clause, if special character "%", "_", or "\" is queried in LIKE, add the slash "\" before each character.
- If an IN clause is added after a WHERE clause and no parenthesis is added after IN, only one element can be added after IN, and restrictions can be used only in ORA-compatible mode. The element has the following restrictions:
  - Constant: such as character strings, integers, and floating points
  - Column name
  - CASE WHEN clause
  - Function
START WITH clause
The START WITH clause is usually used together with the CONNECT BY clause and indicates the initial condition of recursion. Data is traversed recursively and hierarchically. If this clause is omitted and the CONNECT BY clause is used alone, all rows in the table are used as the initial set. For details, see CONNECT BY.
CONNECT BY clause
CONNECT BY indicates a recursive join condition. It is used together with START WITH to implement data traversal and recursion. Example:
```
gaussdb=#  CREATE TABLE test(name varchar, id int, fatherid int);
gaussdb=#  INSERT INTO test VALUES('A', 1, 0), ('B', 2, 1),('C',3,1),('D',4,1),('E',5,2);
gaussdb=#  SELECT * FROM TEST START WITH id = 1 CONNECT BY prior id = fatherid ORDER SIBLINGS BY id DESC;
 name | id | fatherid 
------+----+----------
 A    |  1 |        0
 D    |  4 |        1
 C    |  3 |        1
 B    |  2 |        1
 E    |  5 |        2
(5 rows)
```
In the CONNECT BY condition, the PRIOR keyword can be specified for a column to indicate that the column is recursive. If NOCYCLE is added before the recursive join condition, recursion stops when a loop record is encountered.

NOTICE:

A SELECT statement containing the START WITH .. CONNECT BY clause does not support the FOR SHARE or UPDATE lock.

The process of executing the START WITH statement is as follows:
1. The initial dataset is selected based on the condition in the START WITH clause. In the preceding example, ('A', 1, 0) is selected first. Then, this initial dataset is set as the working set.
2. If the working set is not empty, the data in the working set is used as the input for the next query. The filter criteria are specified in the CONNECT BY clause. The keyword PRIOR indicates the current record. For example, prior id = fatherid in the preceding example indicates that the ID of the current record is fatherid of the next record.
3. Set the data set filtered out in the previous step as the working set and repeat the previous step.
In addition, the database adds the following pseudocolumns to each selected data record so that users can learn about the location of the data in the recursive or tree structure:
- LEVEL: level of a node. The level of the root node is 1.
- CONNECT_BY_ISLEAF: specifies whether a node is a leaf node.
  NOTICE:
  connect_by_isleaf: If a node does not have any subnode, the node is a leaf node and connect_by_isleaf is set to 1. Otherwise, connect_by_isleaf is set to 0.
  
  Assume that table T1 exists and the data in table T1 is shown in Figure 1.
  Figure 1 Data structure
  
  Run the following statement:
  SELECT * FROM T1 CONNECT BY PRIOR B=A AND LEVEL<=3;
  The tree structure of the result is shown in Figure 2, where the blue nodes are leaf nodes.
  Figure 2 Logical structure of the execution
In addition to pseudocolumns, the following query functions are provided (for details, see Hierarchical Recursion Query Functions):
- sys_connect_by_path(col, separator): returns the connection path from the root node to the current row. The col parameter indicates the name of the column displayed in the path, and the separator parameter indicates the connector.
- connect_by_root(col): displays the top-level node of the node. col indicates the name of the output column.
If a loop exists in the dataset, the database provides loop detection. By default, if a loop is detected, an error is reported and no data is returned. In addition, the NOCYCLE keyword is provided. With it, the query can be executed normally and when the first duplicate data record is found, the query exits directly instead of reporting an error.

Besides, in the hierarchical query, the search is performed strictly according to the depth-first order. If ROWNUM is used as the filter condition in START WITH or CONNECT, the value of ROWNUM is increased by 1 for each record to be returned. Then, the record is verified based on ROWNUM conditions. Records that do not satisfy the conditions are discarded and the value of ROWNUM is decreased by 1.
NOTICE:
- The PRIOR keyword can be used only in the CONNECT BY clause instead of the START WITH clause.
- In addition to targetlist, "prior (single column)" is parsed as "prior single column". User-defined functions named prior are not perceived.
- The PRIOR keyword can be specified only for columns in the table instead of expressions, pseudocolumns, or type conversion. For example, PRIOR (a + 1) is not allowed.
- In the CONNECT BY clause, the column using the keyword PRIOR cannot be in the same condition with pseudocolumns such as level and rownum, but they can be in different conditions. For example, (PRIOR a = level) is not allowed, but (PRIOR a = b) and (level = 1) is allowed. Different conditions refer to the conditions connected by AND at the top of the CONNECT BY clause. For example, (PRIOR a = 1 or level = 1) is considered as a condition and is not allowed.
- In the START WITH and CONNECT BY clauses, pseudocolumns cannot be used for sublinks, for example, "rownum = (subquery)" or "rownum in (subquery)".
- You are advised not to use ROWNUM in the CONNECT BY clause. If ROWNUM is required, test it thoroughly to avoid unexpected results.
- When START WITH and CONNECT BY are called on the CTE defined by WITH AS, if there are multiple CTEs, ensure that the definition of each CTE does not depend on other CTEs.
- If no loop exists in the data but the error message "runs into cycle" is reported, increase the value of max_recursive_times.
- connect_by_isleaf, connect_by_iscycle and level are of the int type.
- In hierarchical queries, connect_by_isleaf, connect_by_iscycle, and level must be parsed as pseudocolumns and will not be parsed as columns whose alias names in projection columns are the same as pseudocolumns.
- You are advised not to reference other views when defining views with hierarchical queries.
- Optimization suggestions for the START WITH clause:
  - Create indexes based on the conditions in the CONNECT BY clause to improve the performance of the START WITH clause.
  - Identify bottlenecks based on the plan collected by running EXPLAIN PERFORMANCE. If the recursive operator (inner plan) of RECURSIVE UNION is the Hash JOIN operator, but the hash table is created for the temporary table tmp_result or the hash table in plan is materialized (that is, the batch size is greater than 1), the possible cause is that the value of work_mem is too small. As a result, the hash table cannot be created for the outer data table. You can increase the value of work_mem to improve performance.
    Note: GaussDB optimizes tables with a small volume of data and caches table results in hash tables to improve performance. In this case, indexes are not required. However, if the data volume exceeds the limit specified by work_mem, the optimization becomes invalid. In this case, you can create indexes for optimization.
- The keyword PRIOR can be used only in the CONNECT BY clause and cannot be used in the START WITH clause.
- The keyword PRIOR can be specified only for columns in the table instead of the expression and type conversion. For example, prior (a + 1) is not allowed.
- In the CONNECT BY clause, the column using the keyword PRIOR cannot in the same condition with pseudo columns such as level and rownum, but they can be in different conditions. For example, (prior a = level) is not allowed, but (prior a = b) and (level = 1) is allowed. Different conditions refer to the conditions connected by AND at the top of the CONNECT BY clause. For example, (prior a = 1 or level = 1) is considered as a condition and is not allowed.
- You are advised not to use pseudo columns in the START WITH statement. If pseudo columns need to be used, please test the columns to avoid inconsistency between the result and expected result.
- If no value or NULL is used in the START WITH statement, the initial recursive condition is empty and an empty result is returned.
- If no value is used in the CONNECT BY statement, the recursive condition is empty and all results that meet the condition of the START WITH clause are returned.
- Columns modified by PRIOR support only the following types: INT8, INT1, INT2, OID, INT4, BOOL, CHAR, NAME, FLOAT4, FLOAT8, ABSTIME, RELTIME, DATE, CASH, TIME, TIMESTAMP, TIMESTAMPTZ, SMALLDATETIME, UUID, INTERVAL, TIMETZ, INT2VECTOR, CLOB, NVARCHAR2, VARCHAR, TEXT, VECTOR, BPCHAR, RAW, BYTEA, NUMERIC, XID, CID, and TID. The whitelist restriction cannot be bypassed by forcibly converting the type to the preceding types, for example, PRIOR col::text (where col is not of the type in the whitelist) is not supported.
- In a hierarchical query statement, if the projection column utilizes a window function, the result depends on the order of the intermediate result set. To use a window function, conduct thorough testing to verify that the semantics align with expectations.
The default behavior of START WITH is width-first search. However, depth-first search can be implemented by working with pseudocolumns. Example:
```
gaussdb=# SELECT sys_connect_by_path(name,'-') as path, *, LEVEL FROM test START WITH id = 1 CONNECT BY fatherid=prior id ORDER BY path;
  path  | name | id | fatherid | level 
--------+------+----+----------+-------
 -A     | A    |  1 |        0 |     1
 -A-B   | B    |  2 |        1 |     2
 -A-B-E | E    |  5 |        2 |     3
 -A-C   | C    |  3 |        1 |     2
 -A-D   | D    |  4 |        1 |     2
gaussdb=# DROP TABLE test;
```
If a loop exists in the dataset, the database provides loop detection. By default, if a loop is detected, an error is reported and no data is returned. In addition, the NOCYCLE keyword is provided. With it, the query can be executed normally and when the first duplicate data record is found, the query exits directly instead of reporting an error.
ORDER SIBLINGS BY clause
The output of the START WITH statement is returned level by level. However, there is no sequence guarantee at each level because the database automatically selects the optimal execution path during each round of query. In the preceding example, A is output first, but the sequence of B, C, and D is not fixed. If you have requirements on the final output sequence, you can use ORDER SIBLINGS BY. The usage of ORDER SIBLINGS BY is the same as that of ORDER BY. ORDER SIBLINGS BY is used for sorting at each level during recursion.

NOTICE:

The expression following ORDER SIBLINGS BY only supports sorting by calling non-aggregate and window functions for ordinary columns, column name offsets, and column names. It does not support calling system functions related to START WITH for column names or using pseudocolumns related to START WITH.
GROUP BY clause
Condenses query results into a single row all selected rows that share the same values for the grouped expressions.
- CUBE ( { expression | ( expression [, ...] ) } [, ...] )
  A CUBE grouping is an extension to the GROUP BY clause that creates subtotals for all of the possible combinations of the given list of grouping columns (or expressions). In terms of multidimensional analysis, CUBE generates all the subtotals that could be calculated for a data cube with the specified dimensions. For example, given three expressions (n=3) in the CUBE clause, the operation results in 2ⁿ = 2³ = 8 groupings. Rows grouped on the values of n expressions are called regular rows, and the rest are called superaggregate rows.
- GROUPING SETS ( grouping_element [, ...] )
  Another extension to the GROUP BY clause. It allows users to specify multiple GROUP BY options. This improves efficiency by trimming away unnecessary data group. After you specify the required data group, the database does not need to compute a whole ROLLUP or CUBE.
NOTICE:
- If sql_mode contains the ONLY_FULL_GROUP_BY option, the following situations occur:
  - If the non-aggregate function column in the SELECT list is consistent with the column for GROUP BY, no error is reported.
  - If the non-aggregate function column in the SELECT list is inconsistent with the column for GROUP BY, the following situations may occur:
    
    If the GROUP BY list contains a primary key or a unique non-null key, the SQL statement does not report an error.
    
    Non-aggregate function columns in the SELECT list are also included in the GROUP BY or WHERE list. If included in the WHERE list, the columns in the WHERE clause must be in the form of a constant, and then no error will be reported.
    
    If both the JOIN and GROUP BY clauses are contained, the following situations may occur:
    
    LEFT JOIN
    In the syntax, when the ON clause is used for equality check between two columns, no error is reported if the GROUP BY clause contains columns from the LEFT JOIN table.
    
    For example, when LEFT JOIN operations are performed on tables t1 and t2, if the condition in the ON clause is t1.col1=t2.col2 ..., the GROUP BY clause only needs to contain t1.col1.
    
    RIGHT JOIN
    In the syntax, when the ON clause is used for equality check between two columns, no error is reported if the GROUP BY clause contains columns from the RIGHT JOIN table.
    
    For example, when RIGHT JOIN operations are performed on tables t1 and t2, if the condition in the ON clause is t1.col1=t2.col2 ..., the GROUP BY clause only needs to contain t2.col2.
    
    INNER JOIN/CROSS JOIN/STRAIGHT_JOIN
    In the syntax, when the ON clause is used for equality check between two columns, no error is reported if the GROUP BY clause contains either of the columns.
- If sql_mode does not contain the ONLY_FULL_GROUP_BY option, no restriction exists and no error is reported in the preceding error scenarios.
  - If SELECT contains both non-aggregate columns and aggregate functions, it must be used together with GROUP BY. Otherwise, an error is reported.
  - If a non-aggregate column beyond the column for GROUP BY contains multiple data records after it is grouped, due to differences in execution plans and DN data distribution, the queried column data is a random data record in the grouped data.
HAVING clause
Selects special groups by working with the GROUP BY clause. The HAVING clause compares some attributes of groups with a constant. Only groups that matching the logical expression in the HAVING clause are extracted.
WINDOW clause
The general format is WINDOW window_name AS ( window_definition ) [, ...]. window_name is a name can be referenced by window_definition. window_definition can be expressed in the following forms:
```
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
```
frame_clause defines a window frame for the window function. The window function (not all window functions) depends on window frame and window frame is a set of relevant rows of the current query row. frame_clause can be expressed in the following forms:
```
[ RANGE | ROWS ] frame_start
[ RANGE | ROWS ] BETWEEN frame_start AND frame_end
```
Formats of frame_start and frame_end:
```
UNBOUNDED PRECEDING
VALUE PRECEDING
CURRENT ROW
VALUE FOLLOWING
UNBOUNDED FOLLOWING
```
NOTE:
Restrictions on scenarios where VALUE is used:
- The table columns operated by the aggregate function or window function support only the INT1, INT2, INT4, INT8, FLOAT4, FLOAT8, NUMERIC, INTERVAL, TIME, TIMETZ, TIMESTAMP, TIMESTAMPTZ, and DATE types.
- The VALUE type supports only the INT1, INT2, INT4, INT8, INT16, FLOAT4, FLOAT8, NUMERIC, INTERVAL, and character string (only character strings that can be implicitly converted to numbers are supported) types.
- The value of VALUE must be a non-negative number.
- Currently, VALUE only supports constants as the upper and lower boundaries and does not support variables and expressions.
UNION clause
Computes the set union of the rows returned by the involved SELECT statements.

The UNION clause has the following constraints:
- By default, the result of UNION does not contain any duplicate rows unless the ALL clause is declared.
- The number of columns in multiple SELECT statements must be the same, and the data type and sequence of each column must be the same.
- Multiple UNION operators in the same SELECT statement are evaluated left to right, unless otherwise specified by parentheses.
- FOR UPDATE cannot be specified either for a UNION result or for any input of a UNION.
General expression:
```
select_statement UNION [ALL] select_statement
```
- select_statement can be any SELECT statement without an ORDER BY, LIMIT, or FOR UPDATE clause.
- ORDER BY and LIMIT can be attached to the subexpression if it is enclosed in parentheses.
NOTICE:
- It is recommended that the number of UNION ALL clauses be less than 100. If the number of UNION ALL clauses exceeds 100, ensure that the instance memory is sufficient.
- The following constraints apply when you perform set operations with NULL:
  - NULL can only be a projection column itself or a projection column in a subquery of the FROM statement.
  - In CTEs and recursive queries, complex subqueries (DISTINCT, GROUP BY, and ORDER BY statements) are not supported.
INTERSECT clause
Computes the set intersection of rows returned by the involved SELECT statements. The result of INTERSECT does not contain any duplicate rows.

The INTERSECT clause has the following constraints:
- Multiple INTERSECT operators in the same SELECT statement are evaluated left to right, unless otherwise specified by parentheses.
- Processing INTERSECT preferentially when UNION and INTERSECT operations are executed for results of multiple SELECT statements.
General format:
```
select_statement INTERSECT select_statement
```
select_statement can be any SELECT statement without a FOR UPDATE clause.
EXCEPT clause
Has the following common form:
```
select_statement EXCEPT [ ALL ] select_statement
```
select_statement can be any SELECT statement without a FOR UPDATE clause.

The EXCEPT operator computes the set of rows that are in the result of the left SELECT statement but not in the result of the right one.

The result of EXCEPT does not contain any duplicate rows unless the ALL clause is declared. To execute ALL, a row that has m duplicates in the left table and n duplicates in the right table will appear MAX(m–n, 0) times in the result set.

Multiple EXCEPT operators in the same SELECT statement are evaluated left to right, unless parentheses dictate otherwise. EXCEPT binds at the same level as UNION.

Currently, FOR UPDATE cannot be specified either for an EXCEPT result or for any input of an EXCEPT.
MINUS clause
Has the same function and syntax as EXCEPT clause.
ORDER BY clause
Sorts data retrieved by SELECT in descending or ascending order. If the ORDER BY expression contains multiple columns:
- If two columns are equal according to the leftmost expression, they are compared according to the next expression and so on.
- If they are equal according to all specified expressions, they are returned in an implementation-dependent order.
- When used with the DISTINCT keyword, the columns to be sorted in ORDER BY must be included in the columns of the result set retrieved by the SELECT statement.
- When used with the GROUP BY clause, the columns to be sorted in ORDER BY must be included in the columns of the result set retrieved by the SELECT statement.
When the clause is not used with the GROUP BY clause and the columns of the result set retrieved by the SELECT statement contain aggregate functions:
- The ORDER BY clause is ignored if the columns of the retrieved result set do not contain any set returning functions.
- Only the sorting of the columns that contain set returning functions is retained if the columns of the retrieved result set contain set returning functions.
NOTE:
To support Chinese pinyin order, set the encoding format to UTF-8, GB18030, GB18030_2022, GBK, or ZHS16GBK when initializing the database. The statements are as follows:
```
initdb –E UTF8 –D ../data –locale=zh_CN.UTF-8
initdb -E GB18030 -D ../data -locale=zh_CN.GB18030
initdb -E GB18030_2022 -D ../data -locale=zh_CN.GB18030
initdb –E GBK –D ../data –locale=zh_CN.GBK
initdb –E ZHS16GBK –D ../data –locale=zh_CN.GBK
```
LIMIT clause
Consists of two independent sub-clauses:

LIMIT { count | ALL } limits the number of rows to be returned. count specifies the number of rows to be returned. The effect of LIMIT ALL is the same as that of omitting the LIMIT clause.

OFFSET start count specifies the maximum number of rows to return, while start specifies the number of rows to skip before starting to return rows. When both are specified, start rows are skipped before starting to count the count rows to be returned.

ROWNUM cannot be used as count or offset in the LIMIT clause.
OFFSET clause
The SQL: 2008 standard has introduced a different clause:

OFFSET start { ROW | ROWS }

start specifies the number of rows to skip before starting to return rows.
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY
If count is omitted in a FETCH clause, it defaults to 1.
FOR UPDATE clause
The FOR UPDATE clause locks the rows retrieved by SELECT. This ensures that the rows cannot be modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows will be blocked until the current transaction ends.

To prevent the operation from waiting for the commit of other transactions, you can use NOWAIT. If the selected row cannot be locked immediately, an error is reported immediately when you execute SELECT FOR UPDATE NOWAIT. If you use WAIT n and the selected row cannot be locked immediately, the operation needs to wait for n seconds (the value of n is of the int type with a range of 0 ≤ n ≤ 2147483). If the lock is obtained within n seconds, the operation is performed normally. Otherwise, an error is reported. If you use SKIP LOCKED, locked rows are skipped when a table is locked. Only rows that are locked using a row lock can be skipped. In scenarios where locks do not block each other in different transactions, for example, SELECT FOR SHARE - SELECT FOR SHARE SKIP LOCKED, rows that are locked with preceding locks are not skipped though SKIP LOCKED is specified.

FOR SHARE behaves similarly, except that it acquires a shared rather than exclusive lock on each retrieved row. A share lock blocks other transaction from performing UPDATE, DELETE, or SELECT FOR UPDATE on these rows, but it does not prevent them from performing SELECT FOR SHARE.

If specified tables are named in FOR UPDATE or FOR SHARE, then only rows coming from those tables are locked. Any other tables used in SELECT are simply read as usual. Otherwise, locking all tables in the statement.

If FOR UPDATE or FOR SHARE is applied to a view or sub-query, it affects all tables used in the view or sub-query.

Multiple FOR UPDATE and FOR SHARE clauses can be written if it is necessary to specify different locking behaviors for different tables.

If the same table is mentioned (or implicitly affected) by both FOR UPDATE and FOR SHARE clauses, it is processed as FOR UPDATE. Similarly, a table is processed as NOWAIT if that is specified in any of the clauses affecting it.
NOTICE:
- For SQL statements containing FOR UPDATE or FOR SHARE, their execution plans will be pushed down to DNs. If the pushdown fails, an error will be reported.
- The query of row number contained in the projected column or WHERE conditions does not support FOR UPDATE/SHARE.
- For the FOR UPDATE/SHARE statements whose subquery is a stream plan, the same locked row cannot be concurrently updated.
- For the ORDER BY FOR UPDATE/SHARE statement, the execution sequence of the SORT and LOCK operators in the stream plan is different from that in other plans. In the stream plan, LOCK is executed before SORT. In other plans, SORT is executed before LOCK. The reason is that if the data in the stream plan is not on the current DN, the data needs to be redistributed and locked on the original DN. After redistribution, data becomes disordered. Therefore, the SORT operator needs to be added. If SORT is performed before LOCK, the original ordered data becomes disordered again. In this case, the SORT operator is meaningless and can be eliminated. The final plan execution sequence is changed from sort > lock > sort to lock > sort.
- In scenarios where FOR UPDATE/SHARE statements are concurrently executed, ORDER BY is used to sort data to avoid deadlocks. This method is not feasible for distributed systems because the DN lock sequence cannot be ensured by ORDER BY. In addition, adding ORDER BY causes performance overhead. Therefore, you are advised not to add ORDER BY to solve the deadlock problem.
- The DATABASE LINK function does not support the SKIP LOCKED syntax.
- In distributed scenarios, FOR UPDATE/SHARE cannot be performed on multiple tables.
NLS_SORT
Specifies that a column is sorted in a special order. Currently, only Chinese Pinyin and case-insensitive sorting are supported. To support this sorting mode, you need to set the encoding format to UTF8, GB18030, GB18030_2022, GBK, or ZHS16GBK when creating a database. If you set the encoding format to another format, for example, SQL_ASCII, an error may be reported or the sorting mode may be invalid.

Value range:
- SCHINESE_PINYIN_M, sorted by Pinyin order.
- generic_m_ci: sorted in case-insensitive order (optional; only English characters are supported in the case-insensitive order.)
PARTITION clause
Queries data in the specified partition in a partitioned table.

Parent topic: S

Previous topic: SECURITY LABEL ON

Next topic: Simple Queries

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot