---
metadata:
  - name: generator
    content: Diplodoc Platform v5.52.0
alternate:
  - https://ydb.tech/docs/en/postgresql/statements/select.md?version=v25.4
  - https://ydb.tech/docs/ru/postgresql/statements/select.md?version=v25.4
  - href: en/postgresql/statements/select.md
    type: text/markdown
    title: Markdown version
  - href: ../../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/postgresql/statements/select.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# SELECT

<!-- markdownlint-disable blanks-around-fences -->

<!-- source: en/postgresql/_includes/alert_preview.md -->
{% note warning %}

At the moment, YDB's compatibility with PostgreSQL **is under development**, so not all PostgreSQL constructs and [functions](https://ydb.tech/docs/en/postgresql/functions.md?version=v25.4) are supported yet. PostgreSQL compatibility is available for testing in the form of a Docker container, which can be deployed by following these [instructions](https://ydb.tech/docs/en/postgresql/docker-connect.md?version=v25.4).

{% endnote %}
<!-- endsource: en/postgresql/_includes/alert_preview.md -->

Syntax of the `SELECT` statement:

<!-- source: en/postgresql/_includes/statements/select/syntax.md -->
```sql
SELECT [<table column>, ... | *]
FROM [<table name> | <sub query>] AS <table name alias>
LEFT | RIGHT | CROSS | INNER JOIN <another table> AS <table name alias> ON <join condition>
WHERE <condition>
GROUP BY <table column>
HAVING <condition>
UNION | UNION ALL | EXCEPT | INTERSECT
ORDER BY <table column> [ASC | DESC]
LIMIT [<limit value>]
OFFSET <offset number>
```
<!-- endsource: en/postgresql/_includes/statements/select/syntax.md -->

## Calling SELECT without specifying a target table {#select_func}

`SELECT` is used to return computations to the client side and can be used even without specifying a table, as seen in constructs like `SELECT NOW()`, or for performing operations such as working with dates, converting numbers, or calculating string lengths. However, `SELECT` is also used in conjunction with `FROM ...` to retrieve data from a specified table. When used with `INSERT INTO ...`, `SELECT` serves to select data that will be inserted into another table. In subqueries, `SELECT` is utilized within a larger query, not necessarily with `FROM ...`, to contribute to the overall computation or condition.

For example, `SELECT` can be used for working with dates, converting numbers, or calculating string length:

<!-- source: en/postgresql/_includes/statements/select/select_no_target.md -->
```sql
SELECT CURRENT_DATE + INTERVAL '1 day';  -- Returns tomorrow's date
SELECT LENGTH('Hello');  -- Returns the length of the string 'Hello'
SELECT CAST('123' AS INTEGER);  -- Converts the string '123' to an integer
```
<!-- endsource: en/postgresql/_includes/statements/select/select_no_target.md -->

Such use of `SELECT` can be useful for testing, debugging expressions, or SQL functions without accessing a real table, but more often `SELECT` is used to retrieve rows from one or more tables.

## Retrieving values from one or multiple columns {#select_from}

To return values from one or several columns of a table, `SELECT` is used in the following form:

<!-- source: en/postgresql/_includes/statements/select/select_few_col_syntax.md -->
```sql
SELECT <column name> , <column name>
FROM <table name>;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_few_col_syntax.md -->

To read all data from a table, for example, the `people` table, you need to execute the command `SELECT * FROM people;`, where `*` is the special symbol indicating all columns. With this statement, all rows from the table will be returned with data from all columns.

To display the "id", "name", and "lastname" columns for all rows of the `people` table, you can do it as follows:

<!-- source: en/postgresql/_includes/statements/select/select_few_col_example.md -->
```sql
SELECT id, name, lastname
FROM people;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_few_col_example.md -->

## Limiting the results obtained from a query using WHERE {#select_from_where}

To select only a subset of rows, the `WHERE` clause with filtering criteria is used: `WHERE <column name> <condition> <column value>;`:

<!-- source: en/postgresql/_includes/statements/select/select_few_col_where.md -->
```sql
SELECT id, name, lastname
FROM people
WHERE age > 30;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_few_col_where.md -->

`WHERE` allows the use of multiple conditional selection operators (`AND`, `OR`) to create complex selection conditions, such as ranges:

<!-- source: en/postgresql/_includes/statements/select/select_few_col_and.md -->
```sql
SELECT id, name, lastname
FROM people
WHERE age > 30 AND age < 45;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_few_col_and.md -->

## Retrieving a subset of rows using LIMIT and OFFSET conditions {#select_from_where_limit}

To limit the number of rows in the result set, `LIMIT` is used with the specified number of rows:

<!-- source: en/postgresql/_includes/statements/select/select_few_col_where_limit.md -->
```sql
SELECT id, name, lastname
FROM people
WHERE age > 30 AND age < 45
LIMIT 5;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_few_col_where_limit.md -->

Thus, the first 5 rows from the query will be printed out. With `OFFSET`, you can specify how many rows to skip before starting to print out rows:

<!-- source: en/postgresql/_includes/statements/select/select_few_col_where_limit_offset.md -->
```sql
SELECT id, name, lastname
FROM people
WHERE age > 30 AND age < 45
OFFSET 3
LIMIT 5;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_few_col_where_limit_offset.md -->

When specifying `OFFSET 3`, the first 3 rows of the resulting selection from the `people` table will be skipped.

## Sorting the results of a query using ORDER BY {#select_from_where_order_by}

By default, the database does not guarantee the order of returned rows, and it may vary from query to query. If a specific order of rows is required, the `ORDER BY` clause is used with the designation of the column for sorting and the direction of the sort:

<!-- source: en/postgresql/_includes/statements/select/select_few_col_where_order_by.md -->
```sql
SELECT id, name, lastname, age
FROM people
WHERE age > 30 AND age < 45
ORDER BY age DESC;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_few_col_where_order_by.md -->

Sorting is applied to the results returned by the `SELECT` clause, not to the original columns of the table specified in the `FROM` clause. Sorting can be done in ascending order – `ASC` (from smallest to largest - this is the default option and does not need to be specified) or in descending order – `DESC` (from largest to smallest). How sorting is executed depends on the data type of the column. For example, strings are stored in utf-8 and are compared according to "unicode collate" (based on character codes).

## Grouping the results of a query from one or more tables using GROUP BY {#select_from_where_group_by}

`GROUP BY` is used to aggregate data across multiple records and group the results by one or several columns. The syntax for using `GROUP BY` is as follows:

<!-- source: en/postgresql/_includes/statements/select/select_group_by_syntax.md -->
```sql
SELECT <column name>, <column name>, ...
FROM <table name>
[WHERE <column name> = <value>]
GROUP BY <column name>, <column name>, ...;
[HAVING <column name> = <limit column value>]
[LIMIT <value>]
[OFFSET <value>]
```
<!-- endsource: en/postgresql/_includes/statements/select/select_group_by_syntax.md -->

Example of grouping data from the "people" table by gender ("sex") and age ("age") with a selection limit (`WHERE`) based on age:

<!-- source: en/postgresql/_includes/statements/select/select_few_col_where_group_by_few_cond.md -->
```sql
SELECT sex, age
FROM people
WHERE age > 40
GROUP BY sex, age;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_few_col_where_group_by_few_cond.md -->

In the previous example, we used `WHERE` – an optional parameter for filtering the result, which filters individual rows before applying `GROUP BY`. In the next example, we use `HAVING` to exclude from the result the rows of groups that do not meet the condition. `HAVING` filters the rows of groups created by `GROUP BY`. When using `HAVING`, the query becomes grouped, even if `GROUP BY` is absent. All selected rows are considered to form one group, and in the `SELECT` list and `HAVING` clause, one can refer to the table columns only from aggregate functions. Such a query will yield a single row if the result of the `HAVING` condition is true, and zero rows otherwise.

### Examples for HAVING

#|
|| `HAVING` + `GROUP BY` | `HAVING` + `WHERE` + `GROUP BY` ||
||

<!-- source: en/postgresql/_includes/statements/select/select_having_group_by.md -->
```sql
SELECT sex, country, age
FROM people
GROUP BY sex, country, age
HAVING sex = 'Female';
```
<!-- endsource: en/postgresql/_includes/statements/select/select_having_group_by.md -->

|

<!-- source: en/postgresql/_includes/statements/select/select_having_where_group_by.md -->
```sql
SELECT sex, name,age
FROM people
WHERE age > 40
GROUP BY sex, name, age
HAVING sex = 'Female';
```
<!-- endsource: en/postgresql/_includes/statements/select/select_having_where_group_by.md -->

||
|#

## Joining tables using the JOIN clause {#select_from_join_on}

`SELECT` can be applied to multiple tables with the specification of the type of table join. The joining of tables is set through the `JOIN` clause, which comes in five types: `LEFT JOIN`, `RIGHT JOIN`, `INNER JOIN`, `CROSS JOIN`, `FULL JOIN`. When a `JOIN` is performed on a specific condition, such as a key, and one of the tables has several rows with the same value of this key, a [Cartesian product](https://en.wikipedia.org/wiki/Cartesian_product) occurs. This means that each row from one table will be joined with every corresponding row from the other table.

### Joining tables using LEFT JOIN, RIGHT JOIN, or INNER JOIN {#select_from_left_right__inner_join_on}

The syntax for `SELECT` using `LEFT JOIN`, `RIGHT JOIN`, `INNER JOIN`, `FULL JOIN` is the same:

<!-- source: en/postgresql/_includes/statements/select/select_join_syntax.md -->
```sql
SELECT <table name left>.<column name>, ... ,
FROM <table name left>
LEFT | RIGHT | INNER | FULL JOIN <table name right> AS <table name right alias>
ON <table name left>.<column name> = <table name right>.<column name>;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_join_syntax.md -->

All `JOIN` modes, except `CROSS JOIN`, use the keyword `ON` for joining tables. In the case of `CROSS JOIN`, its usage syntax will be as follows: `CROSS JOIN <table name> AS <table name alias>;`. Let's consider an example of using each `JOIN` mode separately.

#### LEFT JOIN {#left_join}

Returns all rows from the left table and the matching rows from the right table. If there are no matches, it returns `NULL` (the output will be empty) for all columns of the right table. Example of using `LEFT JOIN`:

<!-- source: en/postgresql/_includes/statements/select/select_left_join.md -->
```sql
SELECT people.name, people.lastname, card.social_card_number
FROM people
LEFT JOIN social_card AS card
ON people.name = card.card_holder_name AND people.lastname = card.card_holder_lastname;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_left_join.md -->

The result of executing an SQL query using `LEFT JOIN` without one record in the right table `social_card`:

<!-- source: en/postgresql/_includes/statements/select/select_left_join_output.md -->
```text
 name   | lastname | social_card_number
---------+----------+--------------------
 John    | Doe      |          123456789
 Jane    | Smith    |          223456789
 Alice   | Johnson  |          323456789
 Bob     | Brown    |          423456789
 Charlie | Davis    |          523456789
 Eve     | Martin   |          623456789
 Frank   | White    |
```
<!-- endsource: en/postgresql/_includes/statements/select/select_left_join_output.md -->

#### RIGHT JOIN {#right_join}

Returns all rows from the right table and the matching rows from the left table. If there are no matches, it returns `NULL` for all columns of the left table. This type of `JOIN` is rarely used, as its functionality can be replaced by `LEFT JOIN`, and swapping the tables. Example of using `RIGHT JOIN`:

<!-- source: en/postgresql/_includes/statements/select/select_right_join.md -->
```sql
SELECT people.name, people.lastname, card.social_card_number
FROM people
RIGHT JOIN social_card AS card
ON people.name = card.card_holder_name AND people.lastname = card.card_holder_lastname;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_right_join.md -->

The result of executing an SQL query using `RIGHT JOIN` without one record in the left table `people`:

<!-- source: en/postgresql/_includes/statements/select/select_right_join_output.md -->
```text
 name   | lastname | social_card_number
---------+----------+--------------------
John    | Doe      |          123456789
Jane    | Smith    |          223456789
Alice   | Johnson  |          323456789
Bob     | Brown    |          423456789
Charlie | Davis    |          523456789
Eve     | Martin   |          623456789
        |          |          723456789
```
<!-- endsource: en/postgresql/_includes/statements/select/select_right_join_output.md -->

#### INNER JOIN {#inner_join}

Returns rows when there are matching values in both tables. Excludes from the results those rows for which there are no matches in the joined tables. Example of using `INNER JOIN`:

<!-- source: en/postgresql/_includes/statements/select/select_inner_join.md -->
```sql
SELECT people.name, people.lastname, card.social_card_number
FROM people
RIGHT JOIN social_card AS card
ON people.name = card.card_holder_name AND people.lastname = card.card_holder_lastname;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_inner_join.md -->

Such an SQL query will return only those rows for which there are matches in both tables:

<!-- source: en/postgresql/_includes/statements/select/select_inner_join_output.md -->
```text
 name   | lastname | social_card_number
---------+----------+--------------------
John    | Doe      |          123456789
Jane    | Smith    |          223456789
Alice   | Johnson  |          323456789
Bob     | Brown    |          423456789
Charlie | Davis    |          523456789
Eve     | Martin   |          623456789
```
<!-- endsource: en/postgresql/_includes/statements/select/select_inner_join_output.md -->

#### CROSS JOIN {#cross_join}

Returns the combined result of every row from the left table with every row from the right table. `CROSS JOIN` is usually used when all possible combinations of rows from two tables are needed. `CROSS JOIN` simply combines each row of one table with every row of another without any condition, which is why its syntax lacks the `ON` keyword: `CROSS JOIN <table name> AS <table name alias>;`.

Example of using `CROSS JOIN` with the result output limited by `LIMIT 5`:

<!-- source: en/postgresql/_includes/statements/select/select_cross_join.md -->
```sql
SELECT people.name, people.lastname, card.social_card_number
FROM people
CROSS JOIN social_card AS card
LIMIT 5;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_cross_join.md -->

The example above will return all possible combinations of columns participating in the selection from the two tables:

<!-- source: en/postgresql/_includes/statements/select/select_cross_join_output.md -->
```text
name | lastname | social_card_number
------+----------+--------------------
 John | Doe      |          123456789
 John | Doe      |          223456789
 John | Doe      |          323456789
 John | Doe      |          423456789
 John | Doe      |          523456789
```
<!-- endsource: en/postgresql/_includes/statements/select/select_cross_join_output.md -->

#### FULL JOIN {#full_join}

Returns both matched and unmatched rows in both tables, filling in `NULL` for columns from the table for which there is no match. Example of executing an SQL query using `FULL JOIN`:

<!-- source: en/postgresql/_includes/statements/select/select_full_join.md -->
```sql
SELECT people.name, people.lastname, card.social_card_number
FROM people
FULL JOIN social_card AS card
ON people.name = card.card_holder_name AND people.lastname = card.card_holder_lastname;
```
<!-- endsource: en/postgresql/_includes/statements/select/select_full_join.md -->

As a result of executing the SQL query, the following output will be returned:

<!-- source: en/postgresql/_includes/statements/select/select_full_join_output.md -->
```text
 name   | lastname | social_card_number
---------+----------+--------------------
 Liam    | Martinez |         1323456789
 Eve     | Martin   |          623456789
 Hank    | Miller   |          923456789
 Molly   | Robinson |         1423456789
 Sam     | Walker   |
 Paul    | Harris   |         1723456789
 Kara    | Thompson |         1223456789
         |          |         1923456789
...
```
<!-- endsource: en/postgresql/_includes/statements/select/select_full_join_output.md -->