SQL
Clauses
* Creating tables and inserting data:
* [`CREATE OR REPLACE TABLE`](#docs:sql:statements:create_table::create-or-replace): avoid `DROP TABLE IF EXISTS` statements in scripts.
* [`CREATE TABLE ... AS SELECT` (CTAS)](#docs:sql:statements:create_table::create-table--as-select-ctas): create a new table from the output of a table without manually defining a schema.
* [`INSERT INTO ... BY NAME`](#docs:sql:statements:insert::insert-into--by-name): this variant of the `INSERT` statement allows using column names instead of positions.
* [`INSERT OR IGNORE INTO ...`](#docs:sql:statements:insert::insert-or-ignore-into): insert the rows that do not result in a conflict due to `UNIQUE` or `PRIMARY KEY` constraints.
* [`INSERT OR REPLACE INTO ...`](#docs:sql:statements:insert::insert-or-replace-into): insert the rows that do not result in a conflict due to `UNIQUE` or `PRIMARY KEY` constraints. For those that result in a conflict, replace the columns of the existing row to the new values of the to-be-inserted row.
* Describing tables and computing statistics:
* [`DESCRIBE`](#docs:guides:meta:describe): provides a succinct summary of the schema of a table or query.
* [`SUMMARIZE`](#docs:guides:meta:summarize): returns summary statistics for a table or query.
* Making SQL clauses more compact:
* [`FROM`-first syntax with an optional `SELECT` clause](#docs:sql:query_syntax:from::from-first-syntax): DuckDB allows queries in the form of `FROM tbl` which selects all columns (performing a `SELECT *` statement).
* [`GROUP BY ALL`](#docs:sql:query_syntax:groupby::group-by-all): omit the group-by columns by inferring them from the list of attributes in the `SELECT` clause.
* [`ORDER BY ALL`](#docs:sql:query_syntax:orderby::order-by-all): shorthand to order on all columns (e.g., to ensure deterministic results).
* [`SELECT * EXCLUDE`](#docs:sql:expressions:star::exclude-clause): the `EXCLUDE` option allows excluding specific columns from the `*` expression.
* [`SELECT * REPLACE`](#docs:sql:expressions:star::replace-clause): the `REPLACE` option allows replacing specific columns with different expressions in a `*` expression.
* [`UNION BY NAME`](#docs:sql:query_syntax:setops::union-all-by-name): perform the `UNION` operation along the names of columns (instead of relying on positions).
* Transforming tables:
* [`PIVOT`](#docs:sql:statements:pivot) to turn long tables to wide tables.
* [`UNPIVOT`](#docs:sql:statements:unpivot) to turn wide tables to long tables.
* Defining SQL-level variables:
* [`SET VARIABLE`](#docs:sql:statements:set::set-variable)
* [`RESET VARIABLE`](#docs:sql:statements:set::reset-variable)