Results of 1 - 10 of about 25 for Subquery (0.002 sec.)
- Subqueries 16107
- Subqueries are parenthesized query expressions that appear as part of a larger, outer query. Subqueries are usually based on SELECT ... FROM , but in DuckDB other query constructs such as PIVOT can also appear as a subquery. Scalar Subquery
...bqueries are subqueries that return a single value. They can be used anywhere where an expression can be used. If a scalar subquery returns more than a single value, an error is raised (unless scalar_subquery_error_on_multiple_rows is set to false , in w...We can run the following query to obtain the minimum grade: SELECT min(grade) FROM grades; min(grade) 7 By using a scalar subquery in the WHERE clause, we can figure out for which course this grade was obtained: SELECT course FROM grades WHERE grade = (...ed with ARRAY to collect all results in a list. SELECT ARRAY(SELECT grade FROM grades) AS all_grades; all_grades [7, 9, 8] Subquery Comparisons: ALL , ANY and SOME In the section on scalar subqueries , a scalar expression was compared directly to a subqu... - https://man.mtool.work/duckdb/2.0/sql/expressions/subqueries.html
- IN Operator 9166
- The IN operator checks containment of the left expression inside the collection on the right hand side (RHS). Supported collections on the RHS are tuples, lists, and maps, as well as subqueries that return a single column. The single-column
...(not values): SELECT 'key1' IN MAP {'key1': 50, 'key2': 75}; true SELECT 'key3' IN MAP {'key1': 50, 'key2': 75}; false IN Subquery The IN operator works with subqueries that return a single column. For example: SELECT 42 IN (SELECT unnest([32, 42, 52])...AS x); true If the subquery returns more than one column, a Binder Error is thrown: SELECT 42 IN (SELECT unnest([32, 42, 52]) AS x, 'a' AS y); Binder...Error: Subquery returns 2 columns - expected 1 IN String The IN operator can be used as a shorthand for the contains string function . For... - https://man.mtool.work/duckdb/2.0/sql/expressions/in.html
- FROM and JOIN Clauses 9033
- The FROM clause specifies the source of the data on which the remainder of the query should operate. Logically, the FROM clause is where the query starts execution. The FROM clause can contain a single table, a combination of multiple table
...s that are joined together using JOIN clauses, or another SELECT query inside a subquery node. DuckDB also has an optional FROM -first syntax which enables you to also query without a SELECT statement. Examples...e(100) AS t(i); Select all columns from the CSV file called test.csv : SELECT * FROM 'test.csv'; Select all columns from a subquery: SELECT * FROM (SELECT * FROM tbl); Select the entire row of the table as a struct: SELECT t FROM t; Select the entire row...of the subquery as a struct (i.e., a single column): SELECT t FROM (SELECT unnest(generate_series(41, 43)) AS x, 'hello' AS y) t; Join two... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/from.html
- Describe 8535
- Describing a Table To view the schema of a table, use the DESCRIBE statement (or its alias DESC ) followed by the table name. CREATE TABLE tbl (i INTEGER PRIMARY KEY, j VARCHAR); DESCRIBE tbl; column_name column_type null key default extra
...column_name column_type null key default extra i INTEGER NO PRI NULL NULL j VARCHAR YES NULL NULL NULL Using DESCRIBE in a Subquery DESCRIBE can be used as a subquery. This allows creating a table from the description, for example: CREATE TABLE tbl_descr... - https://man.mtool.work/duckdb/2.0/guides/meta/describe.html
- Lambda Functions 8535
- DuckDB lambda functions (a.k.a. 'lambdas' or 'lambda expressions') are expressions that define an inline anonymous function. Lambdas are passed as argument to certain list functions , which evaluate the passed lambda expression against its
...element. This is always the last parameter. The expression must be a scalar expression. Additionally, it cannot contain a subquery, table function, and the like. The expression may contain macro invocations. However, only scalar macros are allowed, and...only if they do not rely on a subquery. For example, the following are all valid lambda functions: lambda param : param > 1 lambda s : contains(concat(s, 'DB'),...The expression can contain macro calls, but only if the macro is a scalar macro. In addition, the macro must not contain a subquery or rely on another macro that contains a subquery. 3) lambda functions must be defined inline as argument to the list func... - https://man.mtool.work/duckdb/2.0/sql/functions/lambda.html
- QUALIFY Clause 8402
- The QUALIFY clause is used to filter the results of WINDOW functions . This filtering of results is similar to how a HAVING clause filters the results of aggregate functions applied based on the GROUP BY clause . The QUALIFY clause avoids t
...he need for a subquery or WITH clause to perform this filtering (much like HAVING avoids a subquery). An example using a WITH clause instead of Q... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/qualify.html
- Summarize 8335
- The SUMMARIZE command can be used to easily compute a number of aggregates over a table or a query. The SUMMARIZE command launches a query that computes a number of aggregates over all columns ( min , max , approx_unique , avg , std , q25 ,
...215 0.00 l_comment VARCHAR Tiresias zzle? furiously iro 3558599 NULL NULL NULL NULL NULL 6001215 0.00 Using SUMMARIZE in a Subquery SUMMARIZE can be used as a subquery. This allows creating a table from the summary, for example: CREATE TABLE tbl_summary... - https://man.mtool.work/duckdb/2.0/guides/meta/summarize.html
- UPDATE Statement 8219
- The UPDATE statement modifies the values of rows in a table. Examples For every row where i is NULL , set the value to 0 instead: UPDATE tbl SET i = 0 WHERE i IS NULL; Set all values of i to 1 and all values of j to 2: UPDATE tbl SET i = 1,
...matches at most one source row, e.g., by deduplicating the source. In contrast, the sub-select form throws an error if the subquery returns more than one row (unless the scalar_subquery_error_on_multiple_rows setting is set to false ), and MERGE INTO thr... - https://man.mtool.work/duckdb/2.0/sql/statements/update.html
- CREATE MACRO Statement 8020
- The CREATE MACRO statement defines a named, callable SQL expression as a database schema object. Macros may declare parameters, which can be referenced by its expression. Once a macro is created, it may be called by referencing its name and
...Scalar macros evaluate to a scalar value. For scalar macros, the expression can either be a simple expression, or a scalar subquery. * Table macros return a tabular result: when called, they act essentially as table functions and return a table value. Th...e a macro for a CASE expression: CREATE MACRO ifelse(a, b, c) AS CASE WHEN a THEN b ELSE c END; Create a macro that does a subquery: CREATE MACRO one() AS (SELECT 1); Macros are schema-dependent, and have an alias, FUNCTION : CREATE FUNCTION main.my_avg(...b : SELECT a + b AS x; Then, the parameters are replaced by the supplied arguments: SELECT 40 + 2 AS x; Limitations Using Subquery Macros Table macros as well as scalar macros defined using scalar subqueries cannot be used in the arguments of table func... - https://man.mtool.work/duckdb/2.0/sql/statements/create_macro.html
- Profiling Queries 8020
- DuckDB supports profiling queries via the EXPLAIN and EXPLAIN ANALYZE statements. EXPLAIN To see the query plan of a query without executing it, run: EXPLAIN ⟨query⟩; The output of EXPLAIN contains the estimated cardinalities for each opera
...ptimized plan, it can differ substantially from the input query (for example through join reordering, constant folding, or subquery introduction). The generated query preserves the result column names, column order, and logical types, but the exact forma... - https://man.mtool.work/duckdb/2.0/sql/statements/profiling.html
DuckDB 2.0 Documentation / man.mtool.work