Summary of Commands

Snowflake supports most of the commands and statements defined in SQL:1999.

In this Topic:

DDL (Data Definition Language) Commands

Account-based and Session-based DDL

Commands

Notes

ALTER ACCOUNT

Used to set account-level parameters (only by users with ACCOUNTADMIN role).

ALTER SESSION

Used to set parameters in the current session.

BEGIN , COMMIT , ROLLBACK

Used for multi-statement transactions in the current session.

SHOW FUNCTIONS

Displays system-defined functions, as well as user-defined functions.

SHOW PARAMETERS

Used to view parameters settings for the account or current session. Also can be used to view parameter settings for objects that have parameters.

SHOW { LOCKS | TRANSACTIONS

SHOW VARIABLES

Used to view SQL variables in the current session.

SET , UNSET

Used to set and unset SQL variables in the current session.

USE { DATABASE | ROLE | SCHEMA | WAREHOUSE }

The USE command sets the database, role, schema, or warehouse for the current session.

Account Object DDL

Commands

Notes

{ ALTER | CREATE | DESC | DROP | UNDROP | USE } DATABASE

CREATE also supports cloning existing databases.

{ ALTER | CREATE | DESC | DROP } RESOURCE MONITOR

{ ALTER | CREATE | DESC | DROP | USE } ROLE

{ ALTER | CREATE | DESC | DROP } SHARE

{ ALTER | CREATE | DESC | DROP } USER

{ ALTER | CREATE | DESC | DROP | USE } WAREHOUSE

COMMENT

This command can be used with any account object; also, comments be set using CREATE or ALTER.

{ GRANT | REVOKE } PRIVILEGE

Privileges are not strictly a first-class object; however, these commands are used for enabling and managing access control for all first-class objects in Snowflake.

SHOW { DATABASES | PRIVILEGES | RESOURCE MONITORS | ROLES | SHARES | USERS | WAREHOUSES }

Database / Schema Object DDL

Commands

Notes

{ ALTER | CREATE | DESC | DROP } FILE FORMAT

CREATE also supports cloning existing file formats.

{ ALTER | CREATE | DESC | DROP } FUNCTION

Used for creating and managing UDFs (user-defined functions).

{ ALTER | CREATE | DESC | DROP } PIPE

{ ALTER | CREATE | DESC | DROP } PROCEDURE

Used for creating and managing stored procedures.

{ ALTER | CREATE | DESC | DROP } SEQUENCE

CREATE also supports cloning existing sequences.

{ ALTER | CREATE | DESC | DROP } STAGES

CREATE also supports cloning existing named stages (external only).

{ ALTER | CREATE | DESC | DROP | UNDROP | USE } SCHEMA

CREATE also supports cloning existing schemas.

{ ALTER | CREATE | DESC | DROP | TRUNCATE | UNDROP } TABLE

CREATE also supports cloning existing tables, creating populated tables (using CTAS), and creating empty tables (from existing tables).

{ ALTER | CREATE | DESC | DROP | UNDROP } VIEW

COMMENT

This command can be used with any database/schema object; also, comments be set using CREATE or ALTER.

SHOW { FILE FORMATS | PIPES | PROCEDURES | SCHEMAS | SEQUENCES | STAGES | TABLES | USER FUNCTIONS | VIEWS }

DML (Data Manipulation Language) Commands

Commands

Notes

DELETE , INSERT , MERGE , REPLACE , UPDATE

Used for general DML. INSERT includes support for multi-table inserts.

COPY INTO { <table> | <location> }

Used for loading/unloading data (i.e. bulk import/export).

GET , LIST , PUT , REMOVE

These commands do not perform DML; they are used to stage and manage files for data loading and unloading.

Query Syntax & Operators

Category

Constructs/Operators

Notes

Query Syntax

[ WITH ]

SELECT [ DISTINCT ]

Required.

[ FROM ]

[ AT | BEFORE ]

For querying using Time Travel.

[ SAMPLE ] / [ TABLESAMPLE ]

TABLESAMPLE is a synonym for SAMPLE.

[ JOIN ]

Support for inner, left outer, right outer, and full joins.

[ PIVOT | UNPIVOT ]

[ WHERE ]

[ GROUP BY ]

Columns used in GROUP BY must be in SELECT list.

[ HAVING ]

[ ORDER BY ]

[ VALUES ]

[ LIMIT ]

Arithmetic Operators

+ , - , * , \ , %

Comparison Operators

= , != , < > , > , >= , < , <=

Logical/Boolean Operators

AND , NOT , OR

Set Operators

INTERSECT [ ALL ] , MINUS [ ALL ] / EXCEPT , UNION [ ALL ]

Subquery Operators

ANY / ALL , [ NOT ] EXISTS , [ NOT ] IN

Predicates

BETWEEN , [ NOT ] EXISTS , [ NOT ] IN , LIKE