SQL Statements

This page lists common SQL statements for MatrixOne instances by category. It is a categorized index, not a complete grammar for every statement. The console’s Permissions · MatrixOne settings and instance capabilities determine whether you can execute a statement.

DDL (data definition)

Defines or changes databases, tables, indexes, views, and MatrixOne extension objects.

Category	Statements (summary)
Create	CREATE DATABASE, CREATE TABLE, CREATE TABLE AS SELECT, CREATE EXTERNAL TABLE, CREATE VIEW, CREATE INDEX / vector indexes, CREATE SEQUENCE, CREATE FUNCTION, CREATE STAGE, CREATE PUBLICATION, CREATE ... FROM ... PUBLICATION (subscription), CREATE SNAPSHOT, CREATE PITR, CREATE SOURCE, CREATE DYNAMIC TABLE, and others
Alter	ALTER TABLE, ALTER VIEW, ALTER SEQUENCE, ALTER STAGE, ALTER PUBLICATION, ALTER PITR, ALTER REINDEX, RENAME TABLE, and others
Drop	DROP DATABASE, DROP TABLE, DROP VIEW, DROP INDEX, DROP SEQUENCE, DROP FUNCTION, DROP STAGE, DROP PUBLICATION, DROP SNAPSHOT, DROP PITR, and others
Truncate	TRUNCATE TABLE
Restore	RESTORE SNAPSHOT, RESTORE PITR, RESTORE ACCOUNT, and others, subject to privilege and scenario restrictions

Product-oriented descriptions of extension objects:

• Stage: An abstraction for an external storage location, commonly used with LOAD DATA and file-listing functions.

• Publication / Subscription: Publishes and subscribes to data across instances.

• Snapshot / PiTR: Capabilities related to backup and point-in-time recovery.

• External / Dynamic table: External and dynamic tables, subject to availability in the instance version.

DML (data manipulation)

Statement	Purpose
INSERT / INSERT INTO ... SELECT	Inserts rows
INSERT IGNORE / INSERT ... ON DUPLICATE KEY UPDATE	Ignores a conflict or updates on conflict
REPLACE	Replaces rows
UPDATE	Updates existing rows
DELETE	Deletes rows
LOAD DATA INFILE / LOAD DATA INLINE	Bulk loading. See the syntax subset in Limitations.

DQL (data query)

Centered on SELECT:

Capability	Key points
Single table / projection / filtering / aggregation	SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT
Joins	INNER / LEFT / RIGHT / FULL / NATURAL / OUTER JOIN
Subqueries	IN / EXISTS / ANY/SOME / ALL, and derived tables
CTE	WITH common table expressions
Set operations	UNION, INTERSECT, MINUS

TCL (transactions)

Statement	Purpose
START TRANSACTION / BEGIN	Starts a transaction
COMMIT	Commits
ROLLBACK	Rolls back
SET AUTOCOMMIT	Controls automatic commits

DCL (data control)

Use with Privilege Categories and Permissions · MatrixOne:

Statement	Purpose
CREATE USER / DROP USER / ALTER USER	Users
CREATE ROLE / DROP ROLE	Roles
GRANT / REVOKE	Grants and revokes privileges

Other administrative statements

Category	Examples
Switch context	USE a database or switch a role
SHOW	SHOW DATABASES, SHOW TABLES, SHOW COLUMNS, SHOW CREATE TABLE, SHOW INDEX, SHOW VARIABLES, SHOW GRANTS, SHOW PROCESSLIST, SHOW ROLES, SHOW PUBLICATIONS, SHOW SUBSCRIPTIONS, SHOW STAGE, SHOW SNAPSHOTS, SHOW PITR, SHOW SEQUENCES, SHOW FUNCTION STATUS, and others
SET	Sets session or global variables, uses SET ROLE, and more
Prepared statements	PREPARE / EXECUTE / DEALLOCATE
Plans	EXPLAIN / EXPLAIN ANALYZE
Sessions	KILL

Relationship to platform features

Business scenario	Common statement categories
Creating tables and querying data in Catalog	DDL + DQL + DML
Sharing data across instances	Publication / Subscription DDL and SHOW
Backup and recovery	Snapshot / PiTR DDL
Vector search	CREATE TABLE with vector columns + vector indexes + distance-function queries
Privilege governance within an instance	DCL + SHOW GRANTS