Full Join avec des partitions dans PostgreSQL

postgresql, sql
Publié le 2017-02-26 | Jérémy Grèze

We have two tables with a column that partitions the data, for example a date column in the format YYYYY-MM-DD.

We want to join the two tables exhaustively (FULL OUTER JOIN) but in a way that keeps our partitioning and with a condition (a id match for example).

select
    COALESCE("input1"."date", "input2"."date") as "date",
    -- ...
    -- for example:
    "input1"."my_id" IS NOT NULL AS "exist_in_input1",
    "input2"."my_id" IS NOT NULL AS "exist_in_input2",
    COALESCE("input1"."my_id", "input2"."my_id") AS "my_id",

from "input1"
full outer join "input2"
        "input1"."my_id" = "input2"."my_id" -- just an example
    and
        "input1"."date" = "input2"."date"

where -- if you want to build only one partition
    "input1"."date" = '2017-02-26' or "input2"."date" = '2017-02-26'

This is an example with PostgreSQL. But it does not work with MySQL.