Results of 1 - 10 of about 38 for Clauses (0.002 sec.)
- Friendly SQL 12311
- DuckDB offers several advanced SQL features and syntactic sugar to make SQL queries more concise. We refer to these colloquially as “friendly SQL”. Several of these features were first introduced by DuckDB, while some are inspired by other
...riendly SQL 2026 Calendar with a short explanation, an example, and an abstract illustration for 12 friendly SQL features. Clauses Creating tables and inserting data: CREATE OR REPLACE TABLE : avoid DROP TABLE IF EXISTS statements in scripts. CREATE TAB...uccinct summary of the schema of a table or query. SUMMARIZE : returns summary statistics for a table or query. Making SQL clauses more compact and readable: FROM -first syntax with an optional SELECT clause : DuckDB allows queries in the form of FROM t...rm the UNION operation along the names of columns (instead of relying on positions). Prefix aliases in the SELECT and FROM clauses : write x: 42 instead of 42 AS x for improved readability. Specifying a percentage of the table size for the LIMIT clause... - https://man.mtool.work/duckdb/2.0/sql/dialect/friendly_sql.html
- ALTER TABLE Statement 11871
- The ALTER TABLE statement changes the schema of an existing table in the catalog. Examples CREATE TABLE integers (i INTEGER, j INTEGER); Add a new column with name k to the table integers , it will be filled with the default value NULL : AL
...NAME TO ⟨new_name⟩ continues to work as well. RENAME COLUMN To rename a column of a table, use the RENAME or RENAME COLUMN clauses: ALTER TABLE integers RENAME COLUMN i TO j; ALTER TABLE integers RENAME i TO j; The RENAME [COLUMN] clause renames a singl...t rely on this column name are not automatically updated. ADD COLUMN To add a column of a table, use the ADD or ADD COLUMN clauses. E.g., to add a new column with name k to the table integers , it will be filled with the default value NULL : ALTER TABLE...pe⟩ , you can also use the equivalent ALTER ⟨column_name⟩ SET TYPE ⟨type⟩ and the ALTER ⟨column_name⟩ SET DATA TYPE ⟨type⟩ clauses. Change the type of the column i to the type VARCHAR , using the specified expression to convert the data for each row: AL... - https://man.mtool.work/duckdb/2.0/sql/statements/alter_table.html
- Window Functions 10990
- DuckDB supports window functions , which can use multiple rows to calculate a value for each row. Window functions are blocking operators , i.e., they require their entire input to be buffered, making them one of the most memory-intensive o
...(amount) OVER (PARTITION BY region) FROM sales; Syntax Window functions can be used in the SELECT , ORDER BY , and QUALIFY clauses. To share OVER specifications between functions, use the statement's WINDOW clause and use the OVER ⟨window_name⟩ syntax....ult) Exclusion is implemented for both windowed aggregates as well as for the first , last and nth_value functions. WINDOW Clauses Multiple different OVER clauses can be specified in the same SELECT , and each will be computed separately. Often, however...E BETWEEN INTERVAL 1 DAYS PRECEDING AND INTERVAL 1 DAYS FOLLOWING) ORDER BY 1, 2; The queries above do not use a number of clauses commonly found in select statements, like WHERE , GROUP BY , etc. For more complex queries you can find where WINDOW claus... - https://man.mtool.work/duckdb/2.0/sql/functions/window_functions.html
- SELECT Statement 10550
- The SELECT statement retrieves rows from the database. Examples Select all columns from the table tbl : SELECT * FROM tbl; Select the rows from tbl : SELECT j FROM tbl WHERE i = 3; Perform an aggregate grouped by the column i : SELECT i, su
...LECT statement retrieves rows from the database. The canonical order of a SELECT statement is as follows, with less common clauses being indented: SELECT ⟨select_list⟩ FROM ⟨tables⟩ USING SAMPLE ⟨sample_expression⟩ WHERE ⟨condition⟩ GROUP BY ⟨groups⟩ HA...ntax diagrams into several parts. The full syntax diagram can be found at the bottom of the page. SELECT Clause The SELECT clause specifies the list of columns that will be returned by the query. While it appears first in the clause, logically the expre...are returned. Column names are case-insensitive. See the Rules for Case Sensitivity for more details. FROM Clause The FROM clause specifies the source of the data on which the remainder of the query should operate. Logically, the FROM clause is where th... - https://man.mtool.work/duckdb/2.0/sql/statements/select.html
- PIVOT Statement 10110
- The PIVOT statement allows distinct values within a column to be separated into their own columns. The values within those new columns are calculated using an aggregate function on the subset of rows that match each distinct value. DuckDB i
...NG ⟨values⟩ GROUP BY ⟨rows⟩ ORDER BY ⟨columns_with_order_directions⟩ LIMIT ⟨number_of_rows⟩; The ON , USING , and GROUP BY clauses are each optional, but they may not all be omitted. Example Data All examples use the dataset produced by the queries belo...Use the PIVOT statement below to create a separate column for each year and calculate the total population in each. The ON clause specifies which column(s) to split into separate columns. It is equivalent to the columns parameter in a spreadsheet pivot...esult that is identical to the one above: PIVOT cities ON year USING first(population); Note The SQL syntax permits FILTER clauses with aggregate functions in the USING clause. In DuckDB, the PIVOT statement currently does not support these and they are... - https://man.mtool.work/duckdb/2.0/sql/statements/pivot.html
- Order Preservation 9670
- For many operations, DuckDB preserves the order of rows, similarly to data frame libraries such as Pandas. Example Take the following table for example: CREATE TABLE tbl AS SELECT * FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c')) t(x, y); SELECT
...the row (1, 'a') occurs before (3, 'c') in the original table, it is guaranteed to come before that row in this table too. Clauses The following clauses guarantee that the original row order is preserved: COPY (see Insertion Order ) FROM with a single t... - https://man.mtool.work/duckdb/2.0/sql/dialect/order_preservation.html
- Pivot Internals 9229
- PIVOT Pivoting is implemented as a combination of SQL query re-writing and a dedicated PhysicalPivot operator for higher performance. Each PIVOT is implemented as set of aggregations into lists and then the dedicated PhysicalPivot operator
...ed to find the distinct values that should become columns. Each ENUM is then injected into one of the PIVOT statement's IN clauses. After the IN clauses have been populated with ENUM s, the query is re-written again into a set of aggregations into lists... - https://man.mtool.work/duckdb/2.0/internals/pivot.html
- SQL Quirks 8789
- Like all programming languages and libraries, DuckDB has its share of idiosyncrasies and inconsistencies. Some are vestiges of our feathered friend's evolution; others are inevitable because we strive to adhere to the SQL Standard and speci
...isting keys ( map_extract returns an empty list). struct_extract throws an error because keys of structs are like columns. Clauses Automatic Column Deduplication in SELECT Column names are deduplicated with the first occurrence shadowing the others: CRE...sired column a in file.parquet . USING SAMPLE The USING SAMPLE clause is syntactically placed after the WHERE and GROUP BY clauses (same as the LIMIT clause) but is semantically applied before both (unlike the LIMIT clause). Implicit Conversion of Ident... - https://man.mtool.work/duckdb/2.0/sql/dialect/sql_quirks.html
- CREATE TRIGGER Statement 8789
- New The CREATE TRIGGER feature was introduced in DuckDB 2.0. The CREATE TRIGGER statement registers a trigger: a set of SQL statements that run automatically in response to INSERT , UPDATE , or DELETE events on a table. Examples Record ever
...ent that modifies a table in an audit table using a statement-level AFTER trigger. The REFERENCING OLD TABLE and NEW TABLE clauses expose the rows before and after the change as transition tables: CREATE TABLE target (id INTEGER, val INTEGER); CREATE TA...xists on the table. Without either clause, creating a trigger with a name that already exists results in an error. The two clauses cannot be combined. Multiple Triggers A table can have multiple triggers for the same event, as long as they have the same... - https://man.mtool.work/duckdb/2.0/sql/statements/create_trigger.html
- Star Expression 8129
- Syntax The * expression can be used in a SELECT statement to select all columns that are projected in the FROM clause. SELECT * FROM tbl; TABLE.* and STRUCT.* The * expression can be prepended by a table name to select only columns from tha
...ROM numbers; id number 2 20 4 40 6 NULL COLUMNS Expression in a WHERE Clause COLUMNS expressions can also be used in WHERE clauses. The conditions are applied to all columns and are combined using the logical AND operator. SELECT * FROM ( SELECT 'a', 'a...valent to: x = 'a' OR y = 'a' x y a a a b COLUMNS Expression in DISTINCT ON COLUMNS expressions can be used in DISTINCT ON clauses to specify distinct columns by pattern: SELECT DISTINCT ON (COLUMNS('x|y')) * FROM (VALUES (1, 2, 'a'), (1, 2, 'b'), (3, 4... - https://man.mtool.work/duckdb/2.0/sql/expressions/star.html
DuckDB 2.0 Documentation / man.mtool.work