Shape / table · All operations

Run a SQL query

Run a SQL query over the table.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

A query over the table, reading the step before it as a CTE chain

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
WITH north AS (SELECT * FROM orders WHERE region = 'north'),
     by_product AS (SELECT product, sum(amount) AS total FROM north GROUP BY product)
SELECT * FROM by_product ORDER BY total DESC;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 3 AND sum(total) = 420.5 FROM result)) THEN 'as expected' ELSE error('the chain did not total 420.50 over three products') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the chain did not total 420.50 over three products”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache Hop (1)
  • Spark SQLread 2026-09-27

    Run one SQL statement over the incoming Spark Datasets as a single Catalyst query

Apache NiFi (1)
  • QueryRecordread 2026-09-27

    Run SQL (Apache Calcite) over the records of a FlowFile; each query is its own output relationship

Apache SeaTunnel (2)
  • Calciteread 2026-09-27

    Next docs, not in 2.3.13: row-by-row SQL transform compiled by Apache Calcite

  • SQLread 2026-09-27

    Transform rows with a SQL query on an in-memory engine; joins and aggregation are not supported

AWS Glue (1)
  • SQLread 2026-09-27

    Transform the data with a Spark SQL query

Bruin (1)
  • SQL assets (family)read 2026-09-27

    19 platform types, one per warehouse, each a SQL query run there: bq.sql, sf.sql, pg.sql, duckdb.sql, athena.sql, …

DuckDB (6)
  • CREATE VIEWread 2026-09-27

    Name a query so later queries read it as a table

  • SELECTread 2026-09-27

    Run a query

  • WITH (clause)read 2026-09-27

    Name sub-queries for the rest of the statement; WITH RECURSIVE repeats one until it adds nothing

  • json_execute_serialized_sqlread 2026-09-27

    json_execute_serialized_sql table function

  • queryread 2026-09-27

    query table function

  • query_tableread 2026-09-27

    query_table table function

Ibis (1)
  • SQLStringViewread 2026-09-27

    Run a SQL string over a table expression and use the result as a table

Polars (4)
  • DataFrame.sqlread 2026-09-27

    Run a SQL query against the frame

  • SQL query clauses (group)read 2026-09-27

    16 clauses: SELECT, DISTINCT, DISTINCT ON, FROM, JOIN and others; the SELECT grammar the SQL interface accepts

  • SQLContextread 2026-09-27

    7 methods: execute, execute_global, register, register_many, unregister and others; run SQL over registered frames

  • sqlread 2026-09-27

    Run a SQL query against the frames found in the calling scope

SQLMesh (1)
  • SQL modelsread 2026-09-27

    A MODEL DDL, optional pre/post-statements and one query, written in SQL or Python returning SQL

Background

The SQL SELECT statement returns a result set of rows from one or more tables, specifying the result wanted but not how to calculate it.[1] A query leaves the database management system (DBMS) to plan, optimise and perform the physical operations that produce its result.[1] Standard SELECT statements have no persistent effects on the database.[1]

When to use it

The SELECT statement has optional clauses that choose the columns returned, filter rows (WHERE), group rows for aggregation (GROUP BY), filter groups (HAVING) and order the result (ORDER BY).[1] Reach for a SQL query when a step needs several of these at once, or when the logic reads more plainly as one statement than as a chain of single operations. (Meridian's view) Where one ready-made operation does the job, such as a filter or a rename, the single operation says what the step does more plainly than a query that does the same. (Meridian's view)

Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.[1] When a later step or a reader depends on row order, end the query with an ORDER BY that names it. (Meridian's view) Some non-standard implementations of SELECT can have persistent effects, such as the SELECT INTO syntax provided in some databases.[1]

Despite the existence of the SQL standard, SQL code is not completely portable among different database systems without adjustments.[2][3] Write the query for the engine that will run it, and test it there, rather than assume a statement written for one database runs unchanged on another. (Meridian's view)

The method

SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce after learning about the relational model from Edgar F. Codd in the early 1970s.[3] IBM's first version of SQL, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's System R.[3] SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987.[3] The syntax of SQL is defined as part of ISO/IEC 9075, a standard that is not freely available.[2]

SQL is a declarative language: the database translates a SELECT into a query plan, which may vary between executions, database versions and database software.[1] Under ANSI SQL, the ORDER BY step returns a cursor rather than a table, because ANSI defines a cursor as an ordered set of rows.[1] In DuckDB, the SELECT clause appears first in the statement, but logically its expressions are executed only at the end.[4] In DuckDB, the canonical order of a SELECT statement is SELECT, FROM, USING SAMPLE, WHERE, GROUP BY, HAVING, WINDOW, QUALIFY, ORDER BY and LIMIT, optionally prefixed with a WITH clause.[4]

Since 1999 the SQL standard has allowed WITH clauses, named subqueries often called common table expressions.[1] A WITH clause can also be recursive, referring to itself, which allows tree or graph traversals and, more generally, fixpoint computations.[1] In PostgreSQL, the auxiliary statements in a WITH clause can be thought of as defining temporary tables that exist just for one query.[5] DuckDB handles common table expressions as materialised by default, evaluating each once and storing the result in a temporary table, though under certain conditions it can inline one into the main query.[6]

Compared with the alternatives

A view is the result set of a stored query, a virtual table computed from data in the database when access to the view is requested.[7] Changes applied to the data in an underlying table are reflected in the data a view shows on its next use.[7] In DuckDB, a view is not physically materialised: the query behind it is run every time the view is referenced in a query.[8] In DuckDB, regular (non-recursive) common table expressions are essentially views that are limited in scope to a particular query.[6] Materialised views are pre-executed, non-virtual views, commonly used in data warehousing, that give a static snapshot of the data.[7] Name a stage in a WITH clause when only one query needs it, and make it a view when several queries should read the same definition. (Meridian's view)

A derived table is a subquery in a FROM clause, which lets the query reference the subquery as a table; it is also called an inline view.[1] Joins and other table operations are, in many cases, faster alternatives to subqueries, depending on the implementation.[1]

Sources

  1. [1]Select (SQL)secondary source
  2. [2]SQL syntaxsecondary source
  3. [3]SQLsecondary source
  4. [4]SELECT Statementdocumentation
  5. [5]PostgreSQL: Documentation: 18: 7.8. WITH Queries (Common Table Expressions)documentation
  6. [6]WITH Clausedocumentation
  7. [7]View (SQL)secondary source
  8. [8]CREATE VIEW Statementdocumentation