Datawise
BigQuery Window Functions

Controlling ordering of NULL values in the ORDER BY clause

Constantin LunguUpdated 1 min read

Photo by Markus Spiske on Unsplash

Here's something I found out about the ORDER BY clause in BigQuery SQL the other day.

Check out the NULLS FIRST / NULLS LAST clauses. What do they do? They control how to treat NULL values when sorting.

While these are entirely optional, they're actually already happening behind the scenes even if you don't specify them.

🔹 ORDER BY [column] ASC (which is the default) uses NULLS FIRST if unspecified
🔹 ORDER BY [column] DESC uses NULLS LAST if unspecified

WITH input_data AS (

  SELECT 1 AS order_id, 100 AS order_amount, 'Customer 1' AS customer_id UNION ALL
  SELECT 2 AS order_id, 50 AS order_amount, 'Customer 2' AS customer_id UNION ALL
  SELECT 3 AS order_id, NULL AS order_amount, 'Customer 3' AS customer_id UNION ALL
  SELECT 4 AS order_id, 90 AS order_amount, 'Customer 4' AS customer_id UNION ALL
  SELECT 5 AS order_id, NULL AS order_amount, 'Customer 5' AS customer_id UNION ALL
  SELECT 6 AS order_id, 90 AS order_amount, 'Customer 6' AS customer_id

)
SELECT order_id, order_amount, customer_id

FROM input_data

ORDER BY order_amount DESC NULLS FIRST

BigQuery results: orders 3 and 5 with a null order_amount come first, then order 1 (100), orders 4 and 6 (90) and order 2 (50).

Enjoyed this? Here are some related articles you might find useful: