Query execution failed: - FULL OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join

[LookerExperts]

Knowledge Drop

Last tested: Aug 28, 2020
 

We see this happen in Standard SQL (BigQuery uses Standard SQL as the default dialect). It comes up when we have multiple conditions for one join using OR - apparently Standard SQL doesn't like this.

The workaround here is to join this table multiple times, once for each new condition. If we're using multiple conditions due to null values, we can then add a COALESCE() like:

COALESCE(table1.customer_name,table1_alias1.customer_name,table1_alias2.customer_name)

in the SELECT statement.

If we're writing this in the Explore definition itself, we'll want to make use of the from parameter (joins) and join the same underlying view multiple times to the base explore - then, we'll want to make sure that we bring the correct fields into the explore per joined view, depending on which of the multiple conditions each view is associated with.

The cited Stackoverflow article helps describe the behavior a bit further with a full example.

cite: Stackoverflow

This content is subject to limited support.