在 SELECT 语句中,如果 FROM 子句引用了多个表源或视图,可以使用 JOIN 指示指定的联接操作应在指定的表源或视图之间执行。
一、交叉联接:CROSS JOIN
交叉联接将执行一个叉积(迪卡尔乘积),将一个表的每一行与另一个表的所有行进行匹配,生成一个虚拟表。如果一个表有5行,另一个表有6行,结果生成的虚拟表一共得到 5*6=30 行。
(1) ANSI SQL-89 语法
SELECT a.ColumnA, b.ColumnA FROM Table1 AS a , Table2 AS b |
(2) ANSI SQL-92 语法(推荐)
SELECT a.ColumnA, b.ColumnA FROM Table1 AS a CROSS JOIN Table2 AS b |
二、内部联接:[ INNER ] JOIN
内部联接用于返回所有匹配的行对。如果未指定任何联接类型,则默认为内部联接。
内部联接的处理过程包括以下步骤:
a. 首先,像交叉联接一样,生成一个迪卡尔乘积。
b. 然后,再按照谓词进行筛选(放弃两个表中不匹配的行)。
c. 最后,返回所有匹配的行。
(1) ANSI SQL-89 语法
SELECT a.ColumnA, b.ColumnA FROM Table1 AS a , Table2 AS b WHERE a.IDcol = b.IDcol |
(2) ANSI SQL-92 语法(推荐)
SELECT a.ColumnA, b.ColumnA FROM Table1 AS a JOIN Table2 AS b ON a.IDcol = b.IDcol |
三、外部联接
外部联接的处理过程包括以下步骤:
a. 首先,像交叉联接一样,生成一个迪卡尔乘积。
b. 然后,再按照谓词进行筛选(放弃两个表中不匹配的行)。
c. 根据 LEFT/RIGHT/FULL 选项“添加外部行”
d. 最后,返回所有匹配的行。
例如,下面是2个表。
Table1: EmpID FirstName 001 Mark 002 Jim 003 Tom | Table2: EmpID LastName 002 Smith 005 Johnson |
1. 左外联接:LEFT [ OUTER ] JOIN
指定在结果集中包括左表中所有不满足联接条件的行,除了由内部联接返回所有的行之外,还将另外一个表的输出列设置为 NULL。
SELECT a.EmpID, a.FirstName, b.LastName FROM Table1 AS a LEFT OUTER JOIN Table2 AS b ON a.EmpID = b.EmpID |
返回的结果为:
EmpID FirstName LastName 001 Mark (NULL) 002 Jim Smith 003 Tom (NULL) |
2. 右外联接:RIGHT [OUTER] JOIN
指定在结果集中包括右表中所有不满足联接条件的行,除了由内部联接返回所有的行之外,还将与另外一个表对应的输出列设置为 NULL。
返回的结果为:
EmpID FirstName LastName 002 Jim Smith 005 (NULL) Johnson |
3. 全外联接:FULL [ OUTER ] JOIN
指定在结果集中包括左表或右表中不满足联接条件的行,并将对应于另一个表的输出列设为 NULL。这是对通常由 INNER JOIN 返回的所有行的补充。
返回的结果为:
EmpID FirstName LastName 001 Mark (NULL) 002 Jim Smith 003 Tom (NULL) 005(NULL) Johnson |
四、自联接
1. 语法
自联接即联接同一个表的多个实例。自联接支持交叉联接、内部联接和外部连接。例如:
SELECT a.ColumnA, b.ColumnB FROM Table1 AS a JOIN Table1 AS b ON a.IDcol = b.IDcol |
2. 示例
本例首先生成一个表,并在表中存储0到9的整数,然后生成自交叉联接,从而生成1到1000的整数。
(1)构建一个基本表
CREATE TABLE Table1(ColumnA int) INSERT INTO Table1(ColumnA) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9) |
(2)构建自交叉联接
SELECT T1.ColumnA * 100 + T2.ColumnA * 10 + T3.ColumnA + 1 as MyInt FROM Table1 AS T1 CROSS JOIN Table1 AS T2 CROSS JOIN Table1 AS T3 ORDER BY MyInt |
五、复合联接
当两个表具有复合关系时,可能需要复合联接。例如:
SELECT a.ColumnA, b.ColumnB FROM Table1 AS a JOIN Table2 AS b ON a.ColumnA = b.ColumnA AND a.ColumnB = b.ColumnB |
六、多重联接
联接运算仅操作两个表,可以通过多个联接运算实现多重联接。
SELECT a.ColumnA, b.ColumnB, c.ColumnC FROM Table1 AS a JOIN Table2 AS b ON a.ColumnA = b.ColumnA JOIN Table3 AS c AND a.ColumnB = c.ColumnB |