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