Hologres:SELECT

WITH clause

The WITH clause defines one or more subqueries referenced by name in the main query. These named subqueries are called Common Table Expressions (CTEs).

WITH query_name [ ( column_name [, ...] ) ] AS ( select )

Separate multiple CTEs with commas. A later CTE can reference an earlier one. In subsequent queries, use query_name directly as a view.

If no column_name list is specified, the column names default to those returned by the subquery. If a column_name list is specified, use those defined names, and the count must match the number of columns returned by the SELECT statement.

SELECT list

The SELECT list (between the SELECT and FROM keywords) specifies expressions that form the output rows.

Each output column has a name. To assign a name, add AS output_name after the column expression. The AS keyword can be omitted if the desired output name does not match any PostgreSQL keyword. To avoid potential conflicts with future keywords, use AS or enclose the output name in double quotes. If no column name is specified, PostgreSQL assigns one automatically: a simple column reference uses the column name, while more complex expressions may generate a name such as ?column?.

Reference an output column by name in ORDER BY and GROUP BY clauses. In WHERE and HAVING clauses, use the full expression instead -- output column names are not recognized there.

Use an asterisk (*) as a shorthand for all columns of the selected rows, or table_name.* for all columns from a specific table. When using * or table_name.*, AS cannot rename the output columns.

FROM clause

The FROM clause specifies one or more source tables. Multiple source tables produce a Cartesian product (cross join) of all rows. Typically, a WHERE clause with join conditions restricts the result to a small subset of the Cartesian product.

The FROM clause supports these elements:

WHERE clause

The optional WHERE clause filters rows from the result.

WHERE condition

GROUP BY clause

The optional GROUP BY clause condenses rows sharing the same grouping-expression values into a single summary row.

GROUP BY grouping_element [, ...]

The grouping_element expression can be an input column name, the name or ordinal number of an output column from the SELECT list, or an expression formed from input column values. If a name is ambiguous, GROUP BY interprets it as an input column name rather than an output column name.

If a grouping element contains GROUPING SETS, ROLLUP, or CUBE, the GROUP BY clause defines multiple independent grouping sets. This is equivalent to a UNION ALL of subqueries with individual GROUP BY clauses for each grouping set.

Aggregate functions compute across all rows in each group, producing a single value per group. Without a GROUP BY clause, the query treats all selected rows as a single group.

Filter the rows passed to an aggregate function by appending a FILTER clause to the aggregate function call. Only rows matching the filter condition are included in that aggregate's input.

When a GROUP BY clause or any aggregate function is present, SELECT list expressions cannot reference ungrouped columns except within an aggregate function or when the ungrouped column is functionally dependent on grouped columns. A functional dependency exists when the grouped columns include the primary key (or a subset thereof) of the table containing the ungrouped column.

All aggregate functions are computed before any scalar expressions in the HAVING clause or SELECT list. A CASE expression cannot skip the computation of an aggregate function.

HAVING clause

The HAVING clause filters groups produced by GROUP BY. Its syntax is identical to the WHERE clause, but HAVING operates on grouped rows rather than individual rows.

HAVING condition [, ...]

Use HAVING to filter on aggregate results. For example, HAVING sum(length) < interval '5 hours' removes groups where the total length is 5 hours or more.

CUBE, ROLLUP, and GROUPING SETS

These subclauses extend GROUP BY to define multiple grouping sets in a single query.

CUBE ( { expression | ( expression [, ...] ) } [, ...] )

ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )

GROUPING SETS ( grouping_element [, ...] )

DISTINCT clause

SELECT DISTINCT removes all duplicate rows from the result set, keeping one row per group of duplicates.

SELECT DISTINCT accountid FROM table;

COUNT DISTINCT

COUNT DISTINCT counts the number of distinct values in a column. A value appearing multiple times is counted only once. NULL values are excluded from the count.

Exact count:

SELECT c1, COUNT(DISTINCT c2) FROM table GROUP BY c1;

Approximate count:

An exact COUNT DISTINCT calculation can consume significant resources. Hologres provides approx_count_distinct as a less resource-intensive alternative for approximate calculations:

SELECT c1, approx_count_distinct(c2) FROM table GROUP BY c1;

UNION clause

UNION computes the set union of rows returned by two SELECT statements.

select_statement UNION [ ALL | DISTINCT ] select_statement

INTERSECT clause

INTERSECT computes the set intersection of rows returned by two SELECT statements.

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

EXCEPT clause

EXCEPT computes the set difference: rows in the left SELECT result that are not in the right SELECT result.

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

ORDER BY clause

The optional ORDER BY clause sorts result rows by one or more expressions.

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

Rows are sorted by the leftmost expression first. Rows equal on the leftmost expression are compared on the next expression, and so on. Rows equal on all expressions return in an implementation-dependent order.

Each expression can be:

• The name or ordinal number of an output column from the SELECT list.

• An arbitrary expression formed from input column values.

The ordinal number refers to the position of the output column from left to right. This allows ordering by columns that do not have unique names. Assigning a unique name with AS is generally preferred.

When an ORDER BY expression is a simple name that matches both an output column name and an input column name, ORDER BY uses the output column name. This differs from GROUP BY, which uses the input column name in the same situation. The inconsistency exists for SQL standard compatibility.

Sort direction:

• Add ASC (ascending, the default) or DESC (descending) after any expression.

• Alternatively, use a USING clause with a sort operator that is a less-than or greater-than member of a B-tree operator family. ASC is generally equivalent to USING <, and DESC to USING >.

NULL ordering:

• NULLS LAST: NULLs sort after all non-NULL values (default for ASC).

• NULLS FIRST: NULLs sort before all non-NULL values (default for DESC).

• By default, NULLs are treated as greater than non-NULL values.

• With a USING clause, the default NULL position depends on whether the operator is less-than or greater-than.

Ordering options apply only to the expression they follow. For example, ORDER BY x, y DESC is not the same as ORDER BY x DESC, y DESC.

Limitation: When ORDER BY is applied to a UNION, INTERSECT, or EXCEPT result, only output column names or ordinal numbers are allowed -- not expressions.

LIMIT clause

The LIMIT clause restricts the number of rows returned. FETCH FIRST is an equivalent SQL-standard syntax.

LIMIT { count | ALL }
OFFSET start

FETCH FIRST can be used as an alternative to LIMIT:

FETCH FIRST count ROWS ONLY

When both are specified, start rows are skipped before counting count rows to return.

Use an ORDER BY clause with LIMIT for predictable results. Without ORDER BY, different executions may return different subsets -- this is expected behavior, not a bug.

The query planner factors LIMIT into the query plan. Different LIMIT and OFFSET values may produce different plans and row orders. To get consistent results across different subsets, enforce a deterministic order with ORDER BY.

Basic query

Retrieve all columns from a table:

SELECT * FROM distributors ORDER BY name;

Join two tables

SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d, films f
WHERE f.did = d.did;

WITH clause (CTE)

WITH distributor_name(name) AS (
    SELECT name FROM distributors
)
SELECT name FROM distributor_name ORDER BY name;

GROUP BY

SELECT kind, sum(length) AS total
FROM films
GROUP BY kind;

GROUP BY with HAVING

SELECT kind, sum(length) AS total
FROM films
GROUP BY kind
HAVING sum(length) < interval '5 hours';

GROUP BY CUBE

SELECT l_returnflag,
       l_shipmode,
       SUM(l_quantity)
FROM public.lineitem
GROUP BY CUBE ((l_returnflag), (l_shipmode))
ORDER BY l_returnflag, l_shipmode;

GROUP BY ROLLUP

SELECT l_returnflag,
       l_shipmode,
       SUM(l_quantity)
FROM public.lineitem
GROUP BY ROLLUP ((l_returnflag), (l_shipmode))
ORDER BY l_returnflag, l_shipmode;

GROUP BY GROUPING SETS

SELECT l_returnflag,
       l_shipmode,
       SUM(l_quantity)
FROM public.lineitem
GROUP BY GROUPING SETS ((l_returnflag, l_shipmode), ())
ORDER BY l_returnflag, l_shipmode;

DISTINCT

SELECT DISTINCT accountid FROM table;

COUNT DISTINCT (exact and approximate)

Exact:

SELECT c1, COUNT(DISTINCT c2) FROM table GROUP BY c1;

Approximate:

SELECT c1, approx_count_distinct(c2) FROM table GROUP BY c1;