多表查询的分类 [MySQL][数据库]

多表查询的分类

多表查询可以分为哪几类? (这里我们通过三个角度来分析)

角度一 : 等值连接 vs 非等值连接

角度二 : 自连接 vs 非自连接

角度三 : 内连接 vs 外连接

一. 等值连接和非等值连接

等值连接就是连接条件使用的是: =(等号)进行连接的

非等值连接不是说连接条件是!=(不等于)的,而是指的不是=(等号)的,也就是>(大于),<(小于)也是非等值连接

我们之前在学习多表查询的正确方式时举的例子都是等值连接, 所以这里对于等值连接我们就不再过多说明

这里我们通过一个例子来理解什么是非等值连接

• 在这个例子中我们要引入一个新表: job_grades (这个表是一个工资登记表,在这个工资等级表中我们又每个等级的工资等级对应的工资范围)

eg: 查询员工表中所有员工的姓名,工资,以及工资等级

/*
这里我们可以发现我们对于表中的字段查询的时候前面都给定了这个字段出自哪个表,这里我们要查询的三个字段都只在一个表中独有,如果我们指明字段来自哪个表也是可以执行的,但是站在SQL优化的角度来讲,我们指明字段来自的表之后可以提成一定的程序执行的效率
*/
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
/*
这里就是一个非等值连接,因为这里的连接条件不是=(等号)

这里我们建立连接的过程:
首先我们从e表,也就是employees表中选取记录,然后根据e表中选出的记录的salary字段去j表中匹配,这个时候我们也是在j表中找出一个记录,然后判断我们e表中的记录的salary字段是否是>=(大于等于)j表中的lowest_sal字段,并且小于等于(<=)j表中的highest_sal字段,如果满足条件,那么这两条记录之间就建立了非等值连接
*/
WHERE e.'salary' BETWEEN j.'lowest_sal' AND j.'highest_sal'

• 我们可以发现我们上面建立连接使用的是BETWEEN AND,而不是=(等于运算符),这个时候建立的这种连接就是一种非等值连接

二.自连接与非自连接

自连接就是同一种表自己与自己进行连接

• 注意: 这个时候注意,我们的自连接也是属于多表连接,这个时候我们可以认为我们是同一种表,但是是复制的两张表,这两张表之间进行多表连接

非自连接就是不同的表之间建立连接(eg:员工表与部门表就是两个不同的表)

之前我们学习多表查询的正确方式的时候都是非等值连接,这里我们就对非等值连接不再进行说明

下面我们通过一个例子来理解自连接

eg: 查询员工编号,员工姓名,以及员工对应的管理者的id和姓名

• 这里我们的员工编号再员工表中,员工姓名也在员工表中,管理者id也在员工表中,管理者的姓名其实也是在员工表中,因为管理者也其实就是公司中的一个员工, 这个时候我们要查询管理者姓名的时候其实就是通过在员工表表中查询出的管理者编号和员工编号建立等值连接即可

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.'manger_id' = mgr.'employee_id';

• 这里就是以自连接的方式进行多表查询,这个时候我们可以发现我们的FROM子句中声明了两张表,一张是员工表,一张是管理者表(这里的管理者表其实就是另一种理解上的员工表),并且这两张表其实内容都是employees表,这个时候我们就通过我们定义的员工表中的manger_id去匹配管理者表中的employees_id,通过这种方式建立等值连接
  • 这个时候站在连接条件为=(等号)的角度来将我们的这个多表查询是属于等值连接
  • 这个时候站在是否是一种表自己与自己连接的角度来讲这是一种自连接
  • 其实后面我们还可以发现,这也是一种内连接(并且是SQL92语法之下的内连接)

三.内连接与外连接

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

• 行又称之为: 记录
• 列又称之为: 字段

外连接: 合并具有同一列的两个以上的表的行,结构集中除了包括一个表与另一个表匹配的行之外,还包括左表或右表中不匹配的行

• 外连接又分为了三类:

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

  • 如果是左外连接,则连接条件中左边的表也称之为主表,右边的表称之为: 从表

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

  • 如果是右外连接,则连接条件中右边的表也称之为主表,左边的表称之为: 从表

  ③满外连接 : 两个表在连接的过程中除了返回满足条件的行以外还返回左表和右表中不满足条件的行,这种连接条件称之为满外连接

注意: 上面说的左表中与右表不匹配的行指的是左表中和右表中所有的记录都不匹配的记录,对于这种记录我们进行内连接是查询不到的,我们只有进行左外连接或者满外连接才可以进行查询到

前面我们在多表查询的正确方式中就是使用的内连接的方式

这里我们通过一个例子再来理解内连接

eg:

SELECT employees_id,department_name
FROM employees e,department
WHERE e.'department_id' = d.'department_id';

• 这里就是一种内连接的方式建立的多表连接,这个时候我们假如我们的员工表中有一个员工是没有部门的,这个员工表可能是因为刚刚进入公司,这个时候这个员工还没有定下来在哪个部门中,也就是有一个员工表的部门id为null,那么这个时候通过我们的连接条件: e.‘department_id’ = d.'department_id’我们可以发现我们的员工表表中有一个员工的e.'department’为NULL(也就是一个员工没有部门),那么这个时候就相当于NULL = d.‘department’,这个时候是匹配不成功的,那么这个时候我们的内连接的结果集中是不会包含一个表与另一个表不匹配的行的,那么这个时候结果中就会少了一行,也就是少了这个没有部门的人
• 这里其实是使用SQL92语法下的一个内连接,在SQL92中我们的内连接就是通过WHERE关键字实现的

我们在的MySQL中不支持SQL92语法中的外连接的写法, 但是Oracle是支持的

这里我们来举一个使用SQL92语法的外连接的多表查询

SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.'department_id' = d.'department_id'(+);

• 在SQL92语法中,我们通过使用+(加号)来实现外连接
• 这里我们是左外连接,我们可以发现我们要给右边加上+(加号), 也就是我们如果是左(右)外连接,就给右(左)边加上+(加号)

前面我们讲过的多表查询都是通过WHERE实现的,这里我们要讲,在SQL99语法中使用 JOIN … ON … 的方式实现多表查询,并且在SQL99中的这种JOIN … ON … 的方式同样也能解决外连接的问题,最后,我们的MySQL是支持这种方式的

• SQL92和SQL99语法中都实现了多表查询的方式(内连接和外连接都解决了),并且在MySQL中对于这两种语法下的内连接方式都是支持的 , 但是我们的MySQL中不支持SQL92语法中通过(+)(加号)的实现外连接的方式,而MySQL中支持SQL99中的实现外连接的方式
  • 这里我们要注意: 我们的MySQL中确实是支持SQL99语法下的实现外连接的方式,但是我们要知道,在MySQL中对于左外连接和右外连接我们都可以直接使用SQL99语法下的实现外连接的方式直接实现,但是MySQL中对于满外连接,我们不支持直接使用SQL99语法下的实现的外连接的方式,但是我们可以间接的通过SQL99语法下的实现外连接的方式实现,后面我们会进行举例说明

首先这里我们举例来说明在SQL99语法下如何实现内连接

eg: 查询员工表中的员工姓名和部门名称和部门所在城市

• 员工姓名在员工表中,部门名称在部门表中,部门所在城市在位置表中

SELECT e.last_name,d.department_name,l.city
FROM employees e,JOIN departments d
ON e.'department_id' = d.'department_id'
JOIN Locations l
ON d.'location_id' = l.'location_id';

• 这个时候我们可以发现,我们的查询结果中还是少了没有部门的那个员工,也就是说明这里确实是进行了内连接
• 一个JOIN … ON … 只能连接两个表,所以这里我们使用我们就要使用两个JOIN … ON … 语句连接这三个表
• JOIN … ON … 加载FROM子句的后面
• JOIN后面就是要连接的表,ON后面就是我们的表的连接条件

SQL99语法之下实现左外连接

eg: 查询员工表中的员工姓名,还有对应的部门表中的部门名称

SELECT e.last_name,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.'department_id' = d.'department_id';

• 这里查询出的结果中就包含了左表中不匹配的行,没有部门的哪个员工我们也是可以查询出来的
• 我们可以发现在SQL99语法中我们是通过一个LEFT OUTER JOIN … ON … 来实现左外连接的

SQL99语法之下实现右外连接

eg: 查询员工表中的员工姓名,还有对应的部门表中的部门名称

SELECT e.last_name,d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.'department_id' = d.'department_id';

• 这里查询的结果中就包含了右表中不匹配的行
  • 那么右表中不匹配的行有哪些?
    • 首先我们要知道那个表是右表? 我们说: 在连接条件中处于右边的表就是右表,那么我们在连接条件(ON后面)中可以看到我们的右表是d表,那么d表中有哪些记录和左表是不匹配的?
      • 这个时候我们的左表中(也就是d表(也就是部门表中))有的部门可能是刚刚创建的部门,这个时候在这些部门之内我们都是没有员工的,那么这些部门就是和右表(也就是员工表)是不匹配的,可能这样的新创建的没有员工的部门有6个,这个时候我们使用右外连接的方式就可以将这种没有员工的部门也显示出来
• 我们可以发现在SQL99语法中我们是通过RIGHT OUTER JOIN … ON … 来实现右外连接的

SQL99语法之下实现满外连接(注意: 这里我们是通过SQL99中的实型满外连接的方式直接实现的满外连接,也就是使用FULL OUTER JOIN 直接实现的,我们的MySQL中并不支持这种方式直接实型满外连接)

eg: 查询员工表中的员工姓名,还有对应部门表中的部门名称

SELECT e.last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.'department_id' = d.'department_id';

• 这里直接使用了FULL ORTER JOIN … ON … 的方式来实现满外连接,这种方式在MySQL中是不支持的,我们后面会在 “SQL99语法下的7种JOIN操作” 中讲解如何在MySQL中实现满外连接