Query syntax

Snowflake query syntax supported in Snowflake compatibility mode.

Preview

Snowflake query syntax in Snowflake compatibility mode.

Supported clauses

Clause	Support	Notes
SELECT	Supported	Including DISTINCT and SELECT * EXCLUDE.
FROM	Supported	Tables, views, subqueries, and LATERAL subqueries.
JOIN	Supported	Inner and outer joins, CROSS JOIN, NATURAL JOIN, and JOIN ... USING.
WHERE	Supported	Scalar and EXISTS subqueries.
GROUP BY	Supported	Including ALL, ordinals, GROUPING SETS, ROLLUP, and CUBE.
HAVING	Supported
QUALIFY	Supported	Filters on window function results.
ORDER BY	Supported	Including NULLS FIRST and NULLS LAST.
LIMIT	Supported	With OFFSET.
FETCH	Not supported	FETCH FIRST ... ROWS ONLY is rejected. Use LIMIT with OFFSET.
WITH	Supported	WITH RECURSIVE is not supported.
Window functions	Supported	PARTITION BY, explicit frames, and IGNORE NULLS.
LISTAGG ... WITHIN GROUP	Supported
TOP n	Not supported	Use LIMIT.
PIVOT, UNPIVOT	Not supported
MATCH_RECOGNIZE	Not supported
CONNECT BY	Not supported
ASOF JOIN	Not supported
AT / BEFORE / CHANGES	Not supported	Time travel.
FLATTEN	Not supported	LATERAL FLATTEN and TABLE(FLATTEN(...)).
IDENTIFIER()	Not supported
SAMPLE / TABLESAMPLE	Not supported	Row sampling is not available. Use WHERE RANDOM() < 0.1.
SELECT * ILIKE	Not supported

Set operations

Operation	Support	Notes
UNION, UNION ALL	Supported
INTERSECT	Supported
EXCEPT	Supported
MINUS	Not supported	Snowflake’s synonym for EXCEPT is not accepted. Write EXCEPT.

Query parameters

Snowflake bind variables are written as :name (named) or :N (positional). Bind values with the query_parameters request property; see Parametrized queries for passing parameter values from different client SDKs, and the SQL guide for parameter value types. :N uses the same positional convention as $N (so :1 is bound as $1), which keeps positional binding uniform across dialects:

SELECT id FROM orders WHERE category = :category AND priority = :1;

The infix colon (value:field) is the semi-structured accessor, not a parameter. Referencing a parameter that was not set raises an error.

Limitations

• Query parameters use the :name / :N bind form only. ? positional parameters, $name session variables, and @name stage references are not supported.
• Fully qualified database.schema.table names are not supported and fail with Database 'mydb' does not exist. Two-part schema.table names work. Most tool-generated Snowflake SQL is fully qualified, so this is usually the first thing a migrated workload hits.
• Identifiers are returned in lower case. Snowflake folds unquoted identifiers to upper case, so a column selected as n comes back as N there and n here. A client that looks up result columns by name needs to be case-insensitive.
• $$-quoted string literals are not supported.
• FETCH FIRST ... ROWS ONLY is not supported; use LIMIT with OFFSET.
• MINUS is not accepted as a synonym for EXCEPT.