MatrixOrigin MatrixOrigin Docs
MatrixOne Intelligence MatrixOne
Get Started Guides Developer Reference Release Notes Tutorials
Contents Menu Expand Light mode Dark mode Auto light/dark, in light mode Auto light/dark, in dark mode Skip to content
MatrixOne Intelligence Docs
MatrixOne Intelligence Docs

Reference

  • Reference
    • SQL reference
      • Structure and Syntax
        • Keywords
        • Comments
      • Data Types
        • Data Types Overview
        • Data Type Conversion
        • Date and Time Types
          • TIMESTAMP and DATETIME Auto-Initialization and Update
          • YEAR Type
        • JSON Data Type
        • BLOB and TEXT Data Types
        • DATALINK Data Type
        • ENUM Type
        • UUID Data Type
        • Fixed-Point Numeric Types (DECIMAL)
        • Vector Data Type
      • SQL Statements
        • SQL Statement Categories
        • Data Definition Language (DDL)
          • CREATE DATABASE
          • CREATE INDEX
          • CREATE INDEX...USING IVFFLAT
          • CREATE TABLE
          • CREATE TABLE AS SELECT
          • CREATE EXTERNAL TABLE
          • CREATE PITR
          • CREATE PUBLICATION
          • CREATE SEQUENCE
          • CREATE STAGE
          • CREATE...FROM...PUBLICATION...
          • CREATE VIEW
          • CREATE FUNCTION...LANGUAGE SQL AS
          • CREATE SOURCE
          • CREATE DYNAMIC TABLE
          • CREATE SNAPSHOT
          • ALTER TABLE
          • ALTER TABLE ... ALTER REINDEX
          • ALTER PITR
          • ALTER PUBLICATION
          • ALTER SEQUENCE
          • ALTER STAGE
          • ALTER VIEW
          • DROP DATABASE
          • DROP INDEX
          • DROP TABLE
          • DROP PITR
          • DROP PUBLICATION
          • DROP SEQUENCE
          • DROP STAGE
          • DROP SNAPSHOT
          • DROP VIEW
          • DROP FUNCTION
          • TRUNCATE TABLE
          • RENAME TABLE
          • RESTORE PITR
          • RESTORE ACCOUNT
          • RESTORE SNAPSHOT
        • Data Manipulation Language (DML)
          • INSERT
          • INSERT INTO SELECT
          • DELETE
          • UPDATE
          • LOAD DATA INFILE
          • LOAD DATA INLINE
          • UPSERT
            • UPSERT Overview
            • INSERT ON DUPLICATE KEY UPDATE
            • INSERT IGNORE
            • REPLACE
          • Information Functions
            • LAST_QUERY_ID()
            • LAST_INSERT_ID()
        • Data Query Language (DQL)
          • SELECT
          • SUBQUERY
            • SUBQUERY Overview
            • Derived Tables
            • Subqueries with Comparison Operators
            • SUBQUERY with ANY or SOME
            • SUBQUERY with ALL
            • SUBQUERY with EXISTS
            • SUBQUERY with IN
          • JOIN
            • JOIN Overview
            • INNER JOIN
            • LEFT JOIN
            • RIGHT JOIN
            • FULL JOIN
            • OUTER JOIN
            • NATURAL JOIN
          • With CTE
          • Union Queries
            • UNION
            • INTERSECT
            • MINUS
            • UNION / INTERSECT / MINUS Overview
        • Data Control Language (DCL)
          • CREATE ROLE
          • CREATE USER
          • ALTER USER
          • DROP USER
          • DROP ROLE
          • GRANT
          • REVOKE
        • Transactions (TCL)
          • START TRANSACTION / BEGIN
          • COMMIT
          • ROLLBACK
          • SET AUTOCOMMIT
        • Others
          • SHOW
            • SHOW ACCOUNTS
            • SHOW DATABASES
            • SHOW CREATE TABLE
            • SHOW CREATE VIEW
            • SHOW CREATE PUBLICATION
            • SHOW TABLES
            • SHOW INDEX
            • SHOW COLLATION
            • SHOW COLUMNS
            • SHOW FUNCTION STATUS
            • SHOW GRANTS
            • SHOW PROCESSLIST
            • SHOW PUBLICATIONS
            • SHOW PITR
            • SHOW ROLES
            • SHOW SEQUENCES
            • SHOW SNAPSHOTS
            • SHOW STAGES
            • SHOW SUBSCRIPTIONS
            • SHOW VARIABLES
          • SET
            • SET ROLE
          • USE
            • USE DATABASE
          • KILL
          • Prepared
            • PREPARE
            • EXECUTE
            • DEALLOCATE
          • Explain
            • Explain
            • EXPLAIN output format
            • Explain Analyze
      • Functions
        • Function List
        • Aggregate Functions
          • ANY_VALUE
          • AVG
          • BITMAP
          • BIT_AND
          • BIT_OR
          • BIT_XOR
          • COUNT
          • GROUP_CONCAT
          • MAX
          • MEDIAN
          • MIN
          • STDDEV_POP
          • SUM
          • VARIANCE
          • VAR_POP
        • Date and Time Functions
          • CONVERT_TZ()
          • CURDATE()
          • CURRENT_TIMESTAMP()
          • DATE()
          • DATE_ADD()
          • DATE_FORMAT()
          • DATE_SUB()
          • DATEDIFF()
          • DAY()
          • DAYOFYEAR()
          • EXTRACT()
          • HOUR()
          • FROM_UNIXTIME
          • MINUTE()
          • MONTH()
          • NOW()
          • SECOND()
          • STR_TO_DATE()
          • SYSDATE()
          • TIME()
          • TIMEDIFF()
          • TIMESTAMP()
          • TIMESTAMPDIFF()
          • TO_DATE()
          • TO_DAYS()
          • TO_SECONDS()
          • UNIX_TIMESTAMP
          • UTC_TIMESTAMP()
          • WEEK()
          • WEEKDAY()
          • YEAR()
        • Mathematical Functions
          • ABS()
          • ACOS()
          • ATAN()
          • CEIL()
          • CEILING()
          • COS()
          • COT()
          • EXP()
          • FLOOR()
          • LN()
          • LOG()
          • LOG2()
          • LOG10()
          • PI()
          • POWER()
          • ROUND()
          • RAND()
          • SIN()
          • SINH()
          • TAN()
        • String Functions
          • BIN()
          • BIT_LENGTH()
          • CHAR_LENGTH()
          • CONCAT()
          • CONCAT_WS()
          • EMPTY()
          • ENDSWITH()
          • FIELD()
          • FIND_IN_SET()
          • FORMAT()
          • FROM_BASE64()
          • HEX()
          • INSTR()
          • LCASE()
          • LEFT()
          • LENGTH()
          • LOCATE()
          • LOWER()
          • LPAD()
          • LTRIM()
          • MD5()
          • OCT()
          • REPEAT()
          • REVERSE()
          • RPAD()
          • RTRIM()
          • SHA1()/SHA()
          • SHA2()
          • SPACE()
          • SPLIT_PART()
          • STARTSWITH()
          • SUBSTRING()
          • SUBSTRING_INDEX()
          • TO_BASE64()
          • TRIM()
          • UCASE()
          • UNHEX()
          • UPPER()
          • Regular Expression Overview
            • Regular expression overview
            • NOT REGEXP
            • REGEXP_INSTR()
            • REGEXP_LIKE()
            • REGEXP_REPLACE()
            • REGEXP_SUBSTR()
        • Vector Functions
          • Basic Operators
          • Mathematical Calculations
          • CLUSTER_CENTERS()
          • COSINE_SIMILARITY()
          • COSINE_DISTANCE()
          • INNER_PRODUCT()
          • L1_NORM()
          • L2_NORM()
          • L2_DISTANCE()
          • NORMALIZE_L2()
          • SUBVECTOR()
          • VECTOR_DIMS()
        • Table Functions
          • GENERATE_SERIES()
          • UNNEST()
        • Window Functions
          • DENSE_RANK()
          • RANK()
          • ROW_NUMBER()
        • JSON Functions
          • JQ()
          • JSON_EXTRACT()
          • JSON_EXTRACT_FLOAT64()
          • JSON_EXTRACT_STRING()
          • JSON_QUOTE()
          • JSON_ROW()
          • JSON_UNQUOTE()
          • TRY_JQ()
        • Other Functions
          • LOAD_FILE
          • SAVE_FILE
          • SAMPLE
          • SERIAL_EXTRACT
          • SLEEP
          • STAGE_LIST
          • UUID()
        • System Operations Functions
          • CURRENT_ROLE_NAME()
          • CURRENT_ROLE()
          • CURRENT_USER_NAME()
          • CURRENT_USER()
          • VERSION()
          • PURGE_LOG()
      • Operators
        • INTERVAL
        • Operators Overview
        • Operator Precedence
        • Arithmetic Operators
          • Arithmetic Operators Overview
          • %,MOD
          • *
          • +
          • - (subtraction)
          • - (unary minus)
          • /
          • DIV
        • Assignment Operators
          • Assignment Operators Overview
          • =
        • Bitwise Operators
          • Bitwise Operators Overview
          • &
          • >>
          • <<
          • ^
          • |
          • ~
        • Cast Functions and Operators
          • Cast Functions and Operators Overview
          • BINARY
          • CAST
          • CONVERT
          • DECODE
          • ENCODE
          • SERIAL
          • SERIAL_FULL
        • Comparison Functions and Operators
          • Comparison Functions and Operators Overview
          • >
          • >=
          • <
          • <>,!=
          • <=
          • =
          • BETWEEN ... AND ...
          • IN
          • IS
          • IS NOT
          • IS NOT NULL
          • IS NULL
          • ISNULL
          • ILIKE
          • LIKE
          • NOT BETWEEN ... AND ...
          • NOT IN
          • NOT LIKE
          • COALESCE
        • Control Flow Functions
          • Control Flow Functions Overview
          • CASE WHEN
          • IF
          • IFNULL
          • NULLIF
        • Logical Operators
          • Logical Operators Overview
          • AND,&&
          • NOT,!
          • OR
          • XOR
      • System Variables
        • System Variables Overview
        • System Variable Parameters
          • SQL Mode
          • Query Result Saving Support
          • Timezone Support
          • Case Sensitivity Support
          • Foreign Key Check Support
          • Query Result Column Name Consistency
          • Illegal Login Restrictions
          • Password Complexity Check
          • Connection Whitelist
        • Custom Variables
      • System Tables
      • Privilege Categories
      • Limitations
        • MatrixOne JDBC Feature Support List
        • MatrixOne DDL Statement Partition Support
        • MatrixOne Load Data Support
    • API reference
      • AI Studio
        • Workspaces
          • List workspaces
          • Submit a workspace-creation task
          • List workspace-creation tasks
          • Create a workspace
          • Claim a workspace
          • Update a workspace
          • Accept a workspace invitation
          • Delete a workspace
        • Data connections
          • Connectors
            • List connectors
            • Get connector details
            • Create a connector
            • Update a connector
            • Delete a connector
            • Validate connector configuration
          • Connector files
            • List files
            • Upload files
            • Preview a file
            • Download a file
            • Delete a file
          • Browse databases
            • View browsable connectors
            • List databases
            • List database schemas
            • List tables
            • Get table schema
          • Import tasks
            • List tasks
            • Get task details
            • Import task object fields
            • Create a task
            • Upload locally and create an import task
            • Create a database structured-load task
            • Resolve a structured-load backfill start
            • Update a task
            • Delete a task
            • Pause a task
            • Resume a task
            • List task files
            • List run records
            • Retry failed files
          • Export tasks
            • List tasks
            • Get task details
            • Create a task
            • Delete a task
            • List export files
            • Get task status
            • Rerun a task
            • Configure an export target
            • Check an export target table name
            • Generate export target column mappings
        • Data processing
          • Workflow
            • Create workflow
            • View workflow list
            • View workflow details
            • Update workflow
            • Pause workflow
            • Resume workflow
            • Delete workflow
          • Workflow template
            • View workflow template list
            • View workflow template details
            • Create workflow template
            • Update workflow template
            • Delete workflow template
          • Workflow job
            • Start workflow job
            • View workflow jobs
            • View workflow job details
            • Refresh workflow job status
            • View workflow job results
            • View workflow job node
            • Pause workflow job
            • Resume workflow job
            • Cancel workflow job
            • Delete workflow job
            • View file execution records
          • Workflow rerun
            • View node rerun plan
            • Rerun from a specified node
            • View workflow reruns
            • View workflow rerun details
            • Cancel workflow rerun
          • Workflow retry
            • Retry workflow job
            • Batch retry workflow jobs
          • Custom operator
            • View custom operator list
            • View custom operator details
            • View custom operator code
            • Create custom operator
            • Update custom operator
            • Test-run custom operator
            • Enable custom operator
            • Disable custom operator
            • Delete custom operator
          • Operator directory
            • View workflow operator list
            • View workflow operator details
          • Operator API service
            • View operator API service configuration
            • Publish operator API service
            • Invoke operator API service
            • Enable operator API service
            • Disable operator API service
          • Data lineage
            • View file processing lineage
            • View processing result lineage
            • View processing node details
            • View processing result output contents
            • View processing result output version list
            • Create processing result output version
            • Set processing result effective version
            • View node output contents
            • View node output version list
            • Create node output version
            • Set node effective version
            • Rerun processing node
            • Rerun processing node with specified output version
          • SQL execution
            • Execute SQL
            • View execution status
            • Cancel execution
            • View execution result
            • Download execution result
          • SQL metadata
            • View database list
            • View tables and views in a database
            • View table information
            • View table column list
            • View table or view creation statement
            • Delete database
            • Delete table
            • Delete view
          • SQL workbook
            • View SQL workbook list
            • View SQL workbook version content
            • Create SQL workbook
            • Update SQL workbook name
            • Delete SQL workbook
            • View SQL workbook version list
            • Create SQL draft version
            • Update SQL draft version
            • Finalize SQL version
          • Data dashboard
            • View data dashboard list
            • View data dashboard details
            • Create data dashboard
            • Update data dashboard
            • Delete data dashboard
            • Create dashboard chart
            • Update dashboard chart
            • Delete dashboard chart
            • Preview dashboard chart SQL
            • Generate dashboard chart SQL draft
            • Run dashboard chart
            • View dashboard chart result
            • Download dashboard chart result
            • Refresh all dashboard charts
        • Resource center
          • Catalog
            • Browse resources
              • Browse the resource catalog
              • Get the Catalog system tree
            • Manage Catalogs
              • List Catalogs
              • Get Catalog details
              • Create a Catalog
              • Update a Catalog
              • Delete a Catalog
              • List Catalog references
            • Databases
              • List databases
              • Get database details
              • Create a database
              • Update a database
              • Delete a database
              • List objects in a database
              • List database tables
              • Run NL2SQL
            • File storage
              • Get volume details
              • Create a volume
              • Update a volume
              • Delete a volume
              • Get the full volume path
              • List files
              • Get file details
              • Create a file
              • Delete a file
              • Upload a file
              • Preview a file
              • Download a file
              • Create a folder
              • Update a folder
              • Delete a folder
              • Empty a folder
              • Trigger a file workflow
            • Tables
              • Get table information
              • Get a table overview
              • Get the full table path
              • Get table data
              • Preview table data
              • Create a table
              • Check whether a table exists
              • Truncate a table
              • Delete a table
              • Batch get table information
            • References
              • List Catalog references
              • Query table reference
              • List volume references
              • List folder references
              • Unlink a file
            • Processing results
              • Get a data asset
              • Batch get data assets
              • List file-processing artifacts
              • Batch resolve file artifacts
          • Knowledge Base
            • Manage knowledge bases
              • List knowledge bases
              • List knowledge base tags
              • Get knowledge base details
              • Create a knowledge base
              • Create an empty data-side knowledge base
              • Create a knowledge base with data sources
              • Update a knowledge base
              • Delete a knowledge base
            • Data
              • Upload local files to a knowledge base
              • Upload local files before creating a knowledge base
              • List data
              • Preview data to add
              • Preview data before creating a knowledge base
              • Check whether data has been added
              • Add data
              • Backfill data records for a knowledge base
              • Update data settings
              • Delete data
            • Documents and segments
              • Get document details
              • Preview a source file
              • Preview a processed image
              • Create a segment
              • Update a segment
              • Enable or disable a segment
              • Delete a segment
              • Re-vectorize a data source
            • Processing tasks
              • List processing tasks
              • Reconcile processing task status
            • Semantic entries
              • List semantic entries
              • Create a semantic entry
              • Update a semantic entry
              • Delete a semantic entry
              • Import semantic entries
              • Export semantic entries
              • Validate semantic entries
          • Computing resources
            • Query the computing resource list
            • Query computing resource details
            • Create computing resource
            • Update computing resource
            • Check before deletion
            • Delete computing resource
            • Pause computing resources
            • Restore computing resources
            • Retry compute resource
            • Query Worker running status
            • Query the available Worker image
            • Take over ownership of historical computing resources
          • Computing resource specifications
            • Query specification list
            • Query specification management permission
            • Create spec
            • Updated specs
            • Enable or disable specification
          • Data release
            • Query release list
            • Query release summary
            • Query the source database table
            • Check release name
            • Query the workspace name
            • Batch parse workspace names
            • Create Publish
            • Update released
            • Delete post
          • Data Subscription
            • Query subscription list
            • Accept Subscription
            • Unsubscribe
        • Monitoring
          • Get task execution overview
          • List SQL execution records
          • Get SQL execution details
          • Get SQL execution profile
          • Get SQL history overview
          • List operation logs
          • Get operation log overview
        • Users and permissions
          • Users and tags
            • User management
              • List users
              • Create user
              • Update user information
              • Update user status
              • Delete user
            • Roles and audit records
              • Get user-role bindings
              • Update user-role bindings
              • List user audit events
            • User tags
              • View user tag list
              • Create user tag
              • Update user tag
              • Delete user tag
          • Roles and permissions
            • Role management
              • List roles
              • Update role
              • Update role lifecycle
              • View role deletion impact
              • Delete role
            • Role permissions and impact
              • Get the role permission view
              • Get role ownership impact
              • List role audit events
            • Role inheritance
              • List role inheritance relationships
              • Get the role inheritance graph
              • Add role inheritance
              • Remove role inheritance
            • Data privileges
              • View data privileges
              • Validate data privileges
              • Apply data privileges
              • View data privilege operation
              • Repair data privilege operation
              • View permission resources
              • Batch view permission resources
            • Object permissions
              • View object permission view
              • Validate object authorization
              • Apply object authorization
            • Application permissions
              • View permission schema
              • View current identity roles
              • Validate application permission policy
              • Apply application permission policy
        • Agents
          • Agent
            • Query the list of agents
            • Query the details of the agent
            • Create the agent
            • Update agent
            • Delete agent
            • Query resource binding
            • Update resource binding
            • Update model binding
            • Query running strategy
            • Update run policy
            • Query the agent version
            • Delete agent version
            • Set as default version
            • Deactivate agent version
            • Bind operating environment
            • Re-coordinate the operating environment
          • Call the agent
            • Get the agent call instructions
            • Called by other agents
            • Call from MCP client
          • Session
            • Query session list
            • Query session details
            • Create session
            • Update session
            • Query session message
          • Agent feedback
            • Inquiry feedback
            • Query feedback statistics
          • Automation tasks
            • Query task list
            • Query task details
            • Create task
            • Update task
            • Archive task
            • Start task running
            • Query operation details
            • Query running events
            • Query the running results
          • Intelligent body construction
            • Generate candidate solutions
            • Query the current agent configuration
            • Regenerate candidate
            • Cancel candidate
            • Confirm candidate
            • Check available resources
          • Channel example
            • Query instance list
            • Query instance details
            • Create instance
            • Update instance
            • Delete instance
            • Test the new configuration
            • Test created instance
          • Agent model configuration
            • List available models
            • Query model configuration list
            • Query model configuration details
            • Create model configuration
            • Update model configuration
          • Run policy configuration
            • Query the running policy list
            • Query running policy details
            • Create run policy
            • Update run policy
          • Agent knowledge base
            • Query knowledge base list
            • Query knowledge base details
            • Create agent knowledge base metadata
            • Update agent knowledge base metadata
          • Tools
            • Query tool list
            • Query tool details
            • Creation tool
            • Update tool
            • Query tool tag
            • Query agents that reference this tool
            • Query GitHub tool connection
            • Save GitHub tool connection
            • Remove GitHub Tools connection
            • Query Grafana tool connection
            • Save Grafana tool connection
            • Remove Grafana tool connection
            • Query mail tool connection
            • Save mail tool connection
            • Delete Mail Tool Connection
            • Generate enterprise WeChat callback key
          • Skills
            • Query skill list
            • Query skill details
            • Create skill
            • Update skills
            • Query skill tag
            • Execution Skills
            • Generate a skill with AI
            • Check out the skill pack
            • Import skill package
            • Query skill files
            • Download skill file
            • Query skill version
            • Set the current skill version
            • Query the agent that references this skill
          • Callback rules
            • Query callback rule list
            • Query callback rule details
            • Create callback rule
            • Update callback rule
            • Delete callback rule
          • Agent task templates
            • Query the list of agent task templates
            • Query the details of the agent task template
            • Create agent task template
            • Update agent task template
          • Agent workflow bindings
            • Query the agent workflow binding list
            • Query the agent workflow binding details
            • Create agent workflow binding
            • Update agent workflow binding
          • Runtime providers
            • Query runtime provider list
            • Query runtime provider Profile
          • System agent configuration
            • Query system agent configuration
            • Query the system agent GitHub project
            • Query system agent GitHub projects for new configuration
            • Update system agent configuration
          • Agent packages
            • Load the agent package
            • Export agent package
          • Runtime state and records
            • Query runtime data sharding
            • Query runtime task list
            • Query runtime task details
            • Query runtime task events
            • Query runtime manifest
            • Query runtime round snapshot
          • Asynchronous resource operations
            • Query resource operation list
            • Query resource operation details
            • Cancel resource operation
          • Connections
            • Query connection list
            • Query connection details
            • Create connection
            • Update connection
            • Probe MCP connection
            • Batch creation MCP tool
      • Prepare to call an API
      • Genesis
        • Model inference
          • List callable models
          • Chat generation (Chat Completions)
            • Create a Chat Completion
          • Message generation (Messages)
            • Create a Message
            • Count input tokens
          • Response generation and management (Responses)
            • Create a Response
            • Retrieve a Response
            • List Response input items
            • Delete a Response
          • Conversation management
            • Create a conversation
            • Retrieve a conversation
            • Update conversation metadata
            • Delete a conversation
            • Create a conversation item
            • List conversation items
            • Retrieve a conversation item
            • Delete a conversation item
          • Embeddings
            • Create an Embedding
          • Document reranking
            • Rerank documents
        • Usage and monitoring
          • Model catalog
            • List the model catalog
            • List the public model catalog
            • List model selection options
            • Get model filter options
            • Get model health
            • Get public model overview
          • Call usage
            • Get usage summary
            • Get usage trends
            • Get grouped usage
          • Request logs
            • List request logs
            • Get request log details
            • List upstream request attempts
            • Download a request or response body
          • Local token keys
            • Get local key issuance capability
            • List local token keys
          • Notifications
            • List notifications
            • Get unread notification counts
            • List notification channels
            • List notification rules
          • Runtime status
            • Get model runtime status
        • Accounts and billing
          • Accounts
            • List current accounts
            • Get account overview
            • Get billing settings
          • Usage and costs
            • Get rated usage summary
            • List rated usage records
            • Get cost trends
            • List AI service usage
          • Transactions
            • List transactions
            • List consumption aggregates
          • Bills
            • List monthly bills
            • List monthly bill usage line items
          • Recharges
            • Get recharge options
            • List recharge orders
          • Payment authorizations
            • List payment authorizations
          • Subscriptions
            • List subscription products
            • List subscriptions
Back to top
Reference › SQL Reference › SQL Statements › Data Query Language (DQL) › SUBQUERY

SUBQUERY¶

  • SUBQUERY Overview
  • Derived Tables
  • Subqueries with Comparison Operators
  • SUBQUERY with ANY or SOME
  • SUBQUERY with ALL
  • SUBQUERY with EXISTS
  • SUBQUERY with IN
Last updated on Oct 19, 2026
Subquery
SELECT
Copyright © 2026, MatrixOrigin
Made with Sphinx and @pradyunsg's Furo