MySQL查询（三）——连接查询

MySQL查询（三）——连接查询

1. 连接查询

当查询涉及到多个表的字段时会用到连接查询。

笛卡尔乘积现象：表1有m行，表2有n行，结果出现m*n行。笛卡尔乘积现象发生的原因是没有加匹配条件。

• 按功能分类

  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接

• sql92标准只支持内连接，而sql99标准支持内连接+外连接（左外和右外）+交叉连接

• sql92标准的基本语法

  select 查询字段
  from 表1,表2
  where 表1.匹配字段1=表2.匹配字段2;
  

• sql99标准的基本语法

  select 查询字段
  from 表1 [连接类型]
  join 表2
  on 表1.匹配字段1=表2.匹配字段2
  [where 筛选条件];
  

  推荐使用sql99标准。
  连接类型包括内连接（inner）、左外（left）、右外（right）、交叉连接（cross）。

• 下面例子中使用的数据表

  • employees 员工表

  • departments 部门表

  • jobs 工种表

  • locations 地点表

    员工表分别通过部门id、工种id与部门表、工种表关联，部门表通过地点id与地点表关联。

1.1. sql92标准中的内连接

• 等值连接

  多表连接的结果为几个表的交集部分；n个表连接，至少需要n-1个连接条件；多表的顺序没有要求；一般需要为表起别名；连接时可以搭配所有子句使用。

  #查询员工名和对应的部门名
  select last_name,department_name
  from employees,departments
  where employees.department_id=departments.department_id;
  

  • 为表起别名，方便匹配时使用

    #查询员工名和对应的部门名
    select last_name,e.department_id,department_name
    from employees e,departments d
    where e.department_id=d.department_id;
    

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

  • 加筛选条件（筛选条件与匹配条件用and连接）

    #查询有奖金的员工的名字和所属部门名
    select last_name,department_name,commission_pct
    from employees e,departments d
    where e.department_id=d.department_id and e.commission_pct is not null;
    

  • 加分组

    #查询每个部门中员工的最低工资、部门名
    select min(salary),department_name
    from employees e,departments d
    where e.department_id=d.department_id
    group by department_name;
    

  • 加排序

    #查询每个部门的员工个数，并且按员工个数降序
    select count(*) 个数,department_name
    from employees e,departments d
    where e.department_id=d.department_id
    group by department_name
    order by 个数 desc;
    

  • 实现三个表连接

    #查询员工名、部门名、部门所在城市
    select last_name,department_name,city
    from employees e,departments d,locations l
    where e.department_id=d.department_id
    and d.location_id=l.location_id;
    

• 非等值连接

  增加一个工资等级表job_grades，其中包含三个字段grade_level、lowest_sal、highest_sal，设置每个等级的最低工资标准和最高工资标准。

  #查询员工的工资和工资级别
  select salary,grade_level
  from employees e,job_grades g
  where e.salary between g.lowest_sal and g.highest_sal;
  

• 自连接

  一个表与自身连接

  #查询员工名及上级的名字
  select e.employee_id,e.last_name,m.employee_id,m.last_name
  from employees e,employees m
  where e.manager_id=m.employee_id;
  

1.2. sql99标准中的内连接

• 等值连接的完整语法

  select 查询字段
  from 表1 别名
  inner join 表2 别名
  on 连接条件
  where 分组前的筛选条件
  group by 分组依据
  having 对分组后的结果的筛选条件
  order by 排序字段;
  

  inner可以省略。

• 等值连接示例

  #查询员工名、部门名、工种名，并按部门名降序（三表连接）
  select last_name,department_name,job_title
  from employees e
  inner join departments d on e.department_id=d.department_id
  inner join jobs j on e.job_id=j.job_id
  order by department_name desc;
  

  多个表连接时，执行的顺序按照从前往后一个一个的连接，所以对顺序有一定要求，连接的表需要与前面的某个表有可以匹配的字段。

• 非等值连接示例

  #查询员工的工资级别
  select salary,grade_level
  from employees e
  inner join job_grades j
  on e.salary between j.lowest_sal and j.highest_sal;
  

• 自连接示例

  #查询员工的名字，及其上级的名字
  select e.last_name,m.last_name
  from employees e
  inner join employees m
  on e.manager_id=m.employee_id;
  

1.3. sql99标准中的外连接

• 应用场景：当两个表的匹配字段中的值不是完全一样的，一个表中有，另一个表中没有，此时可以使用外连接。

• 特点：外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值；如果从表中没有和它匹配的，则显示null。外连接查询结果=内连接结果+主表中有而从表中没有的记录。

• 左外连接：left join左边的是主表

• 右外连接：right join右边的是主表

• 示例

  #用左外连接查询没有员工的部门名
  select d.department_name
  from departments d
  left join employees e
  on d.department_id=e.department_id
  where e.employee_id is null;
  

  或者

  #用右外连接查询没有员工的部门名
  select d.department_name
  from employees e
  right join departments d
  on d.department_id=e.department_id
  where e.employee_id is null;
  

  通过筛选条件找主表中有，而从表中没有的记录时，使用从表的主键判断空值。

1.4. 全外连接

• 全外连接查询结果=内连接结果+表1中有但表2中没有的+表2中有但表1中没有的

• 示例

  select 查询列表
  from 表1
  full join 表2
  on 表1.key=表2.key
  

1.5. sql99标准中的交叉连接

• 结果是笛卡尔乘积结果，即表1中的每个记录都与表2中的所有记录相连，不需要匹配条件

• 示例

  select e.*
  from employees e
  cross join departments d;