SELECT

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

• Grammar
• Diagram

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 } ]

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)

See also

• CREATE TABLE
• INSERT