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.

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 (Ctrl Enter 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.

Cancelling a run

While a query or script runs, Run Query at the right of the status bar turns into Cancel. Hover it to see “Cancel query”.

Clicking it sends a cancel request for the job to BigQuery and the button changes to Canceling… and is disabled until the job ends. The run then ends with the message “Query execution was cancelled by user”. BigQuery can still bill for the work the job did before it stopped.

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.

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, temp tables included; see 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;

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

• 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.
• After a semicolon, the list starts with statement keywords: SELECT, CREATE, DECLARE, INSERT, …
• @@ lists BigQuery’s system variables (@@project_id, @@row_count, …) 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;

IF, WHILE, FOR and LOOP work. Variables aren’t offered in completions; if a script is mostly parameters, a notebook with template variables is the better fit.

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.