SELECT

SELECT reads rows from tables or table functions and transforms them into a result.

Example

List the ten largest tables by row count:

SELECT name, total_rows
FROM system.tables
WHERE datastore <> 'system'
ORDER BY total_rows DESC
LIMIT 10;

Syntax

[WITH name AS (query) [, ...]]
SELECT [DISTINCT] expression [AS alias] [, ...]
[FROM source]
[WHERE condition]
[GROUP BY expression [, ...]]
[HAVING condition]
[ORDER BY expression [ASC | DESC] [, ...]]
[LIMIT count]
[OFFSET count];

source can be a table, a supported join, a subquery, or a table function.

Columns and expressions

Select named columns, use * for all columns, or compute expressions. AS names an output column. DISTINCT removes duplicate result rows.

SELECT DISTINCT datastore FROM system.tables;

Tables can be qualified as datastore.schema.table. An unqualified table name uses the default datastore and schema. System tables use two-part names, such as system.tables.

Filtering

WHERE filters input rows before aggregation.

SELECT name, total_rows
FROM system.tables
WHERE total_rows > 1000;

See functions and operators for string matching, date expressions, and other filters.

Joins

Use JOIN in the FROM clause to combine rows from two tables. The ON clause specifies which rows match.

Inner joins

An INNER JOIN returns a row for each matching pair. JOIN without a type means INNER JOIN.

List customers and their orders:

SELECT c.name, o.id AS order_id
FROM customers AS c
JOIN orders AS o ON c.id = o.customer_id;

Left joins

A LEFT JOIN also includes rows from the left table that have no match. For those rows, columns from the right table are NULL.

List all customers, including those without orders:

SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id;

Semi joins

A SEMI JOIN keeps rows from the left table that have at least one match. It returns only columns from the left table, without repeating a left row for multiple matches.

List customers who have placed an order:

SELECT c.id, c.name
FROM customers AS c
SEMI JOIN orders AS o ON c.id = o.customer_id;

Anti joins

An ANTI JOIN keeps rows from the left table that have no match. It returns only columns from the left table.

List customers who have never placed an order:

SELECT c.id, c.name
FROM customers AS c
ANTI JOIN orders AS o ON c.id = o.customer_id;

Join conditions

Inner, left, semi, and anti joins support equality conditions such as c.id = o.customer_id. Use AND to match on multiple keys or add filters to matching pairs, for example ON c.id = o.customer_id AND o.total > 100.

An inner join can also match rows using an inequality instead of equality. This is called a range join. For example, given a discount_tiers table with name and min_total columns, find every discount tier each order qualifies for:

SELECT o.id AS order_id, d.name AS discount_tier
FROM orders AS o
JOIN discount_tiers AS d ON o.total >= d.min_total;

Range joins require exactly one <, <=, >, or >= comparison between numeric, date, or timestamp expressions. Additional join conditions and left, semi, or anti range joins are not supported.

Grouping and aggregates

GROUP BY combines rows with matching keys. HAVING filters the resulting groups, after aggregation.

SELECT table_id, count(*) AS column_count
FROM system.columns
GROUP BY table_id
HAVING count(*) > 5
ORDER BY column_count DESC;

See aggregate functions for the supported aggregates and their result types.

Ordering and limits

ORDER BY sorts the result; ASC is ascending and DESC is descending. LIMIT caps the number of rows, and OFFSET skips rows before returning them. Use an explicit order when the choice of returned rows matters.

SELECT name FROM system.tables ORDER BY name LIMIT 10 OFFSET 10;

Common table expressions

WITH names a query result for use in the same statement.

WITH user_tables AS (
  SELECT name, total_rows
  FROM system.tables
  WHERE datastore <> 'system'
)
SELECT * FROM user_tables WHERE total_rows > 0;

Compatibility

PostgreSQL wire compatibility does not imply support for every PostgreSQL query form. The clauses and join forms above describe the supported surface.

EXPLAIN - inspect a query plan.
VALUES - construct rows directly.
System tables - query server metadata.