`SELECT` clause

The SELECT clause specifies the list of columns to be returned by the query. Although it comes first in the query, logically the expressions in this expression are executed last. A SELECT clause can contain arbitrary expressions that transform output, as well as aggregate and window functions.

Syntax

Select all columns

[ ... ]
SELECT [ { ALL | DISTINCT } ]
       [ TOP <n> ]
       [{<object_name>|<alias>}.]*

       [ ILIKE '<pattern>' ]

       [ EXCLUDE
         {
           <col_name> | ( <col_name>, <col_name>, ... )
         }
       ]

       [ REPLACE
         {
           ( <expr> AS <col_name> [ , <expr> AS <col_name>, ... ] )
         }
       ]

       [ RENAME
         {
           <col_name> AS <col_alias>
           | ( <col_name> AS <col_alias>, <col_name> AS <col_alias>, ... )
         }
       ]

The following keyword combinations can be specified after SELECT *. The keywords must be in the order shown below:

SELECT * ILIKE ... REPLACE ...

SELECT * ILIKE ... RENAME ...

SELECT * ILIKE ... REPLACE ... RENAME ...

SELECT * EXCLUDE ... REPLACE ...

SELECT * EXCLUDE ... RENAME ...

SELECT * EXCLUDE ... REPLACE ... RENAME ...

SELECT * REPLACE ... RENAME ...

Selecting specific columns

[ ... ]
SELECT [ { ALL | DISTINCT } ]
       [ TOP <n> ]
       {
         [{<object_name>|<alias>}.]<col_name>
         | [{<object_name>|<alias>}.]$<col_position>
         | <expr>
       }
       [ [ AS ] <col_alias> ]
       [ , ... ]
[ ... ]

End comma is supported in the column list. For example, the following SELECT clause is supported:

SELECT emp_id,
       name,
       department,
    FROM employees;

Parameters

ALL | DISTINCT
Specifies whether to perform duplicate deletion in the result set:
- ALL includes all values in the result set.
- DISTINCT removes duplicates from the result set.
Default: ALL
For a detailed description, see DISTINCT clause.

<object_name> or <alias>
Specifies the object identifier or object alias (as defined in the FROM clause).

* (asterisk)
The asterisk is an abbreviation indicating that the output must include all columns of the specified object, or all columns of all objects if * is not followed by an object name or alias.
For a detailed description, see Expression with asterisk *.

<col_name>
Specifies the column identifier (as defined in the FROM clause).

$<col_position>
Indicates the position of the column (starting from 1) as defined in the FROM clause. If the column is referenced from the table, this number cannot exceed the maximum number of columns in the table.

<expr>
Specifies an expression (such as a mathematical expression) that evaluates to a specific value for any given row.

[ AS ] <col_alias>
Specifies the column alias assigned to the resulting expression. It is used as the display name in the top-level SELECT list and as the column name in the embedded view.

Do not assign such an alias to a column that will match the name of another column referenced in the query. For example, if you select columns named prod_id and product_id, do not assign the alias product_id to the prod_id column.

Examples

Select all columns from the table named my_table:
```
SELECT * FROM my_table;
```
Perform arithmetic operations on the columns of the table and specify an alias:
```
SELECT column_1 + column_2 AS sum, sqrt(column_1) AS sq_root FROM my_table;
```

Use prefix aliases to get the same result:

SELECT
    sum: column_1 + column_2,
    sq_root: sqrt(column_1)
FROM my_table;

Select all unique names from the employees table:
```
SELECT DISTINCT name FROM employees;
```
Output the total number of rows in the employees table:
```
SELECT count(*) FROM employees;
```
Select all columns except the name column from the employees table:
```
SELECT * EXCLUDE (name) FROM employees;
```
Select all columns from the employees table, but replace name with the result of applying the lower(name) function:
```
SELECT * REPLACE (lower(name) AS name) FROM employees;
```
Select all columns from the table that match the given regular expression:
```
SELECT COLUMNS('number\d+') FROM employees;
```
Calculate the function over all given columns of the table:
```
SELECT min(COLUMNS(*)) FROM employees;
```
Use double quotes (") to select columns with spaces or special characters:
```
SELECT "Фамилия Имя Отчество" FROM employees;
```

List of `SELECT` columns

A SELECT clause contains a list of expressions that define the result of the query. The SELECT list can refer to any columns in a FROM clause and combine them using expressions. Because the result of a SQL-query is a table, each expression in a SELECT clause also has a name. Expressions can be explicitly named using the AS operator (e.g., expr AS name). If the user does not specify a name, expressions are named automatically by the system.

Column names are case insensitive if they are specified without inverted commas.

Expression with asterisk `*`

An asterisk expression () is a special expression that expands to multiple expressions based on the contents of the FROM clause. In the simplest case, is expanded to all expressions in the FROM clause.

Select all columns from the table named my_table:
```
SELECT * FROM my_table;
```

`DISTINCT` clause

The DISTINCT clause can be used to get only unique rows in the result - so all duplicates will be filtered out.

Select all unique names from the employees table:
```
SELECT DISTINCT name FROM employees;
```

Queries starting with SELECT DISTINCT perform deduplication, which is an expensive operation. Therefore, use DISTINCT only when necessary.

`DISTINCT ON` clause

The DISTINCT ON clause returns only one row for each unique value in the expression set, as defined in the ON clause. If the ORDER BY condition is present, the first row that occurs according to the ORDER BY condition is returned. If the ORDER BY condition is not present, the first row encountered is undefined and can be any row in the table.

Select the employee with the highest salary for each department:

SELECT DISTINCT ON(department) name, salary
    FROM employees
    ORDER BY salary DESC;

When querying large datasets, using DISTINCT for all columns can be a costly operation. Therefore, consider using DISTINCT ON for a column (or set of columns) that guarantees a sufficient degree of uniqueness in the results. For example, using DISTINCT ON for the key column(s) of a table guarantees complete uniqueness.

Aggregate functions

Aggregate functions are functions that combine values from multiple rows into one. When aggregate functions are present in a SELECT statement, the query becomes an aggregate query. In an aggregate query, all expressions must be either part of an aggregate function or part of a group (as specified in a GROUP BY clause).

We get the total number of rows in the employee table:
```
SELECT count(*) FROM employees;
```
Get the total number of rows in the table of employees grouped by department:
```
SELECT department, count(*)
    FROM employees
    GROUP BY department;
```

For a detailed description, see Aggregate functions.

Window functions

Window functions are functions that perform calculations for a set of strings that are related in some way to the current string. A call to a window function always contains an OVER clause following the window function name and arguments. The OVER clause specifies exactly how the query strings are to be split for processing by the window function.

We get a row_number column containing incremental identifiers for each row of the salary table:
```
SELECT row_number() OVER ()
    FROM salaries;
```
Compute the difference between the current amount and the previous amount in descending order of time:
```
SELECT amount - lag(amount) OVER (ORDER BY time)
    FROM salaries;
```

For a detailed description, see Window functions.

SELECT clause

Syntax

Select all columns

Selecting specific columns

Parameters

Examples

List of SELECT columns

Expression with asterisk *

DISTINCT clause

DISTINCT ON clause

Aggregate functions

Window functions

`SELECT` clause

List of `SELECT` columns

Expression with asterisk `*`

`DISTINCT` clause

`DISTINCT ON` clause