FULL JOIN is only supported with merge-joinable join conditions

user_3909896 0
Viewed 1051

I have two Redshift tables which get joined on an id and a date in a specific date range.

When I try to full-outer-join the tables (both alternative SQL statements further down below), I get the following error:

[Amazon](500310) Invalid operation: FULL JOIN is only supported with merge-joinable join conditions; [SQL State=0A000, DB Errorcode=500310]

It works for LEFT-JOIN, but not for FULL-OUTER. I basically want to have all ids and ranges in there, even if there were no matches in the other table. How can I solve this error?

Table 1:

  • id
  • colx (date)

Table 2:

  • id
  • col2 (date range beginning)
  • col3 (date range end)
  • something

First alternative join:

SELECT *
FROM table1 
  FULL OUTER JOIN table2
      ON (table1.id = ltrim(table2.id, '0') -- id
      AND DATE(table1.colx) BETWEEN DATE(table2.col2) AND DATE(table2.col3) -- date range
      )  
 ;

Second alternative join: (according to the AWS documentation, the range is inclusive, so the statements should have the same results I suppose)

SELECT *
FROM table1 
  FULL OUTER JOIN table2
      ON table1.id = ltrim(table2.id, '0') -- id
      AND DATE(table1.colx) >= table2.col2  -- date range beginning
      AND DATE(table1.colx) <= table2.col3  -- date range ending
;
amazon-redshift sql
1 Answers

Does rewriting it using union all fix the problem?

SELECT t1.*, t2.*
FROM table1 t1 LEFT JOIN
     table2 t2
     ON t1.id = ltrim(t2.id, '0') AND 
        DATE(t1.colx) BETWEEN DATE(t2.col2) AND DATE(t2.col3)
UNION ALL
SELECT t1.*, t2.*
FROM table2 t2 LEFT JOIN
     table1 t1
     ON t1.id = ltrim(t2.id, '0') AND
        DATE(t1.colx) BETWEEN DATE(t2.col2) AND DATE(t2.col3)
WHERE t1.id IS NULL;
user_11440350
Related