comphead commented on code in PR #22672: URL: https://github.com/apache/datafusion/pull/22672#discussion_r3377565544
########## docs/source/user-guide/sql/select.md: ########## @@ -19,76 +19,320 @@ # SELECT syntax -The queries in DataFusion scan data from tables and return 0 or more rows. -Please be aware that column names in queries are made lower-case, but not on the inferred schema. Accordingly, if you -want to query against a capitalized field, make sure to use double quotes. Please see this -[example](https://datafusion.apache.org/user-guide/example-usage.html) for clarification. -In this documentation we describe the SQL syntax in DataFusion. - -DataFusion supports the following syntax for queries: -<code class="language-sql hljs"> - -[ [WITH](#with-clause) with_query [, ...] ] <br/> -[SELECT](#select-clause) [ ALL | DISTINCT ] select_expr [, ...] <br/> -[ [FROM](#from-clause) from_item [, ...] ] <br/> -[ [JOIN](#join-clause) join_item [, ...] ] <br/> -[ [WHERE](#where-clause) condition ] <br/> -[ [GROUP BY](#group-by-clause) grouping_element [, ...] ] <br/> -[ [HAVING](#having-clause) condition] <br/> -[ [QUALIFY](#qualify-clause) condition] <br/> -[ [UNION](#union-clause) [ ALL | select ] <br/> -[ [ORDER BY](#order-by-clause) expression [ ASC | DESC ][, ...] ] <br/> -[ [LIMIT](#limit-clause) count ] <br/> -[ [EXCLUDE | EXCEPT](#exclude-and-except-clause) ] <br/> -[Pipe operators](#pipe-operators) <br/> - -</code> +Queries in DataFusion scan data from tables, subqueries, table functions, or +literal values and return zero or more rows. DataFusion supports the following +general form for `SELECT` queries. Optional clauses can be omitted. The linked +sections describe each clause in more detail. + +<pre><code>[ <a href="#with-clause">WITH</a> cte [, ...] ] +<a href="#select-clause">SELECT</a> select_item [, ...] +[ <a href="#select-into">INTO</a> table_name ] +[ <a href="#from-clause">FROM</a> from_item [, ...] ] +[ <a href="#join-clause">JOIN</a> join_item ... ] +[ <a href="#where-clause">WHERE</a> condition ] +[ <a href="#group-by-clause">GROUP BY</a> grouping_element [, ...] | GROUP BY ALL ] +[ <a href="#having-clause">HAVING</a> condition ] +[ <a href="#window-clause">WINDOW</a> window_name AS (window_definition) [, ...] ] +[ <a href="#qualify-clause">QUALIFY</a> condition ] +[ { <a href="#set-operations">UNION</a> | <a href="#set-operations">INTERSECT</a> | <a href="#set-operations">EXCEPT</a> } query ] [...] +[ <a href="#order-by-clause">ORDER BY</a> order_expression [, ...] ] +[ <a href="#limit-and-offset-clauses">LIMIT</a> count ] [ <a href="#limit-and-offset-clauses">OFFSET</a> count ] +[ <a href="#pipe-operators">|></a> pipe_operator ... ]</code></pre> + +Unquoted identifiers are normalized to lower case in SQL queries, but inferred +schema field names are not changed. If a field name contains capital letters or +other characters that require quoting, reference it with double quotes. See this +[example](https://datafusion.apache.org/user-guide/example-usage.html) for +clarification. ## WITH clause -A with clause allows to give names for queries and reference them by name. +```text +WITH [RECURSIVE] cte_name [(column_name [, ...])] AS (query) [, ...] +``` + +A `WITH` clause defines common table expressions (CTEs) that can be referenced +by name in the rest of the query. + +Examples: ```sql WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a) SELECT a, b FROM x; ``` +CTEs can also rename their output columns: + +```sql +WITH x(key, total) AS ( + SELECT a, SUM(b) FROM t GROUP BY a +) +SELECT key, total FROM x; +``` + +DataFusion supports `WITH RECURSIVE` for recursive CTEs. Recursive CTE support +is controlled by the `datafusion.execution.enable_recursive_ctes` configuration +setting, which is enabled by default. + +```sql +WITH RECURSIVE numbers AS ( + SELECT 1 AS n + UNION ALL + SELECT n + 1 FROM numbers WHERE n < 3 +) +SELECT n FROM numbers; +``` + ## SELECT clause -Example: +```text +SELECT [ALL | DISTINCT | DISTINCT ON (expression [, ...])] + select_item [, ...] + [INTO table_name] +``` + +The `SELECT` list can contain column references, arbitrary expressions, scalar +functions, aggregate functions, window functions, scalar subqueries, and +wildcards. + +Examples: ```sql -SELECT a, b, a + b FROM table +SELECT a, b, a + b AS sum_ab FROM table_name; ``` -The `DISTINCT` quantifier can be added to make the query return all distinct rows. -By default `ALL` will be used, which returns all the rows. +Aliases can be written with or without `AS`: ```sql -SELECT DISTINCT person, age FROM employees +SELECT a AS key, b value FROM table_name; +``` + +`SELECT` can be used without a `FROM` clause when the selected expressions do +not need input rows: + +```sql +SELECT 1 + 2 AS three; +``` + +`SELECT *` requires a `FROM` clause. + +### DISTINCT + +```text +SELECT DISTINCT select_item [, ...] +SELECT DISTINCT ON (expression [, ...]) select_item [, ...] Review Comment: would be nice having example for `DISTINCT ON` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
