BigQuery SQL: JOIN ON vs USING Clause Differences

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.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

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

Joining with USING vs ON in BigQuery

Comments

Practical SQL

Another look at LOGICAL_AND & LOGICAL_OR in BigQuery

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

Another look at LOGICAL_AND & LOGICAL_OR in BigQuery

More from this blog