Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 37 ยท Querying and Functions

Joins, CTEs and Scripting in BigQuery

Combine tables in BigQuery with INNER, LEFT, FULL and CROSS joins, use CTEs and subqueries, set operations, DECLARE variables, IF and LOOP scripts, temp tables and stored procedures.

Real questions need several tables: orders and customers, events and users, products and prices. This lesson covers combining tables with joins, structuring long queries with CTEs, and BigQuery's scripting language, which lets you declare variables and run several statements in one job.

Join types

BigQuery supports the standard join family. Here is a quick picture:

flowchart LR
  A["INNER: only matching rows"] --- B["LEFT: all left rows plus matches"]
  B --- C["RIGHT: all right rows plus matches"]
  C --- D["FULL OUTER: everything from both"]
  D --- E["CROSS: every combination"]

Choose the join by asking which side's rows must never disappear.

SQL Stand-in: INNER JOIN ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

A LEFT JOIN keeps customers who have no orders; the order columns become NULL. Use it to find "customers who never bought":

SQL Stand-in: customers with no orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

If this returns nothing, every customer in the sample has ordered; the pattern is what matters. A NOT EXISTS subquery gives the same answer and is often clearer:

SQL Stand-in: the same with NOT EXISTS ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Joins and multiplication

A join on a non-unique key multiplies rows. Joining orders to order items gives one row per item, so summing shipping after the join counts shipping once per item:

SQL Stand-in: the fan-out trap ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The fix is to aggregate each side to the join grain before joining (see the CTE pattern below). This mistake is the most common cause of wrong totals in warehouse reports.

USING and cross joins

JOIN b USING (customer_id) joins on a column that has the same name in both tables and outputs it once. In BigQuery, a CROSS JOIN is also written with a comma, and CROSS JOIN UNNEST(array_col) flattens arrays (you will use it in chapter 4).

Cost of joins in BigQuery

Joins shuffle data between workers. BigQuery chooses between a broadcast join (small table sent to every worker, fast) and a hash join (both sides repartitioned, slower). You help it by:

  • Putting the largest table first in the FROM clause.
  • Filtering both sides before the join.
  • Selecting only the needed columns.
  • Joining on integers or short strings, and avoiding functions on join keys (ON LOWER(a) = LOWER(b) is slower).

CTEs: WITH clauses

A common table expression names a subquery so the main query reads top to bottom:

SQL Stand-in: aggregate first, then join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Each step has one job: items to orders, orders to customers, customers get names. This style is easier to test and fix. Note that BigQuery may evaluate a CTE once per reference (it is not guaranteed to be materialised), so for an expensive step used many times, consider a temporary table.

Recursive CTEs

WITH RECURSIVE walks hierarchies such as an org chart. BigQuery supports it with the standard syntax, and so does our stand-in:

SQL Stand-in: walk an org chart ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Set operations

UNION ALL stacks results (fast, keeps duplicates); UNION DISTINCT removes duplicates; INTERSECT DISTINCT and EXCEPT DISTINCT compare sets. BigQuery requires the keyword DISTINCT or ALL; the bare UNION form is an error. Column counts and types must match by position.

Scripting: variables, IF and loops

A BigQuery script is several statements separated by semicolons, run as one job. Variables use DECLARE and SET (read-only, BigQuery-only):

SQL
DECLARE cutoff DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);
DECLARE total_orders INT64;

SET total_orders = (
  SELECT COUNT(*) FROM `my-project.shop.orders` WHERE order_date >= cutoff
);

IF total_orders = 0 THEN
  SELECT 'No orders in the last 30 days' AS message;
ELSE
  SELECT total_orders AS orders_last_30_days;
END IF;

Other scripting pieces:

  • WHILE ... DO ... END WHILE and LOOP with LEAVE and ITERATE.
  • FOR record IN (SELECT ...) DO ... END FOR to loop over query rows.
  • BEGIN ... EXCEPTION WHEN ERROR THEN ... END for error handling.
  • BEGIN TRANSACTION; ... COMMIT TRANSACTION; for multi-statement transactions.
  • EXECUTE IMMEDIATE to run SQL built from a string (parameterise with USING).

Create a temporary table to hold an intermediate result for the whole script:

SQL
CREATE TEMP TABLE recent AS
SELECT * FROM `my-project.shop.orders`
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY);

SELECT status, COUNT(*) FROM recent GROUP BY status;

Temporary tables disappear when the script ends and are not billed for storage.

Stored procedures and functions

Wrap reusable logic as a procedure or a user-defined function (UDF):

SQL
CREATE OR REPLACE FUNCTION `my-project.utils.clean_email`(e STRING) AS (
  LOWER(TRIM(e))
);

SELECT `my-project.utils.clean_email`('  [email protected] ') AS email;

SQL UDFs are fast. JavaScript UDFs (LANGUAGE js) allow arbitrary logic but are slower and best kept for cases SQL cannot handle.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which join finds customers who have no orders?

    A left join keeps unmatched customers and gives NULLs for order columns, which you then filter on.
  2. 2. Why can summing a column after joining a one-to-many table give wrong totals?

    Aggregate to the right grain before joining to avoid fan-out.
  3. 3. What must you write with UNION in BigQuery?

    GoogleSQL requires you to say whether duplicates are kept.
  4. 4. How long does a TEMP TABLE live?

    Temporary tables are dropped automatically when the script finishes.

Summary

  • Choose joins by which side's rows must be kept; aggregate to the join grain first.
  • Put the big table first, filter early, and select only needed columns.
  • CTEs structure queries; recursive CTEs walk hierarchies.
  • Scripting adds DECLARE, SET, IF, loops, temp tables, transactions and EXECUTE IMMEDIATE.
  • SQL UDFs and procedures package reusable logic.

Next: Arrays and UNNEST