Teradata 简明教程
Teradata - SET Operators
集合运算符结合了来自多个 SELECT 语句的结果。这可能看起来类似于联接，但联接合并了来自多个表的列，而集合运算符合并了来自多行中的行。
SET operators combine results from multiple SELECT statement. This may look similar to Joins, but joins combines columns from multiple tables whereas SET operators combines rows from multiple rows.
Rules
-
The number of columns from each SELECT statement should be same.
-
The data types from each SELECT must be compatible.
-
ORDER BY should be included only in the final SELECT statement.
UNION
UNION 语句用于结合来自多个 SELECT 语句的结果。它忽略了重复项。
UNION statement is used to combine results from multiple SELECT statements. It ignores duplicates.
Syntax
以下是 UNION 语句的基本语法。
Following is the basic syntax of the UNION statement.
SELECT col1, col2, col3…
FROM
<table 1>
[WHERE condition]
UNION
SELECT col1, col2, col3…
FROM
<table 2>
[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 |
以下 UNION 查询结合了 Employee 和 Salary 表中的 EmployeeNo 值。
The following UNION query combines the EmployeeNo value from both Employee and Salary table.
SELECT EmployeeNo
FROM
Employee
UNION
SELECT EmployeeNo
FROM
Salary;执行查询后，会产生以下输出。
When the query is executed, it produces the following output.
EmployeeNo
-----------
101
102
103
104
105UNION ALL
UNION ALL 语句类似于 UNION，它结合了来自多个表的包括重复行在内的结果。
UNION ALL statement is similar to UNION, it combines results from multiple tables including duplicate rows.
Syntax
以下是 UNION ALL 语句的基本语法。
Following is the basic syntax of the UNION ALL statement.
SELECT col1, col2, col3…
FROM
<table 1>
[WHERE condition]
UNION ALL
SELECT col1, col2, col3…
FROM
<table 2>
[WHERE condition];Example
以下是 UNION ALL 语句的一个示例。
Following is an example for UNION ALL statement.
SELECT EmployeeNo
FROM
Employee
UNION ALL
SELECT EmployeeNo
FROM
Salary;执行以上查询后，会产生以下输出。你可以看到它还返回了重复项。
When the above query is executed, it produces the following output. You can see that it returns the duplicates also.
EmployeeNo
-----------
101
104
102
105
103
101
104
102
103INTERSECT
INTERSECT 命令也用于结合来自多个 SELECT 语句的结果。它返回了在第二个 SELECT 语句中具有对应匹配项的第一个 SELECT 语句中的行。换句话说，它返回了同时存在于这两个 SELECT 语句中的行。
INTERSECT command is also used to combine results from multiple SELECT statements. It returns the rows from the first SELECT statement that has corresponding match in the second SELECT statements. In other words, it returns the rows that exist in both SELECT statements.
Syntax
以下是 INTERSECT 语句的基本语法。
Following is the basic syntax of the INTERSECT statement.
SELECT col1, col2, col3…
FROM
<table 1>
[WHERE condition]
INTERSECT
SELECT col1, col2, col3…
FROM
<table 2>
[WHERE condition];Example
以下是 INTERSECT 语句的一个示例。它返回了同时存在于这两个表中的 EmployeeNo 值。
Following is an example of INTERSECT statement. It returns the EmployeeNo values that exist in both tables.
SELECT EmployeeNo
FROM
Employee
INTERSECT
SELECT EmployeeNo
FROM
Salary;执行以上查询后，会返回以下记录。EmployeeNo 105 被排除在外，因为它不存在于 SALARY 表中。
When the above query is executed, it returns the following records. EmployeeNo 105 is excluded since it doesn’t exist in SALARY table.
EmployeeNo
-----------
101
104
102
103MINUS/EXCEPT
MINUS/EXCEPT 命令结合了来自多个表的行，并返回存在于第一个 SELECT 中但不存在于第二个 SELECT 中的行。它们都返回相同的结果。
MINUS/EXCEPT commands combine rows from multiple tables and returns the rows which are in first SELECT but not in second SELECT. They both return the same results.