Skip to main content

Multi-Statement Queries

Write multiple SQL statements in a single editor tab, separated by semicolons. Querylab.io parses, validates, and runs them as a BigQuery script.

How It Works​

Separate each statement with a semicolon. Querylab.io treats the entire editor content as a BigQuery script:

DECLARE threshold INT64 DEFAULT 100;

CREATE TEMP TABLE active_users AS
SELECT user_id, email FROM users WHERE login_count > threshold;

SELECT * FROM active_users ORDER BY user_id;

DROP TABLE IF EXISTS active_users;

When you run the script:

  • BigQuery executes statements sequentially, top to bottom
  • Each statement produces its own result
  • Errors in one statement stop execution of subsequent statements

Viewing Results​

After running a multi-statement script, a statement selector bar appears at the top of the results panel. Each statement is shown as a clickable chip with a status indicator:

  • Green circle — statement succeeded
  • Red circle — statement failed

Click any chip to view that statement's results, including data grids for SELECT statements and row counts for DML operations.

Temporary Tables​

Temporary tables let you break complex logic into steps. Create a temp table in one statement and reference it in later statements.

Syntax​

-- Basic
CREATE TEMP TABLE t AS SELECT 1 AS id, 'Alice' AS name;

-- With TEMPORARY keyword
CREATE TEMPORARY TABLE t AS SELECT * FROM users;

-- With OR REPLACE
CREATE OR REPLACE TEMP TABLE t AS SELECT * FROM users;

-- With IF NOT EXISTS
CREATE TEMP TABLE IF NOT EXISTS t AS SELECT * FROM users;

-- With explicit column definitions
CREATE TEMP TABLE t (
  id INT64,
  name STRING,
  tags ARRAY<STRING>,
  profile STRUCT<email STRING, age INT64>
);

Cross-Statement References​

Once created, a temp table is available in all subsequent statements:

CREATE TEMP TABLE daily_orders AS
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE order_date = CURRENT_DATE()
GROUP BY user_id;

-- Reference in a later statement
SELECT u.email, d.order_count
FROM daily_orders d
JOIN users u ON d.user_id = u.user_id
WHERE d.order_count > 5;

Important​

  • Temporary tables persist for ~24 hours after creation, even after the script finishes
  • Storage billing applies — you're billed for temp table storage until they expire or are dropped
  • They are only visible within the current session
  • Use DROP TABLE IF EXISTS to clean up explicitly to avoid unnecessary storage costs
Consider SQL Notebooks

SQL Notebooks manage temporary storage automatically. Each cell result is stored as an anonymous table that Querylab.io cleans up for you — no manual DROP TABLE needed.

Completions​

Querylab.io provides intelligent completions that work across statement boundaries.

Temp Table Suggestions​

After creating a temp table, it appears in completions for all subsequent FROM and JOIN clauses, labeled as "Temporary Table":

CREATE TEMP TABLE active_users AS
SELECT user_id, email FROM users WHERE status = 'active';

SELECT * FROM ¦
-- Completions show: active_users (Temporary Table)

Column Completions​

Querylab.io infers columns from the temp table definition — whether from AS SELECT or explicit column definitions:

CREATE TEMP TABLE metrics AS
SELECT user_id, COUNT(*) AS total, AVG(amount) AS avg_amount
FROM orders GROUP BY user_id;

SELECT metrics.¦ FROM metrics
-- Completions show: user_id, total, avg_amount

Complex types are fully supported:

CREATE TEMP TABLE t (
  id INT64,
  data STRUCT<name STRING, age INT64>,
  tags ARRAY<STRING>
);

SELECT t.¦ FROM t
-- Completions show: id, data, tags

Sequential Visibility​

Temp tables only appear in completions after their CREATE statement. A temp table defined later in the script is not suggested in earlier statements — just like BigQuery's execution model.

Statement Keywords​

When your cursor is after a completed statement (after the semicolon), completions suggest statement-level keywords like SELECT, CREATE, DECLARE, INSERT, and others.

DECLARE & SET​

BigQuery scripting variables work across statements.

DECLARE​

Define typed variables with optional defaults:

DECLARE start_date DATE DEFAULT '2024-01-01';
DECLARE end_date DATE DEFAULT CURRENT_DATE();
DECLARE min_orders INT64 DEFAULT 10;

SELECT * FROM orders
WHERE order_date BETWEEN start_date AND end_date;

Supports all BigQuery types including complex types:

DECLARE items ARRAY<STRING>;
DECLARE person STRUCT<name STRING, age INT64>;
DECLARE nested ARRAY<STRUCT<key STRING, value INT64>>;

SET​

Assign values to declared variables:

DECLARE total INT64;
SET total = (SELECT COUNT(*) FROM users);

SELECT total AS user_count;

System Variables​

Querylab.io provides completions for BigQuery system variables:

VariableDescription
@@project_idCurrent project
@@dataset_idCurrent dataset
@@row_countRows affected by last DML
@@script.bytes_processedTotal bytes processed
@@script.slot_msTotal slot milliseconds
@@script.num_child_jobsNumber of child jobs
@@error.messageError message (in EXCEPTION blocks)
@@error.statement_textFailed statement text

Type @@ to see all available system variables with their descriptions.

Diagnostics​

Querylab.io validates each statement independently and provides script-level diagnostics.

Per-Statement Validation​

Each statement in the script is validated separately. Errors are highlighted inline with accurate line numbers, even deep in a multi-statement script. You see errors from all statements, not just the first one.

Undropped Temporary Tables​

When a script creates temporary tables but never drops them, Querylab.io shows a warning:

Temporary table 't' is created but never dropped. Add DROP TABLE IF EXISTS t; to clean up. Temp tables persist for up to 24 hours with storage costs.

This diagnostic includes a quick fix — press Cmd+. (Mac) or Ctrl+. (Windows) to automatically insert the DROP TABLE IF EXISTS statement at the end of the script.

The check is smart:

  • Case-insensitive matching (CREATE TEMP TABLE MyTable matches DROP TABLE mytable)
  • Recognizes _SESSION qualifier (DROP TABLE _SESSION.t counts as dropping t)
  • Only warns in multi-statement scripts, not single statements

All Standard Rules Apply​

Every diagnostic rule that works on single statements also works within multi-statement scripts — undefined columns, missing partition filters, unused CTEs, and all others listed in Code Quality.

Copy Notebook as Multi-Statement​

SQL Notebooks can be exported as a single multi-statement script. This is useful when you want to share a notebook as a standalone SQL file or run it outside of Querylab.io.

From the notebook toolbar overflow menu, choose one of two export formats:

Copy SQL as CTEs​

Converts notebook cells into a single query using Common Table Expressions:

Notebook cells:

Cell "params":     DECLARE min_amount INT64 DEFAULT 100;
Cell "big_orders": SELECT * FROM orders WHERE amount > min_amount
Cell "summary":    SELECT user_id, COUNT(*) FROM big_orders GROUP BY 1

Result copied to clipboard:

DECLARE min_amount INT64 DEFAULT 100;

WITH big_orders AS (
  SELECT * FROM orders WHERE amount > min_amount
)
SELECT user_id, COUNT(*) FROM big_orders GROUP BY 1
  • Non-SELECT statements (DECLARE, SET, CREATE, DROP) are placed before the WITH clause
  • Each SELECT cell becomes a named CTE using the cell name
  • The last SELECT cell becomes the main query

Copy SQL as Temp Tables​

Converts notebook cells into a script using temporary tables:

Notebook cells:

Cell "params":     DECLARE min_amount INT64 DEFAULT 100;
Cell "big_orders": SELECT * FROM orders WHERE amount > min_amount
Cell "summary":    SELECT user_id, COUNT(*) FROM big_orders GROUP BY 1

Result copied to clipboard:

DECLARE min_amount INT64 DEFAULT 100;

CREATE TEMP TABLE big_orders AS (
  SELECT * FROM orders WHERE amount > min_amount
);

SELECT user_id, COUNT(*) FROM big_orders GROUP BY 1;

DROP TABLE IF EXISTS big_orders;
  • Non-SELECT statements stay in their original position
  • Each SELECT cell (except the last) is wrapped in CREATE TEMP TABLE
  • DROP TABLE IF EXISTS statements are appended for cleanup
  • The last SELECT cell remains as-is

Both options copy the SQL to your clipboard and offer an "Open in new tab" action in the success notification.

Limitations​

  • Temporary tables expire after ~24 hours — re-create them if your session is long-running
  • DECLARE variables are parsed but cross-statement variable completions are limited (use SQL Notebooks for full variable support with template variables)
  • Control flow statements (IF, WHILE, FOR, LOOP) are parsed but do not affect completions or diagnostics