hive之full outer join（全连接）使用方法

目录

介绍

语法

例子

创建顾客表：customers

创建订单表：orders

 full outer join语句

  left join + union + right join语句

介绍

full outer join结合了 LEFT JOIN 和 RIGHT JOIN 的结果，并使用NULL值作为两侧缺失匹配结果。

语法

SELECT 
    table1.column_name(s),table2.column_name(s) 
FROM table1 
    FULL OUTER JOIN table2 
ON table1.column_name = table2.column_name;

相当于:left join + union + right join

SELECT 
    table1.column_name(s),table2.column_name(s)
FROM table1 
    LEFT OUTER JOIN table2 
ON table1.column_name = table2.column_name;
UNION
SELECT 
    table1.column_name(s),table2.column_name(s)
FROM table1 
    RIGHT OUTER JOIN table2 
ON table1.column_name = table2.column_name;

例子

创建