Datawise
Practical SQL

Joining with USING vs ON in BigQuery

Constantin LunguUpdated 2 min read

Photo by Lance Grandahl on Unsplash

What's the difference when joining with ON vs USING clause in BigQuery SQL flavor? I was quite surprised to see USING when moving from SQLServer.

In short:
➡ USING allows you to join tables where the columns you want to join on have the same names and you just test for equality: table1.column = table2.column. You enumerate these columns in the USING clause :

USING (columnA, columnB, etc)

➡ ON is the more general one, doing everything what USING does (but you need to spell out that equality), but also allowing you to join on inequalities, transforming on the fly in joining, filtering for a value, and pretty much any other way you'd need joining.

ON table1.columnA = table2.columnA etc

So yes, USING is pretty much syntactic sugar for a fairly common type of join, but still narrower in functionality than the good old ON.

It's worth pointing out that with USING, the columns in the clause do not need an alias for disambiguation (making clear which one of the two tables we take the column from), effectively doing the same a COALESCE of the columns in the two tables would do.
This helps you a little bit with FULL OUTER JOINS for example. Of course, for other columns, if there are clashes in the namespace, you do need to specify where do you want them sourced from.

Input data: a products table with size, color, product_id and variant (product 100 in sizes XL to XS and colors Blue, Gray and Green, variants such as XL-Blue), and an orders table with order_id, product_id, quantity, variant and country_code: eight orders of product 100 plus order 9 with NULLs.

SELECT
  o.order_id,
  product_id,
  variant,
  o.country_code,
  o.quantity,
  p.color,
  p.size

FROM orders o

LEFT JOIN products p USING (product_id, variant)
SELECT
  o.order_id,
  o.product_id,
  o.variant,
  o.country_code,
  o.quantity,
  p.color,
  p.size

FROM orders o

LEFT JOIN products p ON o.product_id = p.product_id AND
                        o.variant = p.variant

Result of both queries, with columns order_id, product_id, variant, country_code, quantity, color and size: orders 1 to 8 get the color and size of their variant (XL-Green is Green, XL; L-White is White, L), and order 9 is NULL in every column.

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