--- title: Results description: Results sidebar_class_name: cache-icon feature_stage: preview --- **RESULT** provides asynchronous query execution with a transparent cache. Create a RESULT to run a SELECT in the background, then query it like a table while controlling its lifecycle (pause, resume, cancel, drop). You can think of a result as a view with an attached cache that is used whenever possible to speed up queries. Results are stored in memory and will only remain visible until your client-side DuckDB session is restarted. For the SQL syntax reference, see [`RESULT`](/sql-reference/motherduck-sql-reference/result). ## Core concepts ### What is a RESULT? ```sql CREATE RESULT AS ; FROM SELECT ...; ``` A RESULT is a named relation in your DuckDB database that: - Runs the provided `SELECT` in the background (creation is non-blocking) - Caches rows produced by that statement as it runs - Provides lifecycle management (pause, resume, cancel, drop) - Can be queried like a regular table - Maintains execution state and progress information ### Result states Results can be in one of three states: - **BUILDING**: Query is actively running and appending rows to the cache - **PAUSED**: Query execution is temporarily paused - **DONE**: Query execution has completed, which can occur for three reasons: 1. Query finished successfully 2. Query was preemptively stopped (e.g., aborted by the user) 3. Query encountered an error ## Interacting with results ### Creating results When you create a RESULT, the provided `SELECT` starts running in the background. You can query the result like a normal table at any time. results, you can query the result just like you would query a normal table. ```sql -- Basic syntax CREATE RESULT AS ; -- With conflict resolution CREATE RESULT IF NOT EXISTS AS ; CREATE OR REPLACE RESULT AS ; -- Accessing the result FROM LIMIT ; ``` ### Accessing results You can query a result like a table. The relation appears quickly after creation, although the background `SELECT` may still be running. query creating the result has completed successfully. This occurs very quickly and does not mean that the `SELECT` statement associated with the result has completed running. ```sql FROM LIMIT ; ``` There is **no guarantee** the cache is complete when you query a result. Depending on the state of the `RESULT` and your query, the system may read from the cache, wait for additional rows, or bypass the cache and re-run the original `SELECT`. The decision tree below shows how the `FROM my_result LIMIT 100` accessing the RESULT `my_result` behaves. ```mermaid flowchart TD start(("FROM my_result LIMIT 100")):::circle -->|Completed successfully| cache(((Read from cache))):::circle start -->|"RESULT is not running (PAUSED/DONE with error)"| enough start -->|RESULT is BUILDING| enough_building enough_building{"Has enough data?
(cache > 100)"}:::green -->|Yes| cache enough_building -->|No| access_limit access_limit{"access limit < 500,000
(100 < 500,000)"}:::green -->|Yes| delay access_limit -->|No| rerun delay(Wait for 100 rows in cache
or result complete) --> cache enough{"Has enough data?
(cache > 100) OR DONE without error?"}:::green -->|Yes| cache enough -->|No| rerun(((Re-run query))):::circle ``` ### Lifecycle management On creation, new results start in the **BUILDING** state. While building, you can **PAUSE**, **RESUME**, **CANCEL**, or **DROP** the result. Pause suspends execution, resume continues from where it stopped. Cancel stops the job permanently and it cannot be resumed. Canceled results can still be queried, but they will not append any new rows to the cache. When a result is dropped, it is permanently deleted and can no longer be queried. Dropping a result also removes its associated cache. ```mermaid stateDiagram-v2 [*] --> BUILDING: Result Created BUILDING --> PAUSED: PAUSE RESULT PAUSED --> BUILDING: RESUME RESULT BUILDING --> DONE: SELECT statement completes BUILDING --> DONE: CANCEL RESULT PAUSED --> DONE: CANCEL RESULT note right of BUILDING Query is actively running end note note right of PAUSED Query execution paused. Can be resumed. end note note right of DONE Execution finished: completed, error, or canceled. end note note left of DONE PAUSE/RESUME will error when in DONE state end note ``` #### Pause result ```sql PAUSE RESULT ; PAUSE RESULT IF EXISTS ; ``` #### Resume result ```sql RESUME RESULT ; RESUME RESULT IF EXISTS ; ``` #### Cancel result ```sql CANCEL RESULT ; CANCEL RESULT IF EXISTS ; ``` #### Drop result ```sql DROP RESULT ; DROP RESULT IF EXISTS ; ``` ### Introspecting results Use `SHOW ALL RESULTS` to list all your results alongside their status and progress. The returned table also includes: 1. `name`: The name of the result 2. `error`: Any error message associated with the result (is empty if no error occurred) 3. `status`: The current status of the result (BUILDING, PAUSED, DONE) 4. `row_count`: The number of rows in the result cache. This grows as the result builds and is not stable within the same transaction (it will increase as the result is being built). ```sql SHOW ALL RESULTS; --| name | error | status | row_count | --|-------|---------------------------------------------------------------------|----------|-----------| --| foo | (empty) | DONE | 100,000 | --| bar | INTERRUPT Error: The RESULT "bar" has been manually canceled. | DONE | 10,000 | --| hello | (empty) | PAUSED | 1,000 | --| world | (empty) | BUILDING | 100 | ``` If you want to order the results, filter them or limit the output you can use the `MD_SHOW_RESULTS` table function: ```sql FROM MD_SHOW_RESULTS() WHERE name = 'foo'; --| name | error | status | row_count | --|------|---------|--------|-----------| --| foo | (empty) | DONE | 100,000 | ``` ## Best practices - Use `LIMIT` when you need only a small sample so that `RESULT` can serve them quickly from the cache. - Prefer deterministic `SELECT` statements for predictable caching and reuse. - Pause or cancel long-running results you do not need immediately and remember to drop them when no longer in use. ## Notes and limitations - `RESULT` accepts `SELECT` statements only. - The cache may be partial while the result is building. Queries may wait briefly, use the cache, or re-run the `SELECT`. - A canceled result cannot be resumed. - Results are stored in memory and will not persist across client restarts. ## See also - [Building data applications with MotherDuck](https://motherduck.com/blog/building-data-applications-with-motherduck/) - [MotherDuck wasm npm package](https://www.npmjs.com/package/@motherduck/wasm-client?activeTab=readme) - [MotherDuck wasm example repository](https://github.com/motherduckdb/wasm-client)