SQL数据查询——连接查询

什么是连接查询？

连接查询也称多表查询，是涉及两个以上的表的查询，也是数据库查询中最普遍的一种查询。

其中包括等值连接查询、自然连接查询、非等值连接查询、自身连接（自连接）查询、外连接（左外连接、右外连接）查询和复合条件查询等。

查询格式：

• 用SQL语句进行连接查询时，在FROM子句中选择需要连接的多个表。
• 那么如何选择在连接的属性呢？WHERE子句中用来连接两个表的条件称为连接条件或连接谓词。
• 除此之外，还可以用连接查询特定的语句，即JOIN ON子句。只需要在JOIN后面添加要连接的表，在ON后面添加连接条件，在特定的情况下变换格式就可以实现所有的连接查询。

代码格式：

#使用FROM WHERE子句时的格式：
SELECT <属性名>  #可选择多个
FROM <表名>   #选择多个
WHERE [<表名1>.]<属性名1> <比较运算符> [<表名2>.]<属性名2> #连接条件
#使用JOIN ON子句时的格式
SELECT <属性名>        #可选择多个
FROM <表名1> JOIN <表名2> #选择要连接的表（两个）
ON [<表名1>.]<属性名1> <比较运算符> [<表名2>.]<属性名2>  #连接条件
JOIN ...... ON ......   #可继续连接多个表
WHERE [<表名1>.]<属性名1> <运算符> <限定条件>     #其他限定条件

一、等值和非等值连接查询

1.等值连接查询

什么是等值连接查询：

• 例如下面的三张表，Student表存储的是学生的基本信息，Course表存储的是每一门课的课号以及先行课的课号，SC表存储的是学生的选课信息和考试成绩。
  
  
• 学生表和课程表有相同的字段，但是如果要寻找某个学生的姓名对应的成绩，则需要先找出姓名对应的学号，再到课程表找到成绩
  
• 等值查询就是解决这种问题，可以把两个表使用相同的字段连接称为一个大表，然后利用投影选择需要的属性列。可以理解为在等值连接下的查询。
  

示例：

#查询每个学生及其选修课程的情况（方式一）
SELECT *
FROM student, sc
WHERE student.`Sno`=sc.`Sno`;
#查询每个学生及其选修课程的情况（方式二）
SELECT student.`Sno`, student.`Sname`,sc.`Cno`
FROM student, sc
WHERE student.`Sno`=sc.`Sno`;
#使用JOIN ON格式
SELECT student.`Sno`, student.`Sname`,sc.`Cno`
FROM student JOIN sc
ON student.`Sno`=sc.`Sno`;

方式一：

方式二：

2.非等值连接查询

什么是非等值连接查询：

• 在某些查询情况中，可能不会查询某个值在另一个表中对应的准确信息
• 而需要查询表中的值在另一个表中对应的范围
• 这种连接条件运算符是=之外的连接查询称为非等值查询

示例：

#查询有工资员工的工资等级
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

二、自连接与非自连接

上面涉及到的所有操作都是非自连接，那么什么是自连接呢？

• 连接操作不仅可以在两个表之间进行，还可以是一个表与其自己进行连接，称为表的自身连接（自连接）
• 例如下面的员工表，如果要查询员工id,员工姓名及其管理者的id和姓名
• 需要先找到每个员工的管理者的id，再根据管理者的id到这张表中找到对应的名字

示例：

#自连接通过给一个表起两个别名来实现
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`; #连接条件为这个表的不同字段的比较

三、内连接与外连接

1.内连接

什么是内连接：

合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

• 上面的操作都是内连接
• 比如上面的Course表和SC表中都有Cno字段，但是并不是所有的课程都被学生选择了
• 如果将这两个表通过课号Cno进行连接，只会出现在两个表中都出现的课号
• 这种只出现两个连接字段公共元组的情况为内连接

示例：

#查询学生的选课情况
SELECT *
FROM sc JOIN course   #课程表和选课表以课号连接
ON sc.`Cno`=course.`Cno`

2.外连接

什么是外连接：

在关系代数这一篇文章中介绍了定义，在这里再举例说明一下

• 对于上面内连接示例介绍的情况，如果要把所有的课程都加进去
• 则需要用外连接，即把悬浮元组也保存到结果关系中，在其他属性上填空值（NULL）的连接就是外连接

示例：

#查询学生的选课情况
SELECT *
FROM sc RIGHT JOIN course #这里使用右外连接
ON sc.`Cno`=course.`Cno`

使用左外连接还是右外连接？

左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。

右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。

• 这个问题没有固定的答案，需要看连接表的位置
• 一般来说，当左表数据要超出右表数据时，使用左外连接
• 当左表数据要小于右表数据时，使用右外连接

满外连接

当我们需要的数据在左右表都有没匹配的情况的时候，需要用到满外连接

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN代替。

#满外连接：mysql不支持FULL OUTER JOIN (Oracle)
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

四、UNION 的使用

什么时候使用UNION关键字？

当我们有多条SELECT查询语句，并且需要将这几个查询结果合并成为一个大表时（合并查询结果），我们需要用到UNION

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

使用 UNION 还是 UNION ALL ？

差别：

• UNION：会执行去重操作
• UNION ALL:不会执行去重操作

结论：

• 如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

五、各种形式集合关系的SQL实现

对于中间的图，很显然是内连接，不涉及不匹配的数据。

对于左上图，是左外连接，右上图是右外连接，在上面已经介绍了。

对于左中图，是左外连接去掉中间的内连接部分的情况，所以如果在实例中，需要明确中间部分是什么数据，进而知道用什么样的条件可以限制这些数据。右中图同样的道理。

对于左下图是满外连接，根据上一节的介绍，可以使用 UNION ALL 将左上图与右中图，或者由上图与左中图进行合并。要尽可能避免去重，所以要尽量选择没有公共部分的集合。

当然，如果使用Oracle，也可以使用 FULL OUTER JOIN

对于右下图，在左下图的基础上去掉公共部分，此操作与中图相同。

六、语法差异

1. SQL99中，NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段 ，然后进行等值连接 。但是不够灵活，自然连接是所有相同的字段都进行等值连接
   如果是在SQL92中，则需要手动输入连接条件，并且去除重复字段
2. 在Oracle中，可以使用+来实现外连接，而MySQL不可以
3. 在Oracle中使用FULL OUTER JOIN实现满外连接，而MySQL中使用UNION来实现
4. 在SQL99中，使用USING关键字可以将两个表中的同名字段进行连接

# 案例一、SQL99语法的新特性1:自然连接
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;      #SQL92语法格式
# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。
# 但是不够灵活，自然连接是所有相同的字段都进行等值连接
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;  #SQL99语法格式
# 案例二、Oracle与MySQL外连接语法
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+); #Oracle
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;   #MySQL
# 案例三、Oracle与MySQL满外连接语法
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d  #Oracle
ON e.`department_id` = d.`department_id`;
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL                   #MySQL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
# 案例四、SQL99语法的新特性2:USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;     #SQL92语法格式
# USING替换连接条件，当使用两个表中的字段名一样时，可以使用
# 不适用于自连接，因为两个字段名字不一样
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);              #SQL99语法格式

注意事项

1. 出现笛卡尔积的错误：
   当未注明连接条件时，系统会默认进行笛卡尔积，所以我们要注意连接条件
2. 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
   从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。
3. 可以给表起别名，在SELECT和WHERE中使用表的别名。
   一般来说为了方便都会给表起别名，如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
4. 注意查询条件个数
   如果有n个表实现多表的查询，则需要至少n-1个连接条件