全连接FULL JOIN－two side outer join

[@more@]

FULL JOIN －two side outer join

Syntax

FROM Table1 [Alias1] FULL JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
SQL92 (explicit declaration of a full or union join)

SELECT Fieldlist FROM Table1 [Alias1] LEFT JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2 UNION
SELECT Fieldlist FROM Table1 [Alias1] RIGHT JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
SQL92 (if a direct declaration of a full or union join is not supported by the database system)

SELECT Fieldlist FROM Table1 [Alias1], OUTER Table2 [Alias2]
WHERE {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2 UNION
SELECT Fieldlist FROM OUTER Table1 [Alias1], Table2 [Alias2]
WHERE {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
Informix before SQL92 (implicit join declaration)

SELECT Fieldlist FROM Table1 [Alias1], Table2 [Alias2]
WHERE ({Table1|Alias1}.Field1 = {Table2|Alias2}.Field2(+)) UNION
SELECT Fieldlist FROM Table1 [Alias1], Table2 [Alias2]
WHERE ({Table1|Alias1}.Field1(+) = {Table2|Alias2}.Field2)
Oracle before SQL92 (implicit join declaration)

Description

The full or the full outer join includes all records from both tables in one resulting row, where the specifyed join expression evaluates as true. Additionally all records from the left table, where is no match in the right table, and all records from the right table, where is no match in the left table, are included in the query results.

Attention, if the database system does not directly support a full join (Syntax 1), all matching records in both tables are selected in the first select statement with the left join and then are selected a second time in the second select statement with the right join. At the and duplicate rows are eliminated with UNION (without ALL, this is importand).

在SAP出来的数据中，用户名的中文名和英文名是用2行分别存储；

USER ID NAME TYPE NAME

---------------------------------------------------------

1 En John

1 Cn 约翰

2 En Ken

2 Cn 肯

3 En davin

4 Cn 纳什

先要把该用户信息ETL到目标表中，目标表的结构成

USER ID EN_NAME CN_NAME

-----------------------------------------------------------

1 John 约翰

2 Ken 肯

3 davin

4 纳什

目前要用一条SQL实现ETL过程：

insert into target_table(user_id,en_name,cn_name)

(

select user_id,en_name,cn_name from

(

select user_id,en_name from src_table where name_type='En' a full join select user_id,cn_name from src_table where name_type='Cn' b on a.user_id=b.user_id

)

)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15086/viewspace-909274/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15086/viewspace-909274/