Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 37 ยท Querying and Functions

SELECT EXCEPT, REPLACE and Friends

Master the BigQuery SELECT extras: SELECT * EXCEPT and REPLACE, IF, IFNULL, CASE, SAFE_DIVIDE, GENERATE_ARRAY, TABLESAMPLE and trailing commas, with runnable stand-ins for the portable parts.

GoogleSQL follows the SQL standard and then adds quality-of-life features that make long analytical queries shorter and safer. This lesson collects the ones you will use daily: SELECT * EXCEPT, SELECT * REPLACE, IF, IFNULL, SAFE_DIVIDE, COUNTIF, sampling and a few syntax conveniences.

The features that exist only in BigQuery are shown read-only. For the ideas that have a portable twin (CASE, COALESCE, NULLIF), runnable examples use our stand-in shop.

EXCEPT and REPLACE

When a table has 60 columns and you want 58 of them, listing all 58 is silly, and SELECT * costs money for the ones you do not need. BigQuery lets you subtract columns:

SQL
SELECT * EXCEPT (raw_payload, internal_notes)
FROM `my-project.shop.customers`;

EXCEPT removes columns from the star, so those two are not read, which means they are not billed either. REPLACE swaps the definition of a column while keeping its name and position:

SQL
SELECT * REPLACE (
  LOWER(email) AS email,
  ROUND(price, 0) AS price
)
FROM `my-project.shop.products`;

You can combine them: SELECT * EXCEPT (a) REPLACE (b + 1 AS b). A related trick is that you can reference a table alias with a star, as in SELECT o.*, c.country FROM orders AS o JOIN customers AS c ....

IF, IFNULL, NULLIF and COALESCE

BigQuery has short forms for common conditionals:

Function Meaning Portable twin
IF(cond, a, b) If cond then a else b CASE WHEN cond THEN a ELSE b END
IFNULL(x, y) x unless x is NULL COALESCE(x, y)
COALESCE(a, b, c) First non-NULL same
NULLIF(a, b) NULL if a equals b same

CASE and COALESCE run here:

SQL Stand-in: CASE, COALESCE and NULLIF ยท database: company

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

In BigQuery the band could be written more briefly using IF nested, but CASE is clearer for three or more branches. SQLite also has IIF, similar to IF:

SQL Stand-in: IIF as a short IF ยท database: shop

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

SAFE_DIVIDE and the SAFE prefix

Division by zero is an error in BigQuery and stops the whole query. SAFE_DIVIDE(a, b) returns NULL instead:

SQL
SELECT
  campaign,
  SAFE_DIVIDE(clicks, impressions) AS click_rate
FROM `my-project.ads.daily`;

Any scalar function can be called as SAFE.function_name(...) to turn its errors into NULL, for example SAFE.LOG(0) or SAFE.PARSE_DATE(...). In SQLite division by zero already gives NULL; to make the same guard explicit, use NULLIF on the divisor, which is portable and works in BigQuery as well:

SQL Stand-in: guard a division with NULLIF ยท database: shop

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

COUNTIF, LOGICAL_AND and conditional aggregation

COUNTIF(cond) counts rows where a condition is true; it replaces SUM(CASE WHEN ... THEN 1 ELSE 0 END):

SQL
SELECT
  COUNT(*)                         AS orders,
  COUNTIF(status = 'cancelled')    AS cancelled,
  SUM(IF(status = 'delivered', total, 0)) AS delivered_revenue
FROM `my-project.shop.orders`;

The portable version with a filter is:

SQL Stand-in: conditional counts ยท database: shop

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

Generating rows

BigQuery can create sequences without a table, which is handy for calendars and test data:

SQL
SELECT n FROM UNNEST(GENERATE_ARRAY(1, 5)) AS n;

SELECT day
FROM UNNEST(GENERATE_DATE_ARRAY('2025-01-01', '2025-01-07')) AS day;

(UNNEST is explained in Arrays and UNNEST.) On SQLite, a recursive CTE does the same job:

SQL Stand-in: generate a number sequence ยท database: shop

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

Sampling and trailing commas

To explore a huge table cheaply, sample a percentage of its storage blocks:

SQL
SELECT *
FROM `my-project.events.pageviews` TABLESAMPLE SYSTEM (1 PERCENT);

This scans about 1 percent of the data, and so is actually cheaper (unlike LIMIT). The result is random per block, not per row, so it is for exploration, not for statistics.

GoogleSQL also tolerates a trailing comma in the select list, which makes editing queries nicer:

SQL
SELECT
  id,
  name,
  price,   -- a trailing comma is fine in BigQuery
FROM `my-project.shop.products`;

Useful smaller features

  • GROUP BY ALL groups by every non-aggregated column in the select list, and ORDER BY can use column positions, a shortcut for quick work (avoid positions in production code).
  • A WITH clause (CTE) can be referenced several times in one query.
  • SELECT AS STRUCT and SELECT AS VALUE shape rows into structs or scalar values (covered in Structs).
  • Comments: --, # and /* */ all work.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SELECT * EXCEPT (a, b) do for cost?

    Excluding columns reduces the bytes scanned, just like not selecting them.
  2. 2. What does SAFE_DIVIDE(a, 0) return?

    SAFE_DIVIDE returns NULL on division by zero instead of failing the query.
  3. 3. Which is the portable equivalent of IFNULL(x, y)?

    COALESCE returns the first non-NULL argument, which is what IFNULL does for two arguments.
  4. 4. Does TABLESAMPLE SYSTEM (1 PERCENT) reduce bytes scanned?

    Unlike LIMIT, block sampling avoids reading most of the table.

Summary

  • EXCEPT and REPLACE shorten wide queries and skip unneeded columns.
  • IF, IFNULL, COUNTIF, SAFE_DIVIDE and the SAFE. prefix make logic compact and safe.
  • GENERATE_ARRAY/GENERATE_DATE_ARRAY make rows from nothing.
  • TABLESAMPLE samples cheaply; LIMIT does not.

Next: Aggregates and GROUP BY in BigQuery