SAP HANA连接类型Join Types

连接类型

• Inner Join      内连接
• Left Outer Join 左外连接
• Right Outer Join    右外连接
• Referential Join    引用/参照连接
• Text Join           文本连接
• Full Outer Join:    全连接
  

假设有以下两表  

创建两表的SQL语句:

-- REPLACE <Schema_Name> WITH YOUR SCHEMA
CREATE COLUMN TABLE <Schema_Name>."CUSTOMER" (
        "CustomerID" nvarchar(10) primary key,
        "CustomerName" nvarchar(50)
);
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C1', 'Alfred');
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C2', 'John');
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C3', 'Maria');
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C4', 'Harry');

CREATE COLUMN TABLE <Schema_Name>."SALES_ORDER" (
        "OrderID" integer primary key,
        "CustomerID" nvarchar(10),
        "Product" nvarchar(20),
        "Total_Units" integer
);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (101, 'C1','Camera',300);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (102, 'C1','Mobile',200);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (103, 'C2','iPod',500);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (104, 'C3','Television',400);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (105, 'C5','Laptop',800);

Inner Join: 两表内互相匹配的记录的才包含在结果集

即

SELECT T2."OrderID", T1."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         INNER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

结果集：

哪里用?

Attribute View: 连接多个主数据表

Analytical Views:参照完整性不能保证的情况下可用内连接 ，参照完整性能保证的情况下使用引用连接比内连接更有效。

Left Outer Join: 左表的记录全部包含在结果集内，如果右表没有匹配项，则右表内的字段值给null

ＳＱＬ

SELECT T2."OrderID", T1."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         LEFT OUTER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

结果集：

Right Outer Join: 右表的记录全部包含在结果集内，如果左表没有匹配项，则左表内的字段值给null

ＳＱＬ

SELECT T2."OrderID", T2."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         RIGHT OUTER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

结果集：

SAP HANA Referential Join: 与内连接一样（假设能保证参照完整性）。

参照完整性：数据库设计原则之一，即表Ａ的外键是表Ｂ的主键，表Ａ的外键值在表Ｂ中都存在。

引用连接与内连接相比功能相同，但前者性能会好点，但前题是能保证参数完整性。

SAP HANA Text Join: 文本连接，获得语言相关数据

Text Join is used in order to get language-specific data.

You have a product table that contains product IDs without descriptions and you have a text table for products that contains language-specific descriptions for each product. You can create a text join between the two tables to get the language-specific details. In a text join, the right table should be the text table and it is mandatory to specify the Language Column.
比如你有一个产品表包含产品ＩＤ，但没有描述；还有另一个描述表，包含产品ＩＤ、语言及描述。这样产品表和描述表间就使用文本连接，一定将文本表设为右表并选定语言列。

Full Outer Join: 返回笛卡尔乘积

Syntax

SELECT T2."OrderID", T1."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         FULL OUTER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";
结果集：

注意   Referential Join and Text Join.只能用在信息视图内

原文地址　http://saphanatutorial.com/sap-hana-join-types/