SELECT
Attention
This page documents an earlier version. Go to the latest (v2.3) version.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. Certain syntax spots, for example a WHERE
clause predicate or the actual argument of a function like sqrt()
, allow only a scalar subquery.
Syntax
select ::= [ WITH [ RECURSIVE ] { with_query [ , ... ] } ] SELECT
[ ALL | DISTINCT [ ON { ( expression [ , ... ] ) } ] ]
[ * | { { expression | fn_over_window } [ [ AS ] name ] }
[ , ... ] ] [ FROM { from_item [ , ... ] } ]
[ WHERE condition ]
[ GROUP BY { grouping_element [ , ... ] } ]
[ HAVING { condition [ , ... ] } ]
[ WINDOW { { name AS window_definition } [ , ... ] } ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY { order_expr [ , ... ] } ]
[ LIMIT [ integer | ALL ] ]
[ OFFSET integer [ ROW | ROWS ] ]
fn_over_window ::= fn_invocation
[ FILTER ( WHERE { boolean_expression [ , ... ] } ) ]
OVER { window_definition | name }
order_expr ::= expression [ ASC | DESC | USING operator_name ]
[ NULLS { FIRST | LAST } ]
select
fn_over_window
order_expr
Note: 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 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 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 major section Window functions.
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).
condition
Specify an expression that evaluates to a Boolean value.
For details on from_item
, grouping_element
, and with_query
see SELECT in the PostgreSQL documentation.
Examples
Create two sample tables.
yugabyte=# CREATE TABLE sample1(k1 bigint, k2 float, v text, PRIMARY KEY (k1, k2));
yugabyte=# CREATE TABLE sample2(k1 bigint, k2 float, v text, PRIMARY KEY (k1, k2));
Insert some rows.
yugabyte=# INSERT INTO sample1(k1, k2, v) VALUES (1, 2.5, 'abc'), (1, 3.5, 'def'), (1, 4.5, 'xyz');
yugabyte=# INSERT INTO sample2(k1, k2, v) VALUES (1, 2.5, 'foo'), (1, 4.5, 'bar');
Select from both tables using join.
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)