--- title: "SELECT" url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/dml_select/" --- # SELECT Use the SELECT statement to retrieve rows of specified columns that meet a given condition from a table. ## Synopsis Use the `SELECT` statement to retrieve rows of specified columns that meet a given condition from a table. It specifies the columns to be retrieved, the name of the table, and the condition each selected row must satisfy. The same syntax rules govern a subquery, wherever you might use one—like, for example, in an [`INSERT` statement](/stable/api/ysql/the-sql-language/statements/dml_insert/ "INSERT statement"). Certain syntax spots, for example a `WHERE` clause predicate or the actual argument of a function like `sqrt()`, allow only a scalar subquery. ## Syntax - [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-2) - [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-2) ```output.ebnf select ::= [ with_clause ] SELECT select_list [ trailing_select_clauses ] with_clause ::= WITH [ RECURSIVE ] { common_table_expression [ , ... ] } select_list ::= [ ALL | DISTINCT [ ON { ( expression [ , ... ] ) } ] ] [ * | { { expression | fn_over_window | ordinary_aggregate_fn_invocation | within_group_aggregate_fn_invocation } [ [ AS ] name ] } [ , ... ] ] trailing_select_clauses ::= [ FROM { from_item [ , ... ] } ] [ WHERE boolean_expression ] [ GROUP BY { grouping_element [ , ... ] } ] [ HAVING boolean_expression ] [ WINDOW { { name AS window_definition } [ , ... ] } ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY { order_expr [ , ... ] } ] [ LIMIT { int_expression | ALL } ] [ OFFSET int_expression [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } int_expression { ROW | ROWS } ONLY ] [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [ , ... ] ] [ NOWAIT | SKIP LOCKED ] [ ... ] ] common_table_expression ::= cte_name [ ( column_name [ , ... ] ) ] AS [ [ NOT ] MATERIALIZED ] ( { select | values | insert | update | delete } ) fn_over_window ::= name ( [ expression [ , ... ] | * ] [ FILTER ( WHERE boolean_expression ) ] OVER { window_definition | name } ordinary_aggregate_fn_invocation ::= name ( { [ ALL | DISTINCT ] expression [ , ... ] | * } [ ORDER BY order_expr [ , ... ] ] ) [ FILTER ( WHERE boolean_expression ) ] within_group_aggregate_fn_invocation ::= name ( { expression [ , ... ] } ) WITHIN GROUP ( ORDER BY order_expr [ , ... ] ) [ FILTER ( WHERE boolean_expression ) ] grouping_element ::= ( ) | ( expression [ , ... ] ) | ROLLUP ( expression [ , ... ] ) | CUBE ( expression [ , ... ] ) | GROUPING SETS ( grouping_element [ , ... ] ) order_expr ::= expression [ ASC | DESC | USING operator_name ] [ NULLS { FIRST | LAST } ] ``` #### select [with\_clause](#with-clause)SELECT[select\_list](#select-list)[trailing\_select\_clauses](#trailing-select-clauses) #### with\_clause WITHRECURSIVE,[common\_table\_expression](#common-table-expression) #### select\_list ALLDISTINCTON(,[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression))\*,[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression)[fn\_over\_window](#fn-over-window)[ordinary\_aggregate\_fn\_invocation](#ordinary-aggregate-fn-invocation)[within\_group\_aggregate\_fn\_invocation](#within-group-aggregate-fn-invocation)AS[name](/stable/api/ysql/syntax_resources/grammar_diagrams#name) #### trailing\_select\_clauses FROM,[from\_item](/stable/api/ysql/syntax_resources/grammar_diagrams#from-item)WHERE[boolean\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#boolean-expression)GROUPBY,[grouping\_element](#grouping-element)HAVING[boolean\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#boolean-expression)WINDOW,[name](/stable/api/ysql/syntax_resources/grammar_diagrams#name)AS[window\_definition](/stable/api/ysql/syntax_resources/grammar_diagrams#window-definition)UNIONINTERSECTEXCEPTALLDISTINCT[select](#select)ORDERBY,[order\_expr](#order-expr)LIMIT[int\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#int-expression)ALLOFFSET[int\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#int-expression)ROWROWSFETCHFIRSTNEXT[int\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#int-expression)ROWROWSONLYFORUPDATENO KEY UPDATESHAREKEY SHAREOF,[table\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#table-name)NOWAITSKIP LOCKED #### common\_table\_expression [cte\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#cte-name)(,[column\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#column-name))ASNOTMATERIALIZED([select](#select)[values](/stable/api/ysql/syntax_resources/grammar_diagrams#values)[insert](/stable/api/ysql/syntax_resources/grammar_diagrams#insert)[update](/stable/api/ysql/syntax_resources/grammar_diagrams#update)[delete](/stable/api/ysql/syntax_resources/grammar_diagrams#delete)) #### fn\_over\_window [name](/stable/api/ysql/syntax_resources/grammar_diagrams#name)(,[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression)\*FILTER(WHERE[boolean\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#boolean-expression))OVER[window\_definition](/stable/api/ysql/syntax_resources/grammar_diagrams#window-definition)[name](/stable/api/ysql/syntax_resources/grammar_diagrams#name) #### ordinary\_aggregate\_fn\_invocation [name](/stable/api/ysql/syntax_resources/grammar_diagrams#name)(ALLDISTINCT,[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression)\*ORDERBY,[order\_expr](#order-expr))FILTER(WHERE[boolean\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#boolean-expression)) #### within\_group\_aggregate\_fn\_invocation [name](/stable/api/ysql/syntax_resources/grammar_diagrams#name)(,[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression))WITHINGROUP(ORDERBY,[order\_expr](#order-expr))FILTER(WHERE[boolean\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#boolean-expression)) #### grouping\_element ()(,[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression))ROLLUP(,[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression))CUBE(,[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression))GROUPINGSETS(,[grouping\_element](#grouping-element)) #### order\_expr [expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression)ASCDESCUSING[operator\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#operator-name)NULLSFIRSTLAST See the section [The WITH clause and common table expressions](/stable/api/ysql/the-sql-language/with-clause/ "The WITH clause and common table expressions") for more information about the semantics of the `common_table_expression` grammar rule. ## Semantics - An error is raised if the specified `table_name` does not exist. - `*` represents all columns. While the where clause allows a wide range of operators, the exact conditions used in the where clause have significant performance considerations (especially for large datasets). For details on `from_item` see [SELECT](https://www.postgresql.org/docs/15/static/sql-select.html "SELECT") in the PostgreSQL documentation. The `fn_over_window` rule denotes the special kind of `SELECT` list item that must be used to invoke a window function and that may be used to invoke an aggregate function. (Window functions are known as analytic functions in the terminology of some SQL database systems.) The dedicated diagram that follows the main diagram for the `select` rule shows the `FILTER` and the `OVER` keywords. You can see that you *cannot* invoke a function in this way without specifying an `OVER` clause—and that the `OVER` clause requires the specification of the so-called [*window*](/stable/api/ysql/exprs/window_functions/invocation-syntax-semantics/#the-window-definition-rule "window") that gives this invocation style its name. The `FILTER` clause is optional and may be used *only* when you invoke an aggregate function in this way. All of this is explained in the [Window function invocation—SQL syntax and semantics](/stable/api/ysql/exprs/window_functions/invocation-syntax-semantics/ "Window function invocation—SQL syntax and semantics") section within the major section [Window functions](/stable/api/ysql/exprs/window_functions/ "Window functions"). The `ordinary_aggregate_fn_invocation` rule and the `within_group_aggregate_fn_invocation` rule denote the special kinds of `SELECT` list item that are used to invoke an aggregate function (when it isn't invoked as a window function). When an aggregate function is invoked in either of these two ways, it's very common to do so in conjunction with the `GROUP BY` and `HAVING` clauses. All of this is explained in the [Aggregate function invocation—SQL syntax and semantics](/stable/api/ysql/exprs/aggregate_functions/invocation-syntax-semantics/ "Aggregate function invocation—SQL syntax and semantics") section within the major section [Aggregate functions](/stable/api/ysql/exprs/aggregate_functions/ "Aggregate functions"). When you understand the story of the invocation of these two kinds of functions from the accounts in the [Window functions](/stable/api/ysql/exprs/window_functions/ "Window functions") section and the [Aggregate functions](/stable/api/ysql/exprs/aggregate_functions/ "Aggregate functions") section, you can use the `\df` meta-command in `ysqlsh` to discover the status of a particular function, thus: ``` \df row_number ... | Argument data types | Type ... +----------------------------------------+-------- ... | | window \df rank ... | Argument data types | Type ... +----------------------------------------+-------- ... | | window ... | VARIADIC "any" ORDER BY VARIADIC "any" | agg \df avg ... | Argument data types | Type ... +----------------------------------------+-------- ... | bigint | agg ... | | agg ``` - A function whose type is listed as *"window"* can be invoked *only* as a window function. See this account of [`row_number()`](/stable/api/ysql/exprs/window_functions/function-syntax-semantics/row-number-rank-dense-rank/#row-number "row_number()"). - A function whose type is listed *both* as *"window"* and as *agg* can be invoked: - *either* as a window function using the `fn_over_window` syntax—see this account of [`rank()`](/stable/api/ysql/exprs/window_functions/function-syntax-semantics/row-number-rank-dense-rank/#rank "rank()") - *or* as a so-called [within-group hypothetical-set aggregate function](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/#within-group-hypothetical-set-aggregate-functions "within-group hypothetical-set aggregate function") using the `within_group_aggregate_fn_invocation` syntax—see this account of [`rank()`](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/rank-dense-rank-percent-rank-cume-dist/#rank "rank()"). - A function whose type is listed only as *"agg"* can, in fact, be invoked *either* as an aggregate function using the `ordinary_aggregate_fn_invocation` syntax *or* as a window function using the `fn_over_window` syntax. The `avg()` function is described in the "Aggregate functions" major section in the subsection [`avg()`, `count()`, `max()`, `min()`, `sum()`](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/avg-count-max-min-sum/ "avg(), count(), max(), min(), sum()"). See its subsections [`GROUP BY` syntax](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/avg-count-max-min-sum/#group-by-syntax "GROUP BY syntax") and [`OVER` syntax](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/avg-count-max-min-sum/#over-syntax "OVER syntax") for, respectively, the `ordinary_aggregate_fn_invocation` and the `fn_over_window` invocation alternatives. - Notice that the three functions [`mode()`](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/mode-percentile-disc-percentile-cont/#mode "mode()"), [`percentile_disc()`](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/mode-percentile-disc-percentile-cont/#percentile-disc-percentile-cont "percentile_disc()"), and [`percentile_cont()`](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/mode-percentile-disc-percentile-cont/#percentile-disc-percentile-cont "percentile_cont()") are exceptions to this general rule (and they are the *only* exceptions). These functions are referred to as [within-group ordered-set aggregate functions](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/#within-group-ordered-set-aggregate-functions "within-group ordered-set aggregate functions"). `\df` lists the type of these functions only as *"agg"*. But these *cannot* be invoked as window functions. The attempt causes this error: ``` 42809: WITHIN GROUP is required for ordered-set aggregate mode ``` **Note:** The documentation in the [Aggregate functions](/stable/api/ysql/exprs/aggregate_functions/ "Aggregate functions") major section usually refers to the syntax that the `ordinary_aggregate_fn_invocation` rule and the `within_group_aggregate_fn_invocation` rule jointly govern as the [`GROUP BY` syntax](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/avg-count-max-min-sum/#group-by-syntax "GROUP BY syntax") because it's these two syntax variants (and *only* these two) can be used together with the `GROUP BY` clause (and therefore the `HAVING` clause). And it usually refers to the syntax that the `fn_over_window` rule governs as the [`OVER` syntax](/stable/api/ysql/exprs/aggregate_functions/function-syntax-semantics/avg-count-max-min-sum/#over-syntax "OVER syntax") because this syntax variant *requires* the use of the `OVER` clause. Moreover, the use of the `GROUP BY` clause (and therefore the `HAVING` clause) is illegal with this syntax variant. ## Examples Create two sample tables. ```plpgsql yugabyte=# CREATE TABLE sample1(k1 bigint, k2 float, v text, PRIMARY KEY (k1, k2)); ``` ```plpgsql yugabyte=# CREATE TABLE sample2(k1 bigint, k2 float, v text, PRIMARY KEY (k1, k2)); ``` Insert some rows. ```plpgsql yugabyte=# INSERT INTO sample1(k1, k2, v) VALUES (1, 2.5, 'abc'), (1, 3.5, 'def'), (1, 4.5, 'xyz'); ``` ```plpgsql yugabyte=# INSERT INTO sample2(k1, k2, v) VALUES (1, 2.5, 'foo'), (1, 4.5, 'bar'); ``` Select from both tables using join. ```plpgsql yugabyte=# SELECT a.k1, a.k2, a.v as av, b.v as bv FROM sample1 a LEFT JOIN sample2 b ON (a.k1 = b.k1 and a.k2 = b.k2) WHERE a.k1 = 1 AND a.k2 IN (2.5, 3.5) ORDER BY a.k2 DESC; ``` ``` k1 | k2 | av | bv ----+-----+-----+----- 1 | 3.5 | def | 1 | 2.5 | abc | foo (2 rows) ``` ## See also - [CREATE TABLE](/stable/api/ysql/the-sql-language/statements/ddl_create_table "CREATE TABLE") - [INSERT](/stable/api/ysql/the-sql-language/statements/dml_insert "INSERT")