Solved: Bigquery: LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join

Problem:

While performing LEFT JOIN on two tables with an inequality condition, BigQuery throws an error. For example:

Query:
SELECT a.* FROM `prod-projects.Dataset2.Employee` a
left join `prod-projects.Dataset2.Accounts` b
on a.value > b.value

Error:
LEFT OUTER JOIN cannot be used without a condition that is an equality of 
fields from both sides of the join.

Solution:

To solve this error, either use at least one column with an equality condition or convert the join to CROSS JOIN

Method #1 Use at least one column with an equality condition

Make sure to have at least one column with equality condition, followed by inequality conditions. For example, convert the below query:

Query:
SELECT a.* FROM `prod-projects.Dataset2.Employee` a
left join `prod-projects.Dataset2.Accounts` b
on a.value > b.value

to 

Query:
SELECT a.* FROM `prod-projects.Dataset2.Employee` a
left join `prod-projects.Dataset2.Accounts` b
On a.Id = b.Id and a.value > b.value

If adding an equality condition is not an option, use the below method

Method #2 Convert LEFT JOIN to CROSS JOIN

You will need to do is use a cross join and then add your join condition in where clause as shown below:

Query:
SELECT a.* FROM `prod-projects.Dataset2.Employee` a
CROSS JOIN `prod-projects.Dataset2.Accounts` b
WHERE a.value > b.value

dan-irving

posted on 13 Mar 19