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.

Syntax

• Grammar
• Diagram

select ::= [ WITH [ RECURSIVE ] { with_query [ , ... ] } ]  SELECT 
           [ ALL | DISTINCT [ ON { ( expression [ , ... ] ) } ] ] 
           [ * | { expression [ [ AS ] name ] } [ , ... ] ]  
           [ FROM { from_item [ , ... ] } ] [ WHERE condition ]  
           [ GROUP BY { grouping_element [ , ... ] } ] 
           [ HAVING { condition [ , ... ] } ]  
           [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] 
           [ ORDER BY { order_expr [ , ... ] } ]  
           [ LIMIT [ integer | ALL ] ] 
           [ OFFSET integer [ ROW | ROWS ] ]
order_expr ::= expression [ ASC | DESC | USING operator_name ] 
               [ NULLS { FIRST | LAST } ]

select

order_expr

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