Oracle 数据库中 FULL OUTER JOIN 的作用 ？

当需要同时显示两个表中所有记录时，FULL OUTER JOIN 就非常有用。

FULL OUTER JOIN 返回左表（Table A）和右表（Table B）的所有行，并且如果左表或右表中没有匹配的行，则使用 NULL 值填充缺失的部分。

 例子：

SELECT 
    '' AS 唯一标识,
    '0' AS 是否汇总,
    '04' AS 数据粒度,
    COALESCE(a.ORG_NO, b.ORG_NO) AS 单位编号,
    NVL(a.WOKER_TOTAL_NUM, 0) AS 工人总数,
    NVL(b.DENSO_NUM, 0) AS 电装表贴数量,
    NVL(b.COMMISSION_BENCH_NUM, 0) AS 调试台数量,
    NVL(b.VIBRATION_TABLE_NUM, 0) AS 振动台数量,
    COALESCE(a.DATA_DATE, b.DATA_DATE, SYSDATE) AS 数据日期,
    COALESCE(a.CREATE_TIME, b.CREATE_TIME, SYSDATE) AS 统计日期
FROM (
    SELECT 
    ORG_NO,
    WOKER_TOTAL_NUM,
    DATA_DATE,
    CREATE_TIME
    FROM (
        SELECT 
            ROW_NUMBER() OVER(PARTITION BY ORG_NO, TO_CHAR(DATA_DATE, 'YYYY') ORDER BY DATA_DATE DESC, CREATE_TIME DESC) rn,
            ORG_NO,
            NVL(WOKER_TOTAL_NUM, 0) AS WOKER_TOTAL_NUM,
            DATA_DATE,
            CREATE_TIME
        FROM BRAIN.DS_WOKER_WORK_HOUR_INFO
    ) a
    WHERE a.rn = 1
) a
FULL OUTER JOIN (
    SELECT 
    ORG_NO,
    DENSO_NUM,
    COMMISSION_BENCH_NUM,
    VIBRATION_TABLE_NUM,
    DATA_DATE,
    CREATE_TIME
    FROM (
        SELECT 
            ROW_NUMBER() OVER(PARTITION BY ORG_NO, TO_CHAR(DATA_DATE, 'YYYY') ORDER BY DATA_DATE DESC, CREATE_TIME DESC) rn,
            ORG_NO,
            NVL(DENSO_NUM, 0) AS DENSO_NUM,
            NVL(COMMISSION_BENCH_NUM, 0) AS COMMISSION_BENCH_NUM,
            NVL(VIBRATION_TABLE_NUM, 0) AS VIBRATION_TABLE_NUM,
            DATA_DATE,
            CREATE_TIME
        FROM BRAIN.DS_PRODUCT_EQUIP_INFO
    ) b
    WHERE b.rn = 1
) b 
ON a.ORG_NO = b.ORG_NO AND TO_CHAR(a.DATA_DATE,'YYYY') = TO_CHAR(NVL(b.DATA_DATE, SYSDATE),'YYYY')

a表执行结果：

b表执行结果：

 FULL OUTER JOIN  关联之后的结果：（a表多了一条：2021 年的数据，b表没有） ，（b表多了一条：2028年的数据 ，a表没有）   。