SELECT
Description
SELECT retrieves data from tables or views.
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 granted the SELECT permission on the table, and users granted the SELECT ANY TABLE permission can read data in the table or view. System administrators have the permission to read data in the table or view by default when separation of duties is disabled.
- You must have the SELECT permission on all columns in the SELECT statement.
- The UPDATE permission is required for the use of FOR UPDATE or FOR SHARE.
Syntax
Query data.
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */]
[ALL | DISTINCT | DISTINCTROW]
[STRAIGHT_JOIN]
[SQL_NO_CACHE]
[SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
[FROM from_item [,...]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position} [WITH ROLLUP]]
[HAVING condition]
[WINDOW {window_name AS (window_definition)} [, ...]]
[ORDER BY {col_name | expression | position}
[ASC | DESC][ NULLS { FIRST | LAST } ], ...]
[[LIMIT {[offset,] row_count | row_count OFFSET offset}] | FETCH {FIRST | NEXT} [(expr)] {ROW| ROWS} ONLY]
[into_option]
[ FOR READ ONLY |
[ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT N | SKIP LOCKED ]} [...] ] |
LOCK IN SHARE MODE]
select_expr:
expression [ [AS] output_name ]
where_condition:
expr
into_option:
INTO {
[[[LOCAL | GLOBAL] TEMPORARY] [TABLE] table_name] |
OUTFILE file_name [CHARACTER SET encoding_name] [FIELDS fields_items] [LINES lines_items] |
DUMPFILE file_name
}
SELECT ... UNION/EXCEPT:
SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
SELECT ...
EXCEPT[ALL | DISTINCT] SELECT ...
[EXCEPT[ALL | DISTINCT] SELECT ...]
Restrictions on using aliases in the SELECT clause in the WHERE, HAVING, and GROUP BY clauses are as follows:
- Table creation statements are initialized.
m_db=# DROP TABLE IF EXISTS t0,t1; m_db=# CREATE TABLE t0 (c0 INT, c1 INT); m_db=# CREATE TABLE t1 (c0 INT, c1 INT);
- Reference only within the same level.
m_db=# SELECT r FROM t0 WHERE (select c0 as r from t0) = 1; -- error
- Reference only the aliases in the target list.
m_db=# SELECT * FROM t0 WHERE (SELECT c0 AS r FROM t0) = 1 AND r = 2; -- error
- Reference a prior expression in a subsequent expression.
m_db=# SELECT r AS d, c0 AS r FROM t0; -- error
- The volatile function cannot be used.
m_db=# SELECT uuid() AS r FROM t0 WHERE r = 1; -- error
- The Window function cannot be used.
m_db=# SELECT pg_catalog.rank() over(PARTITION BY c0 ORDER BY c1) AS r FROM t0 WHERE r = 1; -- error
- Aliases cannot be referenced in the condition of JOIN ON.
m_db=# SELECT uuid() AS r FROM t0 JOIN t1 ON r = 1; -- error
- An error is reported if the target list contains multiple aliases to be referenced.
m_db=# SELECT c0 AS r AS u AS v FROM t0; -- error
- The subquery with_query is as follows:
with_query_name [ ( column_name [, ...] ) ] AS ( {select} ) - The INTO clause is as follows:
into_option: { INTO [[[LOCAL | GLOBAL] TEMPORARY] [TABLE]] table_name | INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' } export_options: [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] } - The specified query source from_item is as follows:
{ table_name [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] |( 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 [, ...] ) |joined_table
table_name contains the virtual table dual. An M-compatible database allows you to specify dual as the virtual table name when no table is referenced.
- joined_table is as follows:
joined_table: { table_reference [INNER | CROSS] JOIN table_factor [join_specification] | table_reference STRAIGHT_JOIN table_factor [join_specification] | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor } join_specification: { ON join_condition | USING (join_column_list) } join_column_list: column_name [, column_name] ... - The GROUP clause is as follows:
| expression | ( expression [, ...] )
- The specified partition partition_clause in from_item is as follows:
PARTITION { ( { partition_name | subpartition_name } [, ...] ) | FOR ( partition_value [, ...] ) } | SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] )}
- Specifying partitions applies only to partitioned tables.
- If PARTITION specifies multiple partition names, level-1 and level-2 partition names can coexist and can be the same. The partition range is the union set of the partitions.
Parameters
- plan_hint
Follows the SELECT keyword in the /*+ */ format. It is used to optimize the plan of a SELECT statement block based on hints. In each statement, only the first /*+ plan_hint */ comment block takes effect as a hint. Multiple hints can be written.
- WITH [ RECURSIVE ] with_query [, ...]
Specifies one or more subqueries that can be referenced by name in the main query, which are equivalent to temporary tables. 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 for with_query is as follows:
with_query_name [ ( column_name [, ...] ) ] AS ( {select} )- 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 names displayed in the subquery result set.
- You can use SELECT in the subqueries.
- RECURSIVE can appear only after WITH. If there are multiple CTEs, you only need to specify RECURSIVE at the first CTE.
- When RECURSIVE is used, the data types of the subquery results on both sides of UNION ALL and EXCEPT ALL or UNION [DISTINCT] and EXCEPT [DISTINCT] in the CTE subquery must be converted to the same data type using the CAST function. In addition, the precision and collation of the subquery results on both sides must be the same. For example, WITH RECURSIVE cte (n) AS ( SELECT cast(id as signed int) from table_1 UNION ALL SELECT cast((n + 1) as signed int) FROM cte WHERE n < 5 ) SELECT * FROM cte. For details about the type conversion generated by operators, see Specification Constraints of Logical Operators, Specification Constraints of Bitwise Operators, and Specification Constraints of Arithmetic Operators.
- ALL
Specifies that all rows that meet the conditions are returned. The keyword can be omitted because all rows that meet the conditions are returned by default.
- DISTINCT | DISTINCTROW
Removes all duplicate rows from the result set of the SELECT statement so that each row in the result set is unique.
The DISTINCT|DISTINCTROW expression is interpreted using the same rules as ORDER BY. Unless you use ORDER BY to guarantee that the required row appears first, you cannot know what the first row is.
- STRAIGHT_JOIN
For inner joins, you can change the optimizer's execution order of join table queries so that the left table is always read before the right table.
- SQL_CALC_FOUND_ROWS
When you use the found_rows function to calculate the number of rows in a set, ignore any LIMIT clause.
- SELECT list
Specifies the column names in the queried table. It can be the names of some or all of the columns. To specify all columns, use the wildcard (*).
You may use the AS output_name clause to assign an alias to the output column. The alias is usually used for displaying the output column. The keywords NAME, VALUE, and TYPE can be used as column aliases.
Column names can be:
- Manually input column names, where multiple names are separated by commas (,).
- Columns computed by the FROM clause.
- INTO clause
Exports the result of SELECT to a specified user-defined variable or file.
- OUTFILE
- CHARACTER SET specifies the encoding format.
- FIELDS specifies the attribute of each column.
TERMINATED specifies the separator.
[OPTIONALLY] ENCLOSED specifies the quotation mark. When OPTIONALLY is specified, it has effect on only the string data type.
ESCAPED specifies the escape character.
- LINES specifies the row attribute.
STARTING specifies the beginning of the row.
TERMINATED specifies the end of the row.
- DUMPFILE
Exports to a file a single row of data without separators or newline characters.
- file_name
Specifies the absolute path of a file.
Export to a file. m_db=# SELECT * FROM t; a | b ---+--- 1 | a (1 row) -- Export data to a file using OUTFILE. m_db=# SELECT * FROM t INTO OUTFILE '/home/gaussdb/t.txt' FIELDS TERMINATED BY '~' ENCLOSED BY 't' ESCAPED BY '^' LINES STARTING BY '$' TERMINATED BY '&\n'; File content: $t1t~tat&, where LINES STARTING BY($),FIELDS TERMINATED BY(~),ENCLOSED BY(t),LINES TERMINATED BY(&\n) -- Export data to a file using DUMPFILE. m_db=# SELECT * FROM t INTO DUMPFILE '/home/gaussdb/t.txt'; File content: 1a
- OUTFILE
- FROM clause
Specifies one or more source tables for SELECT.
The FROM clause contains 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.
- alias
Assigns a temporary alias to a table or a complex table reference so that the reference of the table by other queries is easier.
An alias is used for abbreviation or eliminating ambiguity in self-joins. If an alias is assigned, it completely hides the actual name of the table.
- column_alias
- PARTITION
Queries data in the specified partition in a partitioned table.
- partition_name
- partition_value
Specifies the partition key value. If a partitioned table has multiple partition keys, you can use PARTITION FOR to uniquely identify a partition.
- SUBPARTITION
Queries data in the specified level-2 partition in a partitioned table.
- subpartition_name
- subpartition_value
Specifies the key values of the level-1 and level-2 partitions. The values of the two partition keys specified by the SUBPARTITION FOR clause can be used to uniquely identify a level-2 partition.
- subquery
Performs a subquery in the FROM clause. A temporary table is created to save the output of the subquery.
- with_query_name
The WITH clause can also be the source of the FROM clause and can be referenced by the name of the WITH query.
- function_name
Specifies the function name. Functions can be used in a FROM clause.
- The types of JOIN 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.
In any case, JOIN binds more tightly than FROM items that are separated by commas.
- LEFT [ OUTER ] JOIN
Returns all rows that meet the join conditions in the Cartesian product, as well as the rows in the left table that do not match the right table rows according to join conditions. Therefore, the rows on the left represent the full length of the joined table, where the corresponding columns in the right table are filled with NULL. Note that the condition of the JOIN clause is used only when matches are being calculated. The condition of the outer layer is applied after the calculation is complete.
- RIGHT [ OUTER ] JOIN
Returns all result rows of INNER JOIN, as well as all rows on the right that do not have matches (rows on the left are filled with NULL).
This is just a notational convenience because you can always convert it to a LEFT OUTER JOIN by switching the left and right inputs.
- CROSS JOIN
CROSS JOIN is equivalent to INNER JOIN ON(TRUE), that is, rows that are not deleted by the conditions. This join type has the same effect as FROM and WHERE, and is more convenient in symbol usage.
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 [, ...]).
- STRAIGHT_JOIN
The function is similar to that of INNER JOIN. In the STRAIGHT_JOIN scenario, the left table is always read before the right table. This changes the optimizer's execution sequence of join table queries.
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.
You can use multiple JOIN clauses consecutively.
- [ INNER ] JOIN
- ON join_condition
Specifies the join condition, defining which rows match. 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 Boolean values (non-zero values are implicitly converted to true and 0 is implicitly converted to false), which may cause unexpected results.
- USING(join_column[, ...])
Abbreviation for ON left_table.a = right_table.a AND left_table.b = right_table.b ... Corresponding columns must have the same name.
- NATURAL
A shorthand for a USING list that contains all columns in the two tables that have the same names.
- from item
Specifies the name of the query source object used for the join.
- table_name
- 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 Boolean values (non-zero values are implicitly converted to true and 0 is implicitly converted to false), which may cause unexpected results.
When using a LIKE operator in a WHERE clause, add a slash (\) before each special character if any of the following is queried in LIKE: %_\
- GROUP BY clause
Groups the query results based on the values of one or more columns. The query results with the same value are in the same group.
WITH ROLLUP :
To collect statistics on grouped query results, add the WITH ROLLUP statement at the end of the GROUP BY statement. WITH ROLLUP can also be used to collect statistics on multi-column group query results.
If sql_mode contains the ONLY_FULL_GROUP_BY option:
- If the non-aggregate function column in the SELECT list is consistent with the GROUP BY column, no error is reported.
- If the non-aggregate function column in the SELECT list is inconsistent with the GROUP BY column, the following two cases exist:
1. If the GROUP BY list contains a primary key or a unique non-null key, the SQL statement does not report an error.
2. All non-aggregate function columns in the SELECT list are displayed in the GROUP BY or WHERE list. In addition, the column in the WHERE clause must be equal to a constant. No error is reported.
- If both the JOIN and GROUP BY clauses are used:
In the ON clause of the syntax, if the condition is to check whether two columns are equal, for example, t1.col1=t2.col2, the GROUP BY clause does not contain the right join table column and no error is reported; t1 LEFT JOIN t2, the GROUP BY clause contains only t1.col1.
(2) RIGHT JOIN
In the ON clause of the syntax, if the condition is to check whether two columns are equal, for example, t1.col1=t2.col2, the GROUP BY clause does not contain the left join table column and no error is reported; t1 RIGHT JOIN t2, the GROUP BY clause contains only t2.col2.
(3) INNER JOIN/CROSS JOIN/STRAIGHT_JOIN
In the ON clause of the syntax, if the condition is to check whether two columns are equal, for example, t1.col1=t2.col2, the GROUP BY clause can contain any column and no error is reported.
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 the table to be queried is empty, a blank row is displayed when the WITH ROLLUP statement is executed.
- HAVING clause
Used together with the GROUP BY clause to select special groups. The HAVING clause compares some attributes of groups with a constant. Only groups that match the logical expression in the HAVING clause are extracted. HAVING can only reference columns in the GROUP BY clause or columns used in aggregate functions.
- 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 ] [ 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. The formats of frame_clause are as follows:
[ RANGE | ROWS ] frame_start [ RANGE | ROWS ] BETWEEN frame_start AND frame_end
The formats of frame_start and frame_end are as follows:
UNBOUNDED PRECEDING VALUE PRECEDING CURRENT ROW VALUE FOLLOWING UNBOUNDED FOLLOWING
Restrictions on scenarios where VALUE is used:
- The value of VALUE must be a non-negative number.
- VALUE PRECEDING/FOLLOWING can be used only with ROWS, but not with RANGE.
- UNION clause
Computes the set union of the rows returned by multiple SELECT statements.
The UNION clause has the following constraints:
- The default result of UNION does not contain any duplicate rows unless the ALL clause is specified.
- UNION operators in the same SELECT statement are evaluated from left to right unless specified by parentheses.
- FOR UPDATE and FOR SHARE cannot be specified in the result or input of UNION.
- The GUC parameter enable_union_all_order determines whether to preserve the order of UNION ALL subqueries.
- Enabling: set enable_union_all_order to on
- Disabling: set enable_union_all_order to off
- Querying: show enable_union_all_order
When the enable_union_all_order parameter is enabled, subquery order preserving is supported if UNION is set to ALL and the main query is not sorted. In other cases, subquery order preserving is not supported.
General expression:
select_statement UNION [ALL] select_statement
- select_statement can be any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE, or FOR SHARE clause.
- ORDER BY and LIMIT can be attached to subexpressions if enclosed in parentheses.
- EXCEPT clause
Removes the intersection of the two query results from the first query result.
The EXCEPT clause has the following restrictions:
- The query result is not deduplicated only when the ALL clause is specified. The default EXCEPT statement deduplicates the query result.
- If a SELECT statement contains multiple EXCEPT operators, the calculation logic is calculated from left to right. The calculation logic queries data based on the priority only when parentheses are used in statements.
- The FOR UPDATE or FOR SHARE operation cannot be specified in the query result or input of EXCEPT.
General expression:
select_statement EXCEPT [ALL | DISTINCT] select_statement
- select_statement can be any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE, or FOR SHARE clause.
- If select_statement contains an ORDER BY or LIMIT clause, add parentheses before and after select_statement.
- ORDER BY clause
Sorts data retrieved by SELECT in ascending or descending order. If the ORDER BY expression contains multiple columns:
- The values in the leftmost column are sorted first. If the values in this column are the same, they are then compared based on the next expression until the comparison is complete.
- If the values are equal for all specified expressions, they are returned in a random 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.
- The columns to be sorted by the ORDER BY clause where the GROUP BY clause is also used must be included in the columns grouped by GROUP BY.
If the SELECT result set contains aggregate functions, the ORDER BY clause, not used together with GROUP BY, takes effect only for the columns where the functions that return the set are located.
m_db=# CREATE TABLE t1(c1 int, c2 int); m_db=# EXPLAIN SELECT sum(c1) c FROM t1 ORDER BY c, c2; -- Ignores ORDER BY. m_db=# EXPLAIN SELECT sum(c1) c, generate_series (1,5) s FROM t1 ORDER BY c, c2; -- Ignores ORDER BY. The m_db=# EXPLAIN SELECT sum(c1) c, generate_series (1,5) s FROM t1 ORDER BY c, s; -- Sorts the s column in the result.
- NULLS FIRST
Specifies that null values appear before non-null values in the sort ordering. This is the default when DESC is specified.
- NULLS LAST
Specifies that null values appear after non-null values in the sort ordering. This is the default when DESC is not specified.
To support Chinese Pinyin sorting, you need to specify the gb18030 and GBK character sets when initializing the M-compatible database. For details, see Database-level Character Sets and Collations. In addition, you need to specify the gb18030_chinese_ci and gbk_chinese_ci collations during comparison.
- LIMIT clause
Consists of two independent 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.
LIMIT start count specifies the maximum number of rows to be returned, while start specifies the number of rows to skip before starting to return rows. When both are specified, start rows are skipped before count rows to be returned start to be counted.
ROWNUM cannot be used as count or offset in the LIMIT clause.
- FETCH {FIRST | NEXT} [(expr)] {ROW| ROWS} ONLY
If count is omitted in a FETCH clause, it defaults to 1, indicating that the clause specifies the total number of rows counting from the first row of the returned query result.
This attribute is not supported when the version compatibility control parameter m_format_dev_version of an M-compatible database is set to 's1' or later.
- OFFSET clause
SQL: A different syntax was proposed in 2008.
OFFSET start { ROW | ROWS }
start specifies the number of rows to skip before starting to return rows.
- Locking 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 to perform UPDATE, DELETE, SELECT FOR UPDATE, or SELECT FOR SHARE on these rows will be blocked until the current transaction ends. Any DELETE on the same row will also acquire the FOR UPDATE lock mode, as will UPDATE that modifies values on the primary key column. Conversely, SELECT FOR UPDATE will wait for concurrent transactions that are running the preceding commands on the same row, and will continue to lock and return the updated rows (there may be no row because the rows may be deleted).
FOR SHARE behaves similarly, except that it requires a shared lock rather than an exclusive lock on each retrieved row. A shared lock prevents other transactions from performing UPDATE, DELETE, or SELECT FOR UPDATE on the rows, but does not prevent them from performing SELECT FOR SHARE.
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. 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 an integer ranging from 0 to 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, rows that are locked with preceding locks are not skipped though SKIP LOCKED is specified.
If table names are specified in a locking clause, then only those tables will be locked. Other tables used in SELECT will not be locked. Otherwise, all tables used in the command will be locked.
If a locking clause is applied to a view or subquery, it will lock all tables used in the view or subquery.
Multiple locking clauses can be used to specify different lock modes for different tables.
If a table appears (or implicitly appears) in multiple clauses, the strongest lock is used. Similarly, if there is NOWAIT in any of the clauses that affect a table, then NOWAIT will apply to the table.
- Only FOR SHARE/FOR UPDATE/FOR READ ONLY/LOCK IN SHARE MODE can be used in querying the Ustore table.
- For the FOR UPDATE or FOR SHARE statements whose subquery is a stream plan, the same row that is locked cannot be concurrently updated.
- SELECT FOR UPDATE and SELECT FOR SHARE cannot be used together with UNION, EXCEPT, DISTINCT, GROUP BY, or HAVING.
- PARTITION clause
Queries data in the corresponding partition in a partitioned table.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot