neilconway commented on code in PR #22672: URL: https://github.com/apache/datafusion/pull/22672#discussion_r3334644204
########## 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: Thanks, fixed. (Although offhand it seems like an odd restriction to me; we should be able to order by computed expressions just fine...) -- 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]
