DATA → QUERY → RESULT

SQL Syntax Map

Find SQL by task, from queries and aggregation to COPY INTO, updates, object definitions and access control. Compare Snowflake and BigQuery syntax, prerequisites and effects.

Coverage and official references

Includes 23 query comparison groups and 22 operation topics. “Examples” means representative examples, not every option or every product command.

Operations checked: 2026-10-06

Coverage and official references
Area	Syntax and features	Coverage	Official references
Queries and analysis	SELECT / JOIN / GROUP BY / WINDOW	Representative examples	Snowflake · BigQuery
Loading and exporting	COPY INTO / LOAD DATA / EXPORT DATA	Representative examples	Snowflake · BigQuery
Data manipulation	INSERT / UPDATE / DELETE / MERGE / TRUNCATE	Representative examples	Snowflake · BigQuery
Object definitions	TABLE / VIEW / SCHEMA / FUNCTION / PROCEDURE	Representative examples	Snowflake · BigQuery
Transactions, access and scripting	TRANSACTION / GRANT / REVOKE / DECLARE / INFORMATION_SCHEMA	Representative examples	Snowflake · BigQuery
Automation and pipelines	PIPE / STREAM / TASK / DYNAMIC TABLE / scheduled queries	Official reference only	Snowflake · BigQuery
External and specialized tables	EXTERNAL TABLE / ICEBERG / SNAPSHOT / CLONE / MATERIALIZED VIEW	Official reference only	Snowflake · BigQuery
Accounts and compute	ACCOUNT / USER / ROLE / WAREHOUSE / IAM / reservations	Official reference only	Snowflake · BigQuery
Security and sharing	POLICY / MASKING / ROW ACCESS / sharing / connections	Official reference only	Snowflake · BigQuery
ML and product extensions	ML / Cortex / search indexes / vector indexes / applications	Official reference only	Snowflake · BigQuery

Load files into a table · COPY INTO <table> / LOAD DATA INTO
Export a table to files · COPY INTO <location> / EXPORT DATA
Prepare a stage and file format · CREATE STAGE / FROM FILES
Validate files before loading · VALIDATION_MODE / LOAD DATA
Insert rows · INSERT INTO / INSERT INTO
Update matching rows · UPDATE / UPDATE
Delete matching rows · DELETE FROM / DELETE FROM
Update matches and insert new rows · MERGE INTO / MERGE INTO
Remove all rows, keep the table · TRUNCATE TABLE / TRUNCATE TABLE
Create a table with column types · CREATE TABLE / CREATE TABLE
Save a query as a new table · CREATE TABLE AS SELECT / CREATE TABLE AS SELECT
Save a query as a view · CREATE VIEW / CREATE VIEW
Add a column to a table · ALTER TABLE / ALTER TABLE
Drop a table · DROP TABLE / DROP TABLE
Commit or roll back changes · BEGIN TRANSACTION / BEGIN TRANSACTION
Grant read access · GRANT / GRANT
Revoke a direct grant · REVOKE / REVOKE
Create a schema or dataset · CREATE SCHEMA / CREATE SCHEMA
Inspect tables and columns · SHOW TABLES / INFORMATION_SCHEMA
Use variables and multiple statements · DECLARE / DECLARE
Create a SQL function · CREATE FUNCTION / CREATE FUNCTION
Save a procedure · CREATE PROCEDURE / CREATE PROCEDURE

SQL at a glance

Covers queries and common operational statements. The coverage table distinguishes examples from reference-only areas.

Build the input

Choose the source

FROM identifies the table or result to process.

Name it / nest it

WITH and a subquery in FROM can express the same intermediate result.

Join horizontally: which rows survive?

Matches / all left / all from both / every combination.

Match condition / filter after joining

In a LEFT JOIN, moving the condition changes which rows survive.

Nested values to rows

Expand elements of a composite value into more rows.

Filter rows

When do you filter?

WHERE before aggregation; HAVING after aggregation; QUALIFY after windows.

Aggregate rows

N rows → 1 value

Choose what to count or which summary to calculate.

Order values inside an aggregate / compute percentiles

WITHIN GROUP orders aggregate inputs; BigQuery places ORDER BY inside aggregate arguments.

Choose the inputs to an aggregate

Control duplicate values, NULLs, maximum-scoring rows and input limits within aggregate calls.

Grouping unit / deduplication

GROUP BY defines aggregation units; DISTINCT removes duplicate selected rows.

Analyze without collapsing

Collapse / attach to each row

Aggregate: N rows → one per group. Window: N rows → N rows.

Ranking: what happens to ties?

Unique sequence / ties with gaps / ties without gaps.

Skip NULL values / sort NULL rows

IGNORE / RESPECT NULLS controls values seen by a function; NULLS FIRST / LAST controls row ordering.

Advanced: row position / value range

ROWS uses row positions; RANGE uses ordering values.

Create values

Compare / combine conditions

Comparisons return TRUE, FALSE or NULL; logical operators combine them.

Detect / replace missing values

Equality with NULL is unknown. Test with IS NULL.

General branching / binary shorthand

CASE supports multiple conditions; IF / IFF is compact for two outcomes.

Normal cast / safe cast

Unconvertible value: CAST errors; the safe version returns NULL.

Remove sign / round

ABS returns magnitude; ROUND rounds to the specified precision.

Change case / extract / concatenate

Apply four operations to the same string.

Current / add / difference / truncate

The operation is shared; argument order and unit syntax differ.

Nested value / SQL string

Both may display 7, but their types differ.

Combine sets

Combine vertically: what about duplicates?

One A and B: keep duplicates / remove them / common values / left only.

Advanced: match by column name

Match names even when column positions differ.

Finish the result

Deduplicate → sort → skip initial rows → take up to the limit.

Load and export files

Load files into a tableCOPY INTO <table>Loads file rows into the table. Inspect the load result and target rows.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Prepare an empty orders table and a CSV with an id,amount header. Upload it to Snowflake internal stage csv_stage, or to compatible-location Cloud Storage for BigQuery. Replace YOUR_BUCKET.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

COPY INTO tbi_lab.orders
FROM @tbi_lab.csv_stage/orders.csv
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
ON_ERROR = 'ABORT_STATEMENT';

Expected change

Loads file rows into the table. Inspect the load result and target rows.

How to verify

SELECT id, amount FROM tbi_lab.orders ORDER BY id;

ON_ERROR controls failures; ABORT_STATEMENT stops the statement. Load history may skip previously loaded files on a rerun. FORCE=TRUE can duplicate data.

Official sources · Snowflake 1

# load-files

Export a table to filesCOPY INTO <location>Writes query results to CSV files. Inspect the destination files.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Prepare the two-row orders table and a writable destination: csv_stage for Snowflake, or Cloud Storage with YOUR_BUCKET replaced for BigQuery.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

COPY INTO @tbi_lab.csv_stage/export_run/
FROM (SELECT id, amount FROM tbi_lab.orders)
FILE_FORMAT = (TYPE = CSV)
HEADER = TRUE;

Expected change

Writes query results to CSV files. Inspect the destination files.

How to verify

LIST @tbi_lab.csv_stage/export_run/;

Use a fresh destination each time. Output may span multiple files. Check file counts and contents at the destination.

Official sources · Snowflake 1

# unload-files

Prepare a stage and file formatCREATE STAGE · CREATE FILE FORMAT · PUT · LISTCreates an empty internal stage and a CSV format in Snowflake.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Prepare tbi_lab and the required create/read permissions.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

CREATE FILE FORMAT tbi_lab.csv_format
  TYPE = CSV SKIP_HEADER = 1;
CREATE STAGE tbi_lab.csv_stage
  FILE_FORMAT = (FORMAT_NAME = 'tbi_lab.csv_format');

Expected change

Creates an empty internal stage and a CSV format in Snowflake.

How to verify

LIST @tbi_lab.csv_stage;

Creating a stage does not upload files. Upload orders.csv in Snowsight or a PUT-capable client. PUT targets internal stages.

Official sources · Snowflake 1 · Snowflake 2 · Snowflake 3

# stage-format

Validate files before loadingVALIDATION_MODE · RETURN_ERRORS · ON_ERROR · COPY INTOIn Snowflake, returns file errors without inserting rows.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Prepare an empty orders table and a CSV with an id,amount header. Upload it to Snowflake internal stage csv_stage, or to compatible-location Cloud Storage for BigQuery. Replace YOUR_BUCKET.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

COPY INTO tbi_lab.orders
FROM @tbi_lab.csv_stage/orders.csv
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
VALIDATION_MODE = 'RETURN_ERRORS';

Expected change

In Snowflake, returns file errors without inserting rows.

How to verify

SELECT id, amount FROM tbi_lab.orders ORDER BY id;

VALIDATION_MODE validates without loading. It cannot be combined with a transformation SELECT. Fix errors before a normal COPY.

Official sources · Snowflake 1

# validate-load

Change stored rows

Insert rowsINSERT INTO · INSERT SELECTAdds id=3, amount=300.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Start with tbi_lab.orders(id, amount) containing (1,100) and (2,200). Restore this initial state for each example; these are not sequential steps.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

INSERT INTO tbi_lab.orders (id, amount)
VALUES (3, 300);

Expected change

Adds id=3, amount=300.

How to verify

SELECT id, amount FROM tbi_lab.orders ORDER BY id;

Official sources · Snowflake 1

# insert-rows

Update matching rowsUPDATE · SET · WHEREChanges amount to 150 for id=1.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Start with tbi_lab.orders(id, amount) containing (1,100) and (2,200). Restore this initial state for each example; these are not sequential steps.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

UPDATE tbi_lab.orders SET amount = 150 WHERE id = 1;

Expected change

Changes amount to 150 for id=1.

How to verify

SELECT id, amount FROM tbi_lab.orders ORDER BY id;

Preview affected rows using SELECT with the same WHERE. BigQuery requires WHERE for UPDATE and DELETE.

Official sources · Snowflake 1

# update-rows

Delete matching rowsDELETE FROM · WHERERemoves the row with id=2.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Start with tbi_lab.orders(id, amount) containing (1,100) and (2,200). Restore this initial state for each example; these are not sequential steps.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

DELETE FROM tbi_lab.orders WHERE id = 2;

Expected change

Removes the row with id=2.

How to verify

SELECT id, amount FROM tbi_lab.orders ORDER BY id;

Preview affected rows using SELECT with the same WHERE. BigQuery requires WHERE for UPDATE and DELETE.

Official sources · Snowflake 1

# delete-rows

Update matches and insert new rowsMERGE INTO · WHEN MATCHED · WHEN NOT MATCHEDUpdates id=1 to 150 and inserts id=3 with 300.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Start with tbi_lab.orders(id, amount) containing (1,100) and (2,200). Restore this initial state for each example; these are not sequential steps.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

MERGE INTO tbi_lab.orders AS t
USING (SELECT 1 AS id, 150 AS amount
       UNION ALL SELECT 3, 300) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, amount)
  VALUES (s.id, s.amount);

Expected change

Updates id=1 to 150 and inserts id=3 with 300.

How to verify

SELECT id, amount FROM tbi_lab.orders ORDER BY id;

Check that multiple source rows do not match one update target key. Duplicate-match behavior depends on product and action.

Official sources · Snowflake 1

# merge-rows

Remove all rows, keep the tableTRUNCATE TABLELeaves zero rows while keeping the column definitions.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Start with tbi_lab.orders(id, amount) containing (1,100) and (2,200). Restore this initial state for each example; these are not sequential steps.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

TRUNCATE TABLE tbi_lab.orders;

Expected change

Leaves zero rows while keeping the column definitions.

How to verify

SELECT COUNT(*) AS rows_left FROM tbi_lab.orders;

Targets every row; there is no WHERE filter.

Official sources · Snowflake 1

# truncate-table

Define tables, views and routines

Create a table with column typesCREATE TABLE · TEMPORARY TABLECreates an empty table with id and amount columns.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Create tbi_lab first and ensure the new object name is unused.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

CREATE TABLE tbi_lab.orders (id INTEGER, amount NUMBER(12,2));

Expected change

Creates an empty table with id and amount columns.

How to verify

DESCRIBE TABLE tbi_lab.orders;

These examples do not use OR REPLACE to overwrite existing data.

Official sources · Snowflake 1

# create-table

Save a query as a new tableCREATE TABLE AS SELECT · CTASSaves rows with amount >= 200 in a new table.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Start with tbi_lab.orders(id, amount) containing (1,100) and (2,200). Restore this initial state for each example; these are not sequential steps.

Create tbi_lab first and ensure the new object name is unused.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

CREATE TABLE tbi_lab.large_orders AS
SELECT id, amount FROM tbi_lab.orders WHERE amount >= 200;

Expected change

Saves rows with amount >= 200 in a new table.

How to verify

SELECT * FROM tbi_lab.large_orders ORDER BY id;

These examples do not use OR REPLACE to overwrite existing data.

Official sources · Snowflake 1

# create-as-select

Save a query as a viewCREATE VIEW · ALTER VIEW · DROP VIEWStores a query definition. A regular view does not retain a copy of the result rows.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Start with tbi_lab.orders(id, amount) containing (1,100) and (2,200). Restore this initial state for each example; these are not sequential steps.

Create tbi_lab first and ensure the new object name is unused.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

CREATE VIEW tbi_lab.large_orders_view AS
SELECT id, amount FROM tbi_lab.orders WHERE amount >= 200;

Expected change

Stores a query definition. A regular view does not retain a copy of the result rows.

How to verify

SELECT * FROM tbi_lab.large_orders_view ORDER BY id;

ALTER VIEW and DROP VIEW change or remove the view definition. Check dependencies on source tables.

Official sources · Snowflake 1

# create-view

Add a column to a tableALTER TABLE · ADD COLUMN · DROP COLUMN · RENAMEAdds note; existing rows have NULL in this column.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Start with tbi_lab.orders(id, amount) containing (1,100) and (2,200). Restore this initial state for each example; these are not sequential steps.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

ALTER TABLE tbi_lab.orders ADD COLUMN note VARCHAR;

Expected change

Adds note; existing rows have NULL in this column.

How to verify

DESCRIBE TABLE tbi_lab.orders;

This example adds a column. Dropping columns or changing types needs a separate data and dependency check.

Official sources · Snowflake 1

# alter-table

Drop a tableDROP TABLE · IF EXISTSRemoves the table definition and its data.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Start with tbi_lab.orders(id, amount) containing (1,100) and (2,200). Restore this initial state for each example; these are not sequential steps.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

DROP TABLE tbi_lab.orders;

Expected change

Removes the table definition and its data.

How to verify

SHOW TABLES LIKE 'ORDERS' IN SCHEMA tbi_lab;

Recovery depends on product retention settings. Do not assume this operation is recoverable.

Official sources · Snowflake 1

# drop-table

Create a schema or datasetCREATE SCHEMA · USE DATABASE · USE SCHEMACreates tbi_lab as a container for objects.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

CREATE SCHEMA tbi_lab;
USE SCHEMA tbi_lab;

Expected change

Creates tbi_lab as a container for objects.

How to verify

SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

Creates a schema in the current database. USE SCHEMA changes the default namespace.

Official sources · Snowflake 1 · Snowflake 2

# namespace

Create a SQL functionCREATE FUNCTION · UDFCreates a function that adds one to its argument.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Create tbi_lab first and ensure the new object name is unused.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

CREATE FUNCTION tbi_lab.add_one(x INTEGER)
RETURNS INTEGER LANGUAGE SQL
AS $$ x + 1 $$;

Expected change

Creates a function that adds one to its argument.

How to verify

SELECT tbi_lab.add_one(2) AS result;

Official sources · Snowflake 1

# user-function

Save a procedureCREATE PROCEDURE · CALLCreates a procedure that returns 3 when called.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Create tbi_lab first and ensure the new object name is unused.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

CREATE PROCEDURE tbi_lab.answer()
RETURNS INTEGER LANGUAGE SQL
AS $$
BEGIN
  RETURN 3;
END;
$$;

Expected change

Creates a procedure that returns 3 when called.

How to verify

CALL tbi_lab.answer();

Official sources · Snowflake 1

# procedure

Manage execution, access and metadata

Commit or roll back changesBEGIN TRANSACTION · COMMIT · ROLLBACKThis example rolls back: id=1 still has amount=100.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Start with tbi_lab.orders(id, amount) containing (1,100) and (2,200). Restore this initial state for each example; these are not sequential steps.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

BEGIN TRANSACTION;
UPDATE tbi_lab.orders SET amount = 150 WHERE id = 1;
ROLLBACK;

Expected change

This example rolls back: id=1 still has amount=100.

How to verify

SELECT id, amount FROM tbi_lab.orders ORDER BY id;

COMMIT persists changes; ROLLBACK cancels them. Snowflake DDL implicitly commits an active transaction, so do not add DDL to this example.

Official sources · Snowflake 1

# transaction

Grant read accessGRANT · TO ROLEGrants the recipient permission to read the table.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Requires orders and authority to change access. Prepare the Snowflake tbi_reader role or verify the BigQuery recipient group.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

GRANT SELECT ON TABLE tbi_lab.orders TO ROLE tbi_reader;

Expected change

Grants the recipient permission to read the table.

How to verify

SHOW GRANTS ON TABLE tbi_lab.orders;

Table SELECT alone may not suffice. Also check database/schema USAGE and warehouse access.

Official sources · Snowflake 1

# permissions-grant

Revoke a direct grantREVOKE · FROM ROLERemoves the specified direct grant; access from other paths may remain.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Requires orders and authority to change access. Prepare the Snowflake tbi_reader role or verify the BigQuery recipient group.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

REVOKE SELECT ON TABLE tbi_lab.orders FROM ROLE tbi_reader;

Expected change

Removes the specified direct grant; access from other paths may remain.

How to verify

SHOW GRANTS ON TABLE tbi_lab.orders;

This REVOKE does not remove access inherited through other roles or parent resources. Inspect effective access.

Official sources · Snowflake 1

# permissions-revoke

Inspect tables and columnsSHOW TABLES · DESCRIBE TABLE · INFORMATION_SCHEMAReturns metadata for accessible tables and columns.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Prepare tbi_lab and the required create/read permissions.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

SHOW TABLES IN SCHEMA tbi_lab;

Expected change

Returns metadata for accessible tables and columns.

How to verify

DESCRIBE TABLE tbi_lab.orders;

Official sources · Snowflake 1 · Snowflake 2

# inspect-objects

Use variables and multiple statementsDECLARE · BEGIN · END · EXECUTE IMMEDIATEChanges a variable from 2 to 3 and returns 3.

Snowflake · SQL for the selected product. This page does not execute it. Checked against official documentation; not executed in a cloud account.

Before running

Snowflake assumes a selected database; BigQuery assumes a selected project. tbi_lab is a practice schema or dataset.

Use an authorized practice environment. Check names, destinations and recipients. Cloud execution and storage may incur charges.

EXECUTE IMMEDIATE $$
DECLARE n INTEGER DEFAULT 2;
BEGIN
  RETURN n + 1;
END;
$$;

Expected change

Changes a variable from 2 to 3 and returns 3.

How to verify

Compare the expected change with the result, access settings or destination in your environment.

BEGIN…END is a procedural block, distinct from BEGIN TRANSACTION.

Official sources · Snowflake 1 · Snowflake 2

# script

Try it: why JOIN increases row counts

When one customer has three orders, that customer appears three times in the joined result. Change the order count and SQL to inspect the matching rows.

Inspect input data

Orders for Alice

Edit SQL SQLite

Run one read-only statement starting with SELECT or WITH. Tables: customers and orders. Up to 200 rows are shown.

Choose an example or edit the SQL, then run it.

Read this SQL in words

First, FROM reads the customers table, and AS c gives customers the short name c within this query.

Next, INNER JOIN combines it with the orders table. AS o names orders as o. ON c.id = o.customer_id keeps only pairs where the customer identifier c.id equals the order’s customer identifier o.customer_id. Several orders for one customer produce several rows.

SELECT returns the customer identifier c.id, the name c.name and the order identifier o.id. AS order_id names the output column for o.id as order_id.

Finally, ORDER BY c.id, o.id sorts by increasing customer identifier, then by increasing order identifier within each customer.

The dot . in c.id connects a table alias and column name; the comma , separates columns or expressions; the semicolon ; ends the SQL statement.

Check your understanding

Which expression gives Cara zero orders after a LEFT JOIN? COUNT(*) COUNT(o.id)

This runner uses SQLite, independently of the Snowflake / BigQuery selector above. It uses fictional data and runs SQL in your browser. Input is not saved or sent. The engine loads from an external CDN on the first run.

Compare JOIN types · Official SQLite SELECT and JOIN reference

SQL at a glance

Build the input

Choose the source

Name it / nest it

Join horizontally: which rows survive?

Match condition / filter after joining

Nested values to rows

Filter rows

When do you filter?

Aggregate rows

N rows → 1 value

Order values inside an aggregate / compute percentiles

Choose the inputs to an aggregate

Grouping unit / deduplication

Analyze without collapsing

Collapse / attach to each row

Ranking: what happens to ties?

Previous row / next row

Skip NULL values / sort NULL rows

Advanced: row position / value range

Create values

Compare / combine conditions

Detect / replace missing values

General branching / binary shorthand

Normal cast / safe cast

Remove sign / round

Change case / extract / concatenate

Current / add / difference / truncate

Nested value / SQL string

Combine sets

Combine vertically: what about duplicates?

Advanced: match by column name

Finish the result

Finish the result

Load and export files

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Change stored rows

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Define tables, views and routines

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Manage execution, access and metadata

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify

Expected change

How to verify