SELECT statement

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

Syntax

[ WITH ... ]
SELECT [ DISTINCT ]
    { * | <col_name> | <expression> [ [AS] <alias> ] [,...] }
    [ FROM ... ]
    [ WHERE ... ]
    [ GROUP BY ... ]
    [ HAVING ... ]
    [ QUALIFY ... ]
    [ UNION ... ]
    [ INTERSECT ... ]
    [ EXCEPT ... ]
    [ ORDER BY ... ]
    [ OFFSET ... ]
    [ LIMIT ...]

Parameters

  • DISTINCT +. Specifies whether to perform duplicate deletion in the result set.
    For a detailed description, see DISTINCT clause.

  • * (asterisk) +. The asterisk is an abbreviation indicating that the output should contain all columns of the specified object.
    For a detailed description, see Expression with asterisk *.

  • <col_name>
    Specifies the identifier of the column.

  • <expression>
    Specifies an expression that evaluates to a specific value for any given row.

  • <alias>
    Specifies the alias of a column in the resulting table.

    Do not assign a column alias 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.

    See also section AS operator

End comma is supported in column listings. For example, the following SELECT expression is supported:

SELECT
    name,
    department,
    FROM employees;

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;

  • Select all unique names from the employees (employees) table:

    SELECT DISTINCT name FROM employees;

  • Output the total number of rows in the employees table using the aggregate function count:

    SELECT count(*) FROM employees;

  • Use double quotes (") to select columns with spaces or special characters:

    SELECT "Фамилия Имя Отчество" FROM employees;

List of SELECT columns

A SELECT query contains a list of columns or expressions over columns that define the result of the query. This list can refer to any columns in a FROM expression and combine them using expressions. Because the result of a SELECT query is a table, each column in the SELECT list has a name. Names can be explicitly named using the AS operator (e.g., expr AS name). If the user does not specify a name, the columns in the results 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.

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.