+2 votes
I have 2 datasets (PostgreSQL) to join: "input1", "input2" and the result should be "output".
All datasets are partitioned by date.

How to make a FULL OUTER JOIN and keep the partition?
asked by anonymous

1 Answer

+1 vote

Here it is (with the partition named "date"):

    COALESCE("input1"."date", "input2"."date") as "date",

from "input1"
full outer join "input2"
    ... and "input1"."date" = "input2"."date"

    "input1"."date" = '$DKU_DST_date' or "input2"."date" = '$DKU_DST_date'


answered by
Side note: since it's an OUTER join, SQL keeps all lines from both datasets, even ones that don't match the ON clause, putting NULL values in columns from the other dataset. So adding a condition like "date" = '$DKU_DST_date' in the ON clause does not works, one needs to add it in the WHERE clause.
840 questions
866 answers
992 users