168大数据

标题: SAP HANA连接类型Join Types [打印本页]

作者: 乔帮主 时间: 2014-11-10 13:46
标题: SAP HANA连接类型Join Types
本帖最后由乔帮主于 2014-11-10 13:50 编辑

连接类型

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/

欢迎光临 168大数据 (http://bi168.cn/)