CODEWITHSUNDEEP
sqldategoogle-bigqueryleft-join
MC09
MC09

Reputation: 95

Standard SQL: LEFT JOIN by two conditions using BETWEEN

I have the following query in BigQuery:

#Standard SQL

SELECT *
FROM `Table_1`     
LEFT JOIN `Table_2` ON (timestamp BETWEEN TimeStampStart AND TimeStampEnd)

But I get the following Error:

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

If I use JOIN instead of LEFT JOIN, it works, but I want to keep all the rows from Table_1 (so also the ones which aren't matched to Table_2)

How to achieve this?

Upvotes: 0

Views: 3970

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272106

This is absolutely stupid... but the same query will work if you add a condition that matches a column from table1 with a column from table2:

WITH Table_1 AS (
  SELECT CAST('2018-08-15' AS DATE) AS Timestamp, 'Foo' AS Foo
  UNION ALL
  SELECT CAST('2018-09-15' AS DATE), 'Foo'
), Table_2 AS (
  SELECT CAST('2018-08-14' AS DATE) AS TimeStampStart, CAST('2018-08-16' AS DATE) AS TimeStampEnd, 'Foo' AS Bar
)
SELECT *
FROM Table_1
LEFT JOIN Table_2 ON Table_1.Foo = Table_2.Bar AND Table_1.Timestamp BETWEEN Table_2.TimeStampStart AND Table_2.TimeStampEnd

See if you have additional matching criteria that you can use (like another column that links table1 and table2 on equality).

Upvotes: 3

Erwin Smout
Erwin Smout

Reputation: 18408

A LEFT JOIN is always equivalent to the UNION of :

the INNER JOIN between the same two arguments on the same join predicate, and the set of rows from the first argument for which no matching row is found (and properly extended with null values for all columns retained from the second argument)

That latter portion can be written as

SELECT T1.*, null as T2_C1, null as T2_C2, ... FROM T1 WHERE NOT EXISTS (SELECT * FROM T2 WHERE )

So if you spell out the UNION you should be able to get there.

Upvotes: 1

Kaspa Phani Vardhan
Kaspa Phani Vardhan

Reputation: 82

Hi as per the documentation, "(" has a special meaning, so please try without the brackets.

SELECT * FROM Table_1 LEFT JOIN Table_2 ON Table_1.timestamp >= Table_2.TimeStampStart AND Table_1.timestamp <= Table_2.TimeStampEnd

Documentation here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Interesting. This works for me in standard SQL:

select *
from (select 1 as x) a left join
     (select 2 as a, 3 as b) b
     on a.x between b.a and b.b

I suspect you are using legacy SQL. Such switch to standard SQL. (And drop the parentheses after the between.)

The problem is:

#(Standard SQL)#

This doesn't do anything. Use:

#StandardSQL

Upvotes: 0

Related Questions