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