Teradata 简明教程

Teradata - Joins

Join 用于将来自多张表的数据记录组合在一起。表是根据这些表中公共栏位/值相连接的。

Join is used to combine records from more than one table. Tables are joined based on the common columns/values from these tables.

有不同类型的 Join 可用。

There are different types of Joins available.

  1. Inner Join

  2. Left Outer Join

  3. Right Outer Join

  4. Full Outer Join

  5. Self Join

  6. Cross Join

  7. Cartesian Production Join

INNER JOIN

Inner Join 组合来自多张表的数据记录，并返回同时存在于这两张表中的值。

Inner Join combines records from multiple tables and returns the values that exist in both the tables.

Syntax

以下是 INNER JOIN 语句的语法。

Following is the syntax of the INNER JOIN statement.

SELECT col1, col2, col3….
FROM
Table-1
INNER JOIN
Table-2
ON (col1 = col2)
<WHERE condition>;

Example

考虑以下员工表和薪资表。

Consider the following employee table and salary table.

EmployeeNo

FirstName

LastName

JoinedDate

DepartmentNo

BirthDate

101

Mike

James

3/27/2005

1

1/5/1980

102

Robert

Williams

4/25/2007

2

3/5/1983

103

Peter

Paul

3/21/2007

2

4/1/1983

104

Alex

Stuart

2/1/2008

2

11/6/1984

105

Robert

James

1/4/2008

3

12/1/1984

EmployeeNo

Gross

Deduction

NetPay

101

40,000

4,000

36,000

102

80,000

6,000

74,000

103

90,000

7,000

83,000

104

75,000

5,000

70,000

以下查询在公共栏位 EmployeeNo 上连接 Employee 表和 Salary 表。每个表都被赋予别名 A 和 B，并且栏位使用正确的别名进行引用。

The following query joins the Employee table and Salary table on the common column EmployeeNo. Each table is assigned an alias A & B and the columns are referenced with the correct alias.

SELECT A.EmployeeNo, A.DepartmentNo, B.NetPay
FROM
Employee A
INNER JOIN
Salary B
ON (A.EmployeeNo = B. EmployeeNo);

当执行以上查询时，将会返回以下记录。员工 105 未包含在结果中，因为它在 Salary 表中没有匹配的记录。

When the above query is executed, it returns the following records. Employee 105 is not included in the result since it doesn’t have matching records in the Salary table.

*** Query completed. 4 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
 EmployeeNo   DepartmentNo     NetPay
-----------  ------------   -----------
    101           1            36000
    102           2            74000
    103           2            83000
    104           2            70000

OUTER JOIN

LEFT OUTER JOIN 和 RIGHT OUTER JOIN 也合并来自多张表的结果。

LEFT OUTER JOIN and RIGHT OUTER JOIN also combine the results from multiple table.

  1. LEFT OUTER JOIN returns all the records from the left table and returns only the matching records from the right table.

  2. RIGHT OUTER JOIN returns all the records from the right table and returns only matching rows from the left table.

  3. FULL OUTER JOIN combines the results from both LEFT OUTER and RIGHT OUTER JOINS. It returns both matching and non-matching rows from the joined tables.

Syntax

以下为 OUTER JOIN 语句的语法。你需要使用 LEFT OUTER JOIN、RIGHT OUTER JOIN 或 FULL OUTER JOIN 中的一个选项。

Following is the syntax of the OUTER JOIN statement. You need to use one of the options from LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN.

SELECT col1, col2, col3….
FROM
Table-1
LEFT OUTER JOIN/RIGHT OUTER JOIN/FULL OUTER JOIN
Table-2
ON (col1 = col2)
<WHERE condition>;

Example

考虑以下 LEFT OUTER JOIN 查询示例。它返回 Employee 表中的所有记录以及 Salary 表中的匹配记录。

Consider the following example of the LEFT OUTER JOIN query. It returns all the records from Employee table and matching records from Salary table.

SELECT A.EmployeeNo, A.DepartmentNo, B.NetPay
FROM
Employee A
LEFT OUTER JOIN
Salary B
ON (A.EmployeeNo = B. EmployeeNo)
ORDER BY A.EmployeeNo;

当执行以上查询时，将生成以下输出。对于员工 105，NetPay 值为 NULL，因为它在 Salary 表中没有匹配的记录。

When the above query is executed, it produces the following output. For employee 105, NetPay value is NULL, since it doesn’t have matching records in Salary table.

*** Query completed. 5 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
 EmployeeNo  DepartmentNo     NetPay
-----------  ------------   -----------
    101           1           36000
    102           2           74000
    103           2           83000
    104           2           70000
    105           3             ?

CROSS JOIN

Cross Join 将左表中的每行与右表中的每行进行连接。

Cross Join joins every row from the left table to every row from the right table.

Syntax

以下是 CROSS JOIN 语句的语法。

Following is the syntax of the CROSS JOIN statement.

SELECT A.EmployeeNo, A.DepartmentNo, B.EmployeeNo,B.NetPay
FROM
Employee A
CROSS JOIN
Salary B
WHERE A.EmployeeNo = 101
ORDER BY B.EmployeeNo;

当执行上述查询时，它会生成以下输出。Employee 表中的 EmployeeNo 101 会与 Salary 表中的每条记录进行联接。

When the above query is executed, it produces the following output. EmployeeNo 101 from Employee table is joined with each and every record from Salary Table.

*** Query completed. 4 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
 EmployeeNo  DepartmentNo   EmployeeNo    NetPay
-----------  ------------  -----------  -----------
    101           1            101         36000
    101           1            104         70000
    101           1            102         74000
    101           1            103         83000