MySQL多表连接查询——等值连接、非等值连接和自连接

MySQL多表连接查询

1. 含义：

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象： 表1 有m行，
                              表2 有n行，那么结果=m*n行

笛卡尔乘积现象：如果连接条件省略或无效的连接条件，导致现象出现
解决办法：添加有效的连接条件

2. 分类：

按年代分类：
           sql92标准：仅仅支持内连接
           sql99标准：【推荐使用】：支持内连接、外连接（左外和右外）、交叉连接
按功能分类：
           内连接：等值连接 、非等值连接 、 自连接
           外连接：左外连接 、 右外连接 、 全外连接
           交叉连接.

一、sql92标准

1) 等值连接

特点：
     ① 多表等值连接的结果为多表的交集部分
     ② n表连接，至少需要n-1个连接条件
     ③ 多表不分主次，没有顺序要求
     ④ 一般为表起别名，提高阅读性和性能
     ⑤ 可以搭配排序、分组、筛选….等子句使用

# 查询员工名和对应的部门名

1. 为表起别名

好处：
      ①提高语句的简洁度；
      ②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

#查询员工名、工种号、工种名

这里需要用表名来限定
但如果反复用表名去限定，语句更复杂
因此，可以为表名起别名

2. 两个表的顺序可以调换
#查询员工名、工种号、工种名

3. 可以添加筛选条件
#查询有奖金的员工名、部门名

#查询城市名中第二个字符为o的部门名和城市名

4. 可以添加分组
#查询每个城市的部门个数

5. 可以添加排序
#查询每个工种的工种名和员工的个数，并且按员工个数降序

6. 三表连接

2) 非等值连接

3) 自连接

#查询员工名和上级的名称

二、sql99语法：通过join关键字实现连接

1. 含义： 1999年推出的sql语法
2. 语法：
            select 查询列表
            from 表1 别名 【连接类型】
            join 表2 别名 on 连接条件
          【where 筛选条件】
          【group by 分组字段】
          【having 分组后的筛选条件】
          【order by 排序的字段或表达式】
3. 分类：
           内连接 ：inner
           外连接 ：
                       左外 ： left 【outer】
                       右外 ：right【outer】
                       全外 ： full 【outer】
            交叉连接 ：cross join

1> 内连接：

语法：
         select 查询列表
         from 表1 别名
         inner join 表2 别名
         on 连接条件 ；
分类：
         等值
         非等值
         自连接

A. 等值连接

特点：
       ①添加排序、分组、筛选
       ②inner可以省略
       ③筛选条件放在where后面， 连接条件放在on后面，提高分离性，便于阅读(sql99的好处)
       ④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

1.查询员工名、部门名

注：两个表调换顺序，结果一样

2.查询名字中包含e的员工名和工种名（添加条件）

3.查询部门个数>3的城市名和部门个数（添加分组+筛选）
① 查询每个城市的部门个数
② 在①结果上筛选满足条件的

4.查询员工个数>3的部门名和员工个数，且按个数降序的部门（添加排序）

5.查询员工名、部门名、工种名，并按部门名降序（三表连接）

B. 非等值连接
查询工资级别的个数>20的个数，并且按工资级别降序

C. 自连接
查询员工的名字、上级的名字

2> 外连接

用于查询一个表中有，另一个表中没有的记录

特点：
      ①外连接的查询结果为主表中的所有记录
          *如果从表中有和它匹配的，则显示匹配的值
          *如果从表中没有和它匹配的，则显示null
          *外连接查询结果=内连接结果(交集部分)+主表中有而从表中没有的(用null填充)记录
      ②左外连接，left join左边的是主表
          右外连接，right join右边的是主表
      ③左外和右外交换两个表的顺序，可以实现同样的效果
      ④全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

左外连接 ：
#查询没有员工的部门
注：查询的信息主要来自哪个表，哪个就是主表

添加条件 ：

使用id是主键 绝对不为空

右外连接 ：

注 ： 全外链接（ MySQL不支持 ）

3> 交叉连接

sql92 VS sql99
功能：    sql99支持的较多
可读性： sql99实现连接条件和筛选条件的分离，可读性强