martin-g commented on code in PR #22672: URL: https://github.com/apache/datafusion/pull/22672#discussion_r3332959102
########## docs/source/user-guide/sql/select.md: ########## @@ -419,50 +758,107 @@ SELECT FROM table2 ``` +`INTERSECT` returns rows that appear in both inputs. `EXCEPT` returns rows from +the left input that do not appear in the right input. Both support `ALL` and +`DISTINCT`. + +```sql +SELECT a FROM table1 +INTERSECT +SELECT a FROM table2; + +SELECT a FROM table1 +EXCEPT ALL +SELECT a FROM table2; +``` + +`UNION BY NAME` matches columns by name instead of by position. `UNION ALL BY NAME` preserves duplicates, and `UNION DISTINCT BY NAME` removes duplicates. + +```sql +SELECT a, b FROM table1 +UNION BY NAME +SELECT b, a FROM table2; +``` + +Set operations can be followed by `ORDER BY`, `LIMIT`, and `OFFSET` clauses, +which apply to the combined result. + ## ORDER BY clause -Orders the results by the referenced expression. By default it uses ascending order (`ASC`). -This order can be changed to descending by adding `DESC` after the order-by expressions. +```text +ORDER BY order_expression [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...] +``` + +`ORDER BY` sorts query results. Each `order_expression` can be an expression, a +`SELECT` alias, or an ordinal position. The default direction is ascending +(`ASC`). + +If multiple rows have the same values for every `ORDER BY` expression, their +relative order is not specified. Add additional `ORDER BY` expressions to break +ties when the exact row order matters. Examples: ```sql -SELECT age, person FROM table ORDER BY age; -SELECT age, person FROM table ORDER BY age DESC; -SELECT age, person FROM table ORDER BY age, person DESC; +SELECT age, person FROM table_name ORDER BY age; +SELECT age, person FROM table_name ORDER BY age DESC; +SELECT age AS years, person FROM table_name ORDER BY years; +SELECT age, person FROM table_name ORDER BY 1, person DESC; ``` -## LIMIT clause +Use `NULLS FIRST` or `NULLS LAST` to control where null values sort: -Limits the number of rows to be a maximum of `count` rows. `count` should be a non-negative integer. +```sql +SELECT age, person FROM table_name ORDER BY age DESC NULLS LAST; +``` -Example: +With the DuckDB dialect, DataFusion supports `ORDER BY ALL`, which orders by +every expression in the `SELECT` list from left to right: ```sql -SELECT age, person FROM table -LIMIT 10 +SET datafusion.sql_parser.dialect = 'DuckDB'; +SELECT address, zip FROM addresses ORDER BY ALL DESC; +``` + +## LIMIT and OFFSET clauses + +```text +[LIMIT count] +[OFFSET count] ``` -## EXCLUDE and EXCEPT clause +`LIMIT` restricts the number of rows returned. `OFFSET` skips rows before +returning results. The count expressions must be constant expressions that +evaluate to non-negative integers or `NULL`; column references are not allowed. +`NULL` has no effect. -Excluded named columns from query results. +Without an `ORDER BY` clause, `LIMIT` and `OFFSET` operate on an unspecified row +order, so the returned rows are not guaranteed to be deterministic. -Example selecting all columns except for `age` and `person`: +Examples: ```sql -SELECT * EXCEPT(age, person) -FROM table; +SELECT age, person FROM table_name LIMIT 10; +SELECT age, person FROM table_name OFFSET 20; +SELECT age, person FROM table_name LIMIT 10 OFFSET 20; +SELECT age, person FROM table_name OFFSET 20 LIMIT 10; ``` +DataFusion also accepts MySQL-style `LIMIT offset, count`: + ```sql -SELECT * EXCLUDE(age, person) -FROM table; +SELECT age, person FROM table_name LIMIT 20, 10; ``` ## Pipe operators -Some SQL dialects (e.g. BigQuery) support the pipe operator `|>`. -The SQL dialect can be set like this: +```text +query |> pipe_operator [|> pipe_operator ...] +``` + +DataFusion supports BigQuery-style pipe operators (`|>`). The default generic +dialect accepts this syntax, and it is also available when the SQL dialect is Review Comment: https://github.com/apache/datafusion/blob/bb121a8fcdb9ca7fa53ee8bcf175844e2a6df7f3/datafusion/sqllogictest/test_files/pipe_operator.slt#L19 says that the default dialect does not support it yet. ########## docs/source/user-guide/sql/select.md: ########## @@ -419,50 +758,107 @@ SELECT FROM table2 ``` +`INTERSECT` returns rows that appear in both inputs. `EXCEPT` returns rows from +the left input that do not appear in the right input. Both support `ALL` and +`DISTINCT`. + +```sql +SELECT a FROM table1 +INTERSECT +SELECT a FROM table2; + +SELECT a FROM table1 +EXCEPT ALL +SELECT a FROM table2; +``` + +`UNION BY NAME` matches columns by name instead of by position. `UNION ALL BY NAME` preserves duplicates, and `UNION DISTINCT BY NAME` removes duplicates. + +```sql +SELECT a, b FROM table1 +UNION BY NAME +SELECT b, a FROM table2; +``` + +Set operations can be followed by `ORDER BY`, `LIMIT`, and `OFFSET` clauses, +which apply to the combined result. + ## ORDER BY clause -Orders the results by the referenced expression. By default it uses ascending order (`ASC`). -This order can be changed to descending by adding `DESC` after the order-by expressions. +```text +ORDER BY order_expression [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...] +``` + +`ORDER BY` sorts query results. Each `order_expression` can be an expression, a +`SELECT` alias, or an ordinal position. The default direction is ascending +(`ASC`). + +If multiple rows have the same values for every `ORDER BY` expression, their +relative order is not specified. Add additional `ORDER BY` expressions to break +ties when the exact row order matters. Examples: ```sql -SELECT age, person FROM table ORDER BY age; -SELECT age, person FROM table ORDER BY age DESC; -SELECT age, person FROM table ORDER BY age, person DESC; +SELECT age, person FROM table_name ORDER BY age; +SELECT age, person FROM table_name ORDER BY age DESC; +SELECT age AS years, person FROM table_name ORDER BY years; +SELECT age, person FROM table_name ORDER BY 1, person DESC; ``` -## LIMIT clause +Use `NULLS FIRST` or `NULLS LAST` to control where null values sort: -Limits the number of rows to be a maximum of `count` rows. `count` should be a non-negative integer. +```sql +SELECT age, person FROM table_name ORDER BY age DESC NULLS LAST; +``` -Example: +With the DuckDB dialect, DataFusion supports `ORDER BY ALL`, which orders by +every expression in the `SELECT` list from left to right: Review Comment: nit: `every expression` is a bit too broad. If there are non-column expressions, like `a + b`, in the SELECT list then it won't work. -- 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]
