# 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.
Source: https://docs.querylab.io/writing-sql/multi-statement-queries/
**Query 1 · 4.8 GB | ~$0.03**
```sql
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;
```
estimated per statement
## 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](https://docs.querylab.io/writing-sql/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".
**status bar**
Left: a query is running. Right: Cancel was clicked and BigQuery has been asked to stop the job.
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.
**results**
✓ 1: DECLARE threshold INT64
DEFAULT 100;
✓ 2: CREATE TEMP TABLE
active_users AS SELECT…
✓ 3: SELECT country, COUNT(*) AS
users FROM…
✓ 4: DROP TABLE IF EXISTS
active_users;
The script finished; statement 3 is selected because it's the last one that returned rows. Statement 2 shows the row count of the CREATE instead of a grid.
- 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](https://docs.querylab.io/writing-sql/execution-plan/) in the Insights panel follows the selected statement, and so does [lineage](https://docs.querylab.io/writing-sql/lineage/).
## Estimating a script's cost
The status bar's estimate covers the whole script, temp tables included; see [Cost limits](https://docs.querylab.io/writing-sql/cost-limits/#create-temp-table-as-select). 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:
```sql
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;
```
> **Temp tables outlive the script.** BigQuery keeps them for up to 24 hours and bills their storage until then. A script that creates one and doesn't drop it gets a warning on the{" "} CREATE ( Temporary table 'daily_orders' is created but never dropped) with a quick fix, `⌘` `.` → Add DROP TABLE IF EXISTS daily_orders, that appends the statement at the end of the script.{" "} DROP TABLE _SESSION.daily_orders counts as dropped too.
[SQL Notebooks](https://docs.querylab.io/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.
```sql
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:
```sql
DECLARE start_date DATE DEFAULT '2024-01-01';
DECLARE end_date DATE DEFAULT CURRENT_DATE();
DECLARE total INT64;
DECLARE tags ARRAY;
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](https://docs.querylab.io/notebooks/) with template variables is the better fit.
## Checking every statement
Every statement is checked on its own, and every [code-quality rule](https://docs.querylab.io/writing-sql/code-quality/) 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.