实验用表
为进行实验，作者在SQL Server数据库下共建立了t1、t2两张表，两张表除主键id外均有个两字段stu_id和teacher_id。
t1表
| id | stu_id | teacher_id |
|---|---|---|
| 1 | S0001 | T01 |
| 2 | S0002 | T01 |
| 3 | S0003 | T02 |
| 4 | S0004 | T02 |
| 5 | S0005 | T03 |
| 6 | S0002 | T04 |
t2表
| id | stu_id | teacher_id |
|---|---|---|
| 1 | S0002 | T05 |
| 2 | S0003 | T03 |
| 3 | S0006 | T02 |
| 4 | S0007 | T04 |
| 5 | S0005 | T03 |
连接
把两个或多个表根据某一个或多个共同的字段连接起来。连接查询即使用连接后的表进行查询操作。
内连接
以两张表为例，内连接会把两张表连接字段*有的值连接起来，抛弃只在一个表中出现的值
SQL脚本
SELECT t1.stu_id,t2.stu_id FROM t1
INNER JOIN t2
ON t1.stu_id=t2.stu_id;
/*只使用JOIN关键字，默认为内连接*/
SELECT t1.stu_id,t2.stu_id FROM t1
JOIN t2
ON t1.stu_id=t2.stu_id;在SQL Server中结果
| stu_id | teacher_id |
|---|---|
| S0002 | S0002 |
| S0003 | S0003 |
| S0005 | S0005 |
| S0002 | S0002 |
在Mysql中结果
| stu_id | teacher_id |
|---|---|
| S0002 | S0002 |
| S0003 | S0003 |
| S0005 | S0005 |
左外连接
以两张表为例，左外连接会把两张表连接字段*有的值连接起来，同时会把在左表中出现而右表中未出现的值显示出来，在右表中未出现的值以Null表示。
SQL脚本
SELECT t1.stu_id,t2.stu_id FROM t1
LEFT JOIN t2
ON t1.stu_id=t2.stu_id;在SQL Server中结果
| stu_id | teacher_id |
|---|---|
| S0001 | Null |
| S0002 | S0002 |
| S0003 | S0003 |
| S0004 | Null |
| S0005 | S0005 |
| S0002 | S0002 |
在Mysql中结果
| stu_id | teacher_id |
|---|---|
| S0001 | Null |
| S0002 | S0002 |
| S0003 | S0003 |
| S0004 | Null |
| S0005 | S0005 |
右外连接
以两张表为例，右外连接会把两张表连接字段*有的值连接起来，同时会把在右表中出现而左表中未出现的值显示出来，在左表中未出现的值以Null表示。
SQL脚本
SELECT t1.stu_id,t2.stu_id FROM t1
RIGHT JOIN t2
ON t1.stu_id=t2.stu_id;在SQL Server中结果
| stu_id | teacher_id |
|---|---|
| S0002 | S0002 |
| S0002 | S0002 |
| S0003 | S0003 |
| Null | S0006 |
| Null | S0007 |
| S0005 | S0005 |
在Mysql中结果
| stu_id | teacher_id |
|---|---|
| S0002 | S0002 |
| S0003 | S0003 |
| Null | S0006 |
| Null | S0007 |
| S0005 | S0005 |
全连接
以两张表为例，全连接不仅把两张表连接字段*有的值连接起来，同时会把在只在左表或只在右表中出现而另一个表中未出现的值显示出来，未出现的值以Null表示。全连接在Mysql中不受支持。
SQL脚本
SELECT t1.stu_id,t2.stu_id FROM t1
FULL JOIN t2
ON t1.stu_id=t2.stu_id;在SQL Server中结果
| stu_id | teacher_id |
|---|---|
| S0001 | Null |
| S0002 | S0002 |
| S0003 | S0003 |
| S0004 | Null |
| S0005 | S0005 |
| S0002 | S0002 |
| Null | S0006 |
| Null | S0007 |
交叉连接
以两张表为例，交叉连接不需指明连接条件，它把表1中的每一行同表2的全部行连接，如果表1有m行记录，表2有n行记录，则交叉连接会产生m*n行记录
SQL脚本
/*使用CROSS JOIN*/
SELECT t1.stu_id,t2.stu_id FROM t1
CROSS JOIN t2;
/*不使用，查询结果同上*/
SELECT t1.stu_id,t2.stu_id FROM t1,t2;在SQL Server中结果
| stu_id | teacher_id |
|---|---|
| S0001 | S0002 |
| S0002 | S0002 |
| S0003 | S0002 |
| S0004 | S0002 |
| S0005 | S0002 |
| S0002 | S0002 |
| S0001 | S0003 |
| S0002 | S0003 |
| S0003 | S0003 |
| S0004 | S0003 |
| S0005 | S0003 |
| S0002 | S0003 |
| S0001 | S0006 |
| S0002 | S0006 |
| S0003 | S0006 |
| S0004 | S0006 |
| S0005 | S0006 |
| S0002 | S0006 |
| S0001 | S0007 |
| S0002 | S0007 |
| S0003 | S0007 |
| S0004 | S0007 |
| S0005 | S0007 |
| S0002 | S0007 |
| S0001 | S0005 |
| S0002 | S0005 |
| S0003 | S0005 |
| S0004 | S0005 |
| S0005 | S0005 |
| S0002 | S0005 |
在Mysql中结果
| stu_id | teacher_id |
|---|---|
| S0001 | S0002 |
| S0002 | S0002 |
| S0003 | S0002 |
| S0004 | S0002 |
| S0005 | S0002 |
| S0001 | S0003 |
| S0002 | S0003 |
| S0003 | S0003 |
| S0004 | S0003 |
| S0005 | S0003 |
| S0001 | S0006 |
| S0002 | S0006 |
| S0003 | S0006 |
| S0004 | S0006 |
| S0005 | S0006 |
| S0001 | S0007 |
| S0002 | S0007 |
| S0003 | S0007 |
| S0004 | S0007 |
| S0005 | S0007 |
| S0001 | S0005 |
| S0002 | S0005 |
| S0003 | S0005 |
| S0004 | S0005 |
| S0005 | S0005 |
注：根据实验结果可知：在任何连接类型下，Mysql都会自动把结果中出现的重复记录合并为一条记录。