Knowledge Drop

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

  • 7 July 2021
  • 0 replies
  • 136 views

Userlevel 4

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.                

 

 


0 replies

Be the first to reply!

Reply