To select from multiple tables in BigQuery, you use a JOIN (e.g., INNER JOIN, LEFT JOIN) when the tables share a key, or a UNION ALL when you want to stack rows from tables with the same schema. You can also use WITH (CTE) subqueries to combine results step by step, or wildcard tables (table*) to query many tables at once by name pattern.
Why You Need Multiple Tables in BigQuery
BigQuery is a serverless data warehouse, and real-world data is rarely stored in one giant table. You might have separate tables for orders, customers, products, or daily event logs. Querying across them lets you answer questions like “Which products did repeat customers buy last month?” without exporting or merging data elsewhere. BigQuery’s architecture is built for this: it separates storage from compute, so scanning multiple tables in one query is normal and cost-effective if you use partitioning and clustering wisely.
How JOIN Works in BigQuery (with Example)
A JOIN combines columns from two or more tables based on a condition, usually a foreign key. The most common types are:
INNER JOIN– returns rows that match in both tables.LEFT JOIN(orLEFT OUTER JOIN) – returns all rows from the left table, with matching rows from the right; unmatched right columns areNULL.RIGHT JOINandFULL OUTER JOIN– less common but available.CROSS JOIN– every row from table A paired with every row from table B.
Example: You have orders (order_id, customer_id, amount) and customers (customer_id, name). To get order amounts with customer names:
SELECT o.order_id, c.name, o.amount
FROM `myproject.mydataset.orders` AS o
INNER JOIN `myproject.mydataset.customers` AS c
ON o.customer_id = c.customer_id;
Note: Always qualify column names with table aliases (o., c.) to avoid ambiguity. BigQuery uses standard SQL by default; legacy SQL is deprecated.
Using UNION ALL to Stack Tables
When you have multiple tables with the same columns (e.g., daily sales exports), use UNION ALL to combine rows. UNION DISTINCT removes duplicates but is slower and rarely needed. Example:
SELECT order_id, customer_id, amount FROM `sales_2024_01`
UNION ALL
SELECT order_id, customer_id, amount FROM `sales_2024_02`;
Both SELECT statements must have the same number of columns and compatible data types. Column names come from the first SELECT. If you need to add a source identifier, include a literal column:
SELECT order_id, customer_id, amount, '2024_01' AS month FROM `sales_2024_01`
UNION ALL
SELECT order_id, customer_id, amount, '2024_02' FROM `sales_2024_02`;
Wildcard Tables for Many Similar Tables
If you have tables like events_20240101, events_20240102, etc., you can query them all with a wildcard:
SELECT * FROM `myproject.mydataset.events_*`;
Use TABLE_DATE_RANGE or _TABLE_SUFFIX to filter by date suffix. For example, to get only January 2024:
SELECT * FROM `myproject.mydataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131';
Wildcard tables are convenient but can be costly if you accidentally scan many tables. Always filter with _TABLE_SUFFIX or use a partitioned table instead.
CTEs (WITH Clauses) for Complex Queries
Common Table Expressions (CTEs) let you define temporary result sets within a query. They don’t physically create tables, but they make multi-step logic readable. Example: combine a JOIN and a UNION in one query:
WITH recent_orders AS (
SELECT customer_id, amount FROM `orders` WHERE order_date >= '2024-01-01'
),
customer_totals AS (
SELECT customer_id, SUM(amount) AS total FROM recent_orders GROUP BY customer_id
)
SELECT c.name, ct.total
FROM customer_totals ct
LEFT JOIN `customers` c ON ct.customer_id = c.customer_id;
CTEs are not materialized; they are inlined by the query optimizer. For very large datasets, consider creating a materialized view or a separate table if you reuse the same logic.
How to Choose Between JOIN, UNION, and Wildcards
- Use JOIN when you need columns from different tables side by side, linked by a key.
- Use UNION ALL when you need to append rows of identical structure (e.g., monthly partitions).
- Use wildcard tables when you have many tables with a naming pattern and you want to query them as a group, but be careful with cost.
- Use CTEs to organize complex queries, not as a performance trick.
A common misconception: JOIN and UNION are interchangeable. They are not – JOIN adds columns horizontally, UNION adds rows vertically. Mixing them up leads to incorrect results or schema errors.
Common Problems and Performance Tips
- Ambiguous column names – always alias tables and qualify columns.
- Data type mismatches – in UNION, ensure columns are compatible; cast if needed (e.g.,
CAST(amount AS FLOAT64)). - Cross join explosion – missing ON condition in a JOIN creates a Cartesian product, which can be huge and expensive.
- Scanning too much data – filter with WHERE on partitioned columns (e.g.,
_PARTITIONTIMEor a date column) to reduce cost. - Using SELECT * with wildcards – if tables have different schemas, you may get errors; use explicit column lists.
For performance, prefer partitioning and clustering on the join keys. BigQuery’s query optimizer handles joins well, but avoid joining on non-distinct keys that cause massive fan-out.
Cost and Quota Considerations
BigQuery charges per query based on the amount of data scanned, not on compute time. When you join multiple tables, the cost is roughly the sum of the bytes read from each table (after pruning). Wildcard queries can scan many tables, so always filter with _TABLE_SUFFIX or use a partitioned table. On-demand pricing is $5 per TB scanned; flat-rate slots are an alternative for predictable workloads. Free tier includes 1 TB of query data per month. To estimate cost, use the “Query validation” button in the console before running.
Alternatives to Multi-Table SELECT
If you find yourself constantly joining the same tables, consider:
- Denormalized tables – pre-join data into a single table (e.g., using a scheduled query).
- Materialized views – BigQuery automatically maintains a precomputed result set for common aggregations.
- Partitioned tables – instead of separate tables, store all data in one table partitioned by date, then query with a WHERE on the partition column.
- BigQuery BI Engine – for interactive dashboards, it accelerates joins in memory.
These options trade storage cost for query speed and simplicity.
Frequently Asked Questions
Can I join more than two tables in BigQuery?
Yes, you can chain joins: FROM table1 JOIN table2 ON ... JOIN table3 ON .... The order matters for performance; join the smallest tables first or use the optimizer’s hints. For example, joining orders to customers and then to products is fine.
What is the difference between UNION and UNION ALL in BigQuery?
UNION (or UNION DISTINCT) removes duplicate rows across the combined result, while UNION ALL keeps all rows. UNION ALL is faster and cheaper because it avoids a deduplication step. Use UNION ALL unless you specifically need distinct rows.
How do I query tables in a different dataset or project?
Use the fully qualified name with backticks: `project.dataset.table`. If the table is in the same project but different dataset, you can omit the project: `dataset.table`. Always use backticks if the names contain hyphens or special characters.
Why does my JOIN return more rows than expected?
This usually happens when the join key is not unique in one of the tables. For example, if a customer has multiple addresses and you join on customer_id, you get a row for each address. Check for duplicates in the key columns, or use DISTINCT or a subquery to deduplicate first.
Can I use a subquery in the FROM clause to combine tables?
Yes, you can use a subquery as a derived table: SELECT * FROM (SELECT ... FROM table1 UNION ALL SELECT ... FROM table2) AS combined. This is equivalent to a CTE but less readable. CTEs are generally preferred for clarity.
Is there a limit on the number of tables I can join?
BigQuery does not have a hard limit on the number of joins in a query, but there are practical limits on query complexity and bytes scanned. The console may warn about excessive joins. For very large numbers of tables, consider using wildcards or a single partitioned table.
Related
- Excel Weather Forecast: How to Pull Live Data
- Excel Want to Merge? How to Combine Cells
- Excel Vertical Indent: How to Indent Down a Cell
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Add Apps Script to Google Sheets: A Practical GuideTo add Apps Script to a Google Sheet, open the spreadsheet in a web browser and choose Extensions…
- Excel Row Locked While Scrolling? Freeze Panes GuideTo keep a row visible while you scroll in Excel for Microsoft 365 on Windows, select View >…
- Clear Excel Cell Formatting: What Changes and What StaysTo clear formatting in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Clear…
- Balance of Payments Diagram: Structure and ExamplesA balance of payments diagram shows how a country records transactions with the rest of the world in…
- How to Add Animated Text to PowerPoint PresentationsTo add animated text in PowerPoint, select a text box or highlight text, open Animations > Add Animation,…
- How to Insert a Checkbox in Word 365To add a clickable checkbox in Word 365 for Windows, turn on the Developer tab, then choose Developer…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.