小译连接类型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

SQL

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

SQL

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: 与内连接一样(假设能保证参照完整性)。

参照完整性:数据库设计原则之一,即表A的外键是表B的主键,表A的外键值在表B中都存在。

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

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

Text Join is used in order to get language-specificdata.

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

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";
结果集:

 

 

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

 

原文地址 http://saphanatutorial.com/sap-hana-join-types/
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值