MySQL 多表查询的分类及UNION的使用

一.多表查询的分类

• 角度1:

等值连接 与 非等值连接

• 角度2:

自连接 与 非自连接

• 角度3:

内连接 与 外连接

1.等值连接与非等值连接

上述示例连接条件为'=',即为等值连接,而连接连接条件也为等值的.不为等值的统称为得等值连接.如!= ,<,>等

#: 非等值连接的栗子:

查询员工在job_grade中的salary等级

SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grade j
WHERE e.`salary` BETWEEN j.`wowest_sal` AND j.`highest_sal`;

2.自连接与非自连接

我们在多表查询的实现中的实例即为非自连接,多个不同的表之间连接;如果实现自己与自己的连接,即称为自连接

#: 自连接栗子:

查询员工id,员工姓名及其管理者的id和姓名

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`;

3.内连接与外连接

• 内连接:

  合并具有同一列的两个遇上表的行,结果集中不包含一个表与另一个表的不匹配的行.即只返回满足条件的字段

  SELECT employee_id,department_name
  FROM employees,departments
  WHERE employees.`department_id` = departments.department_id;

• 外连接:

  两个表在连接过程中,除了返回满足条件的行外,还返回左(或右)表中不满足条件的行,这种连接称为左(或右)连接.没有匹配的行时,结果表中相应的列为空(NULL).

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

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

  • 满外连接:

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

    • MySQL中不支持SQL99语法中FULL JOIN ON 的语法,Oracle支持

• SQL99语法中使用 JOIN ...ON 的方式实现多表的查询,这种方式能解决内连接的问题,也能实现外连接

  • 使用SQL99语法实现内连接:

    SELECT last_name,department_name
    FROM employees e JOIN departments d
    ON e.`department_id` = d.`department_id`; 

    • 这里每加入一个表就需要写一个JOIN ON语句,INNER 和 OUTER 可以省略

  • 使用SQL99语法实现外连接:

    • 左外连接:

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

    • 右外连接:

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

    二.UNION的使用

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

  • 语法格式:

  • 执行 UNION ALL 语句时所需的资源比UNION语句少.如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复数据时,尽量使用UNION ALL 语句,以用来提高数据查询的效率

    SELECT column...FROM table1
    UNION(ALL)
    SELECT column...FROM table2

    1.UNION操作符

    返回两个查询结果并集,去除重复记录

    2.UNION ALL 操作符

    返回两个查询结果集的并集,对于两个结果的重读部分,不去重

    注意:
         执行 UNION ALL 语句时所需的资源比UNION语句少.如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复数据时,尽量使用UNION ALL 语句,以用来提高数据查询的效率