表连接
关系型数据库为达到简单和易于管理的目的，被分解为较小的、更易管理的表。正是由于表被分解为较小的表，它们通过共有字段（主键和外键）形成相互关联的表，并且能够通过这些字段连接在一起。
一、连接查询概念
连接查询是把两个或多个表连接在一起来获取数据，是关系型数据库中最主要的查询。表的连接方式主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点。
同时涉及多个表的查询称为连接查询；用来连接两个表的条件称为连接条件。
二、内连接（INNER JOIN）
1、内连接的概念
内连接是把两个表连接在一起，传回两个数据表相匹配的记录，即两者的交集。
2、内连接的两种写法：
使用 join
SELECT 列名 FROM A INNER JOIN B ON A.N=B.M;
不使用 join
SELECT 列名 FROM A, B WHERE A.N=B.M;
例1：在 demo 数据库中，查询emp, dept两张表，得到empno, ename, job, dname, loc字段信息。
要把两张表里对应的数据同时显示出来，需要把两张表连接在一起：
select empno, ename, job, dname, loc from emp, dept where emp.deptno=dept.deptno;
结果：
例2：将 learnsql 数据库中的表 EMPLOYEE_TBL 和 EMPLOYEE_PAY_TBL 连接起来，查询其中的 EMP_ID 和 DATE_HIRE：
SELECT EMPLOYEE_TBL.EMP_ID, EMPLOYEE_PAY_TBL.DATE_HIRE
FROM EMPLOYEE_TBL INNER JOIN EMPLOYEE_PAY_TBL
ON EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;
或者：
SELECT EMPLOYEE_TBL.EMP_ID, EMPLOYEE_PAY_TBL.DATE_HIRE
FROM EMPLOYEE_TBL, EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;
两种写法的结果是一样的：
3、自连接（SELF JOIN）
自连接是内连接的一种形式，在自连接中，连接的对象是数据表本身，常用于层次数据查询。
语法：使用 join
SELECT 列名 FROM T A INNER JOIN T B ON A.N=B.M;
不使用 join
SELECT 列名 FROM T A, T B WHERE A.N=B.M;
说明：自连接利用表别名在 SQL 语句中对表进行重命名，像处理两个表一样把表连接到自身。
例3：查询 demo_employee 表中所有雇员和其上级领导记录：
SELECT CONCAT(A.姓氏, A.名字) AS '姓名', CONCAT(B.姓氏, B.名字) AS '上级姓名'
FROM demo_employee A, demo_employee B
WHERE A.上级=B.雇员ID;
结果：
在这个例子里，FROM 子句包含了表 demo_employee 两次，让表具有连个别名。这样我们就可以象使用两个不同的表一样进行操作，表 A 象是保存雇员信息的表，表 B 象是保存上级信息的表。所以 JOIN 的条件是比较 A 的上级标识号码和 B 的雇员标识号码。
下面，我们介绍运用自连接从表里删除重复数据的方法。
有时，数据表里有些数据是重复的，需要删除，这时就要用到表的自连接。
例4：demo 表的结构如下：
mysql> desc demo;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| site | varchar(100) | NO | | | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
表里数据如下：
mysql> select * from demo order by id;
+----+---------------------------+
| id | site |
+----+---------------------------+
| 1 | http://www.CodeBit.cn |
| 2 | http://YITU.org |
| 3 | http://www.ShuoWen.org |
| 4 | http://www.CodeBit.cn |
| 5 | http://www.ShuoWen.org |
+----+---------------------------+
5 rows in set (0.00 sec)
如果要删除较旧的重复记录，可以使用下面的语句：
mysql> delete from a
-> using demo as a, demo as b
-> where (a.id > b.id)
-> and (a.site = b.site);
Query OK, 2 rows affected (0.12 sec)
删除重复记录后的表里数据如下：
mysql> select * from demo order by id;
+----+---------------------------+
| id | site |
+----+---------------------------+
| 1 | http://www.CodeBit.cn |
| 2 | http://YITU.org |
| 3 | http://www.ShuoWen.org |
+----+---------------------------+
3 rows in set (0.00 sec)
如果要删除较新的重复记录，可以使用下面的语句：
mysql> delete from a
-> using demo as a, demo as b
-> where (a.id < b.id)
-> and (a.site = b.site);
Query OK, 2 rows affected (0.12 sec)
删除重复记录后的表里数据如下：
mysql> select * from demo order by id;
+----+----------------------------+
| id | site |
+----+----------------------------+
| 2 | http://YITU.org |
| 4 | http://www.CodeBit.cn |
| 5 | http://www.ShuoWen.org |
+----+----------------------------+
3 rows in set (0.00 sec)
可以用下面的语句先确认将被删除的重复记录：
mysql> SELECT a.*
-> FROM demo a, demo b
-> WHERE a.id > b.id
-> AND (a.site = b.site);
+----+---------------------------+
| id | site |
+----+---------------------------+
| 1 | http://www.CodeBit.cn |
| 3 | http://www.ShuoWen.org |
+----+---------------------------+
2 rows in set (0.00 sec)
4、多表连接
例5：多表查询——三表连接：
SELECT S.SName AS 姓名, CS.CName AS 课程, C.Grade AS 成绩
FROM Students AS S
INNER JOIN Sc AS C ON (S.SCode = C.StudentID)
INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)
三、外连接（OUTER JOIN）
1、外连接概念：
与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表（左外连接时）、右表（右外连接时）或两个表（全外连接时）中所有符合搜索条件的数据行，没有配对的数据字段则填入NULL值。
外连接与普通连接的区别：
* 普通连接操作只输出满足连接条件的记录；
* 外连接操作以指定表为连接主体，将主体表中不满足连接条件的记录一并输出。
2、外连接有三种方法：
（1）左外连接
语法：SELECT 列名 FROM A LEFT OUTER JOIN B ON A.N=B.M;
说明：返回 LEFT OUTER JOIN 左侧表里选定字段的全部记录，及右侧表里相匹配记录，不符的部分返回NULL值。
例6：A表 N 字段的数据为1,2,3,4；B表 N 字段的数据为3,4,5：
SELECT A.N as AN, B.N as BN FROM A LEFT OUTER JOIN B ON A.N=B.N;
结果:
例7：查询 demo 数据库中的 emp 表（见图），获取每个雇员和其经理的姓名，包括公司老板本身及所有上面没有经理的雇员。
select A.ename 雇员姓名, B.ename 经理姓名 from emp A left outer join emp B on A.mgr=B.empno;
结果：
2）右外连接
语法： SELECT 列名 FROM A RIGHT OUTER JOIN B ON A.N=B.M;
说明：返回 RIGHT OUTER JOIN 右侧表里选定字段的全部记录，及左侧表里相匹配记录，不符的部分返回NULL值。
例8：从 demo 数据库中的 emp、dept 表里，查询每个雇员的姓名及其所在部门的名称，包括没有雇员的部门。
select E.ename 雇员姓名, D.dname 部门名称 from emp E right outer join dept D on E.deptno=D.deptno;
结果：
（3）全外连接
FULL JOIN 左右侧均为保留数据表。在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN。
语法：SELECT 列名 FROM A FULL JOIN B ON A.N=B.M;
说明：返回 FULL JOIN 左表和右表里选定字段的全部记录，如果没有匹配，这些行同样会列出，不符的部分返回NULL值。
注意：MySQL 5.1以前不支持 FULL JOIN，只有左外连接和右外连接。
四、交叉连接（笛卡尔积）
笛卡尔积是交叉连接的结果。如果从两个或多个没有结合（执行 JOIN 操作）的表里获取数据，输出结果就是所有被选表里的全部记录。
语法： SELECT 列名 FROM A, B;
说明：由于没有指定两表如何结合，结果会把表 A 的每行记录都与表 B 里的全部记录相匹配。
例9：select loc, grade from dept, salgrade;
结果：
loc grade
New York 1
Dallas 1
Chicago 1
Boston 1
Washing 1
New York 2
Dallas 2
Chicago 2
Boston 2
Washing 2
New York 3
Dallas 3
Chicago 3
Boston 3
Washing 3
New York 4
Dallas 4
Chicago 4
Boston 4
Washing 4
New York 5
Dallas 5
Chicago 5
Boston 5
Washing 5
New York 6
Dallas 6
Chicago 6
Boston 6
Washing 6
因为表 dept 有5条记录，salgrade 有6条记录，所以两表交叉连接的结果有5*6共30条记录。
对于交叉连接，也就是笛卡尔积，要特别小心，它是多个表没有进行任何结合的结果，经常会产生大量不必要的数据。因此，在从多个表里获取数据时，一定要根据相关联的字段（通常是主键）把表进行结合。从多个表里获取数据时，强烈建议使用 where 子句。
五、连接查询举例
有学生表 Student(Sno, Sname, Sage, Ssex)，包括学号、姓名、年龄、性别四个字段；
课程表 Course(Cno, Cname, Tno)，包括课程编号、课程名、教师编号三个字段；
成绩表 SC(Sno, Cno, Score)，包括学号、课程编号、成绩三个字段；
教师表 Teacher(Tno, Tname)，包括教师编号、姓名两个字段。
完成如下查询：
1、查询所有学生的选课情况
SELECT SC.Sno, Student.Sname, SC.Cno, Course.Cnam FROM SC,Student,Course Where SC.Sno=Student.Sno and SC.Cno=Course.Cno;
2、查询任何一门课程成绩在70分以上的学生学号、姓名、课程编号和成绩
SELECT distinct Student.Sno, Student.Sname, SC.Cno, SC.Score FROM student,SC WHERE SC.Sno=Student.Sno and SC.Score >=70;
3、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
Select SC.Cno, Student.Sname From SC,Student Where SC.Sno=Student.Sno and SC.Cno='003'and SC.Score>80;
4、查询不同课程成绩相同的学生的学号、课程号、学生成绩
Select distinct A.Sno, A.Cno, B.Score From SC A, SC B Where A.Score=B.Score and A.Cno<>B.Cno;
5、查询所有同学的学号、姓名、选课数、总成绩
Select Student.Sno, Student.Sname, count(SC.Cno), sum(SC.Score) From Student left Outer join SC on Student.Sno=SC.Sno Group by Student.Sno, Student.Sname;
6、查询出只选修了一门课程的全部学生的学号和姓名
Select SC.Sno, Student.Sname, count(SC.Cno) AS 选课数 From SC,Student Where SC.Sno=Student.Sno Group by SC.Sno, Student.Sname Having count(SC.Cno)=1
7、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
Select SC.Sno, Student.Sname, avg(SC.Score) From Student,SC Where Student.Sno=SC.Sno Group by SC.Sno, Student.Sname Having avg(SC.Score) >85;
8、查询课程名称为"数据库"，且分数低于60的学生姓名和分数
Select Student.Sname, SC.Score From Student,SC,Course Where SC.Sno=Student.Sno and SC.Cno=Course.Cno and Course.Cname='数据库'and SC.Score <60;
9、查询不同老师所教的不同课程的平均分，从高到低显示结果
SELECT Z.Tno AS 教师 ID, Z.Tname AS 教师姓名, C.Cno AS 课程 ID, C.Cname AS 课程名称, AVG(T.Score) AS 平均成绩 FROM SC AS T, Course AS C, Teacher AS Z WHERE T.Cno=C.Cno and C.Tno=Z.Tno GROUP BY C.Cno, C.Cname, Z.Tno, Z.Tname ORDER BY AVG(T.Score) DESC;
10、统计列表课程ID、课程名称、各分数段人数。分数段为[100-85],[85-70],[70-60],[<60]
SELECT SC.Cno as 课程ID，Course.Cname As 课程名称,SUM(CASE WHEN SC.Score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100-85],SUM(CASE WHEN SC.Score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85-70],SUM(CASE WHEN SC.Score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70-60],SUM(CASE WHEN SC.Score <60 THEN 1 ELSE 0 END) AS [<60] FROM SC,Course WHERE SC.Cno=Course.Cno GROUP BY SC.Cno, Course.Cname;
【参考资料】
W3SChool 在线SQL教程 http://www.w3SChool.com.cn/sql/sql_join_inner.asp SQL INNER JOIN 关键字
http://www.w3SChool.com.cn/sql/sql_join_left.asp SQL LEFT JOIN 关键字
http://www.w3SChool.com.cn/sql/sql_join_right.asp SQL RIGHT JOIN 关键字
http://www.w3SChool.com.cn/sql/sql_join_full.asp SQL FULL JOIN 关键字
课后练习：
1. 如果不论相关表里是否存在匹配的记录，都要从表里返回记录，应该使用什么类型的结合？
2. JOIN条件位于SQL语句的什么位置？
3. 使用什么类型的结合来判断相关表的记录之间的相等关系？
4. 如果从两个不同的表获取数据，但它们没有结合，会产生什么结果？
5. 在数据库 learnsql 中输入以下代码，研究得到的结果（笛卡尔积）：
SELECT E.LAST_NAME, E.FIST_NAME, EP.DATE_HIRE FROM EMPLOYEE_TBL E, EMLLOYEE_PAY_TBL EP;
6. 输入以下命令来结合表 EMPLOYEE_TBL 和 EMPLOYEE_PAY_TBL:
SELECT E.LAST_NAME, E.FIRST_NAME, EP.DATE_HIRE FROM EMPLOYEE_TBL E, EMLLOYEE_PAY_TBL EP WHERE E.EMP_ID=EP.EMP_ID;
7. 改写练习6里的SQL查询语句，使用 INNER JOIN 语法。
8. 编写一个SQL语句，从表 EMPLOYEE_TBL 返回 EMP_ID、LAST_NAME 和 FIRST_NAME 字段，从表 EMPLOYEE PAY_TBL 返回 SALARY 和 BOUNS 字段。使用两种类型的 INNER JOIN 技术。完成上述查询以后，再进一步计算出每个城市雇员的平均薪水是多少。
9. 使用上节课后练习中建立的数据库 company 进行数据查询：
1）查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。
2）查询薪水大于1200的雇员，按照部门编号进行分组，分组后的平均薪水必须大于1500，查询各分组的平均工资，按照工资的倒序进行排列。
3）查询每个雇员和其所在的部门名。
4）查询每个雇员姓名及其工资所在的等级。
5）查询雇员名第2个字母不是a的雇员的姓名、所在的组名、工资所在的等级。
6）查询每个雇员和其经理的姓名。（提示：用自连接）
7）查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））。（提示：用左外连接）
8）查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）。（提示：用右外连接）
10. 使用学生成绩数据库 stuscore 进行查询练习，使用表的连接关系：
1）查询出所有所代课的教师的姓名及其科目名称。
2）查询出所有教师的姓名及其所代科目的名称。
3）查询出考过试的所有男学生的姓名和平均成绩。
4）查询出所有学生的姓名及平均成绩，如果该生没有参加考试，平均成绩用0分代替。