Multi-statement queries

Several statements in one tab, separated by semicolons, run as one BigQuery script, with variables, temp tables, and a result for every statement. Querylab.io estimates the script before it runs, keeps completions and diagnostics working across statement boundaries, and reminds you to drop what you created.

DECLARE threshold INT64 DEFAULT 100;

CREATE TEMP TABLE active_users AS
SELECT user_id, email, login_count
FROM `ace-analytics.warehouse.users`
WHERE login_count > threshold;

SELECT country, COUNT(*) AS users
FROM active_users a
JOIN `ace-analytics.warehouse.user_profiles` p USING (user_id)
GROUP BY country
ORDER BY users DESC;

DROP TABLE IF EXISTS active_users;

Running a script

⌘Enter (CtrlEnter on Windows and Linux) or Run Query sends the whole tab to BigQuery as a script. BigQuery runs the statements in order, each as a child job; an error stops the script at the statement that failed, and the statements before it stay applied.

To run one statement, select it and press ⌘Enter: only the selection runs. The gutter diamonds do the same per statement, and price it first: see Partial execution.

Reading results per statement

When the script has more than one visible statement, a bar above the results lists them. Each entry is the statement’s number and the start of its SQL, with a green check or a red error icon once it’s done.

• Click an entry to see that statement’s result: a grid for a SELECT, the affected-row count and job details for DDL and DML.
• When the script finishes, the last statement that returned rows is selected; if none did, the last statement.
• A failed statement shows Statement failed and BigQuery’s message.
• BigQuery’s internal expression-evaluation jobs (the ones a DECLARE … DEFAULT (SELECT …) creates) are left out of the bar.

The execution plan in the Insights panel follows the selected statement, and so does lineage.

Estimating a script’s cost

The status bar’s estimate covers the whole script. BigQuery can dry-run a script that has DECLARE, SET, and control flow, but not one that creates a temp table, so:

A temp table declared with columns and no AS SELECT counts as 0 bytes. Details in Cost limits. The per-query limit and budgets apply to the script’s total.

Using temp tables across statements

A temp table created in one statement is a table for the rest of the script:

CREATE TEMP TABLE daily_orders AS
SELECT user_id, COUNT(*) AS order_count
FROM `ace-analytics.warehouse.orders`
WHERE order_date = CURRENT_DATE()
GROUP BY user_id;

SELECT u.email, d.order_count
FROM daily_orders d
JOIN `ace-analytics.warehouse.users` u USING (user_id)
WHERE d.order_count > 5;

CREATE TEMPORARY TABLE, CREATE OR REPLACE TEMP TABLE, IF NOT EXISTS, and explicit column lists with ARRAY and STRUCT types all parse.

SQL Notebooks avoid the problem: each cell’s result lands in an anonymous table that Querylab.io cleans up. A notebook’s ⋮ menu has Copy SQL as CTEs and Copy SQL as temp tables for turning it back into a single script.

Completions across statements

The editor treats the script as one document with statement order.

• After its CREATE, a temp table appears in FROM and JOIN completions as a Temporary Table, and alias. lists its columns, inferred from the AS SELECT or from the column list, including STRUCT fields.
• Before its CREATE, it doesn’t. Statement order is BigQuery’s execution order.
• After a semicolon, the list starts with statement keywords: SELECT, CREATE, DECLARE, INSERT, …
• @@ lists BigQuery’s system variables: @@project_id, @@dataset_id, @@row_count, @@time_zone, @@last_job_id, @@query_label, and the @@script. members with their types.

CREATE TEMP TABLE metrics AS
SELECT user_id, COUNT(*) AS total, AVG(amount) AS avg_amount
FROM `ace-analytics.warehouse.orders` GROUP BY user_id;

SELECT metrics. FROM metrics

Declaring variables

DECLARE and SET work as in BigQuery, with any type:

DECLARE start_date DATE DEFAULT '2024-01-01';
DECLARE end_date DATE DEFAULT CURRENT_DATE();
DECLARE total INT64;
DECLARE tags ARRAY<STRING>;

SET total = (SELECT COUNT(*) FROM `ace-analytics.warehouse.users`);

SELECT * FROM `ace-analytics.warehouse.orders`
WHERE order_date BETWEEN start_date AND end_date;

Variables are parsed and validated, but they aren’t suggested by name in later statements. If a script is mostly parameters, a notebook with template variables is the better fit. Control-flow statements (IF, WHILE, FOR, LOOP) parse and run; they don’t affect what completions or diagnostics see.

Checking every statement

Every statement is checked on its own, and every code-quality rule that applies to a single statement applies inside a script: unknown columns, missing partition filters, unused CTEs. Errors from all statements show at once, on the lines they belong to, including BigQuery’s own errors from the dry run, mapped back to the right statement.