MySQL学习笔记之多表连接

介绍

多表连接，就是从多张表中查找符合条件的数据交集，包括自连接、内连接、外连接（左外连接、右外连接），顺便介绍下union关键字

自连接

自连接即一张表连接自己，用where语句定义查找条件：

SELECT
    worker.first_name worker_first_name,
    worker.last_name worker_last_name,
    manager.first_name manager_first_name,
    manager.last_name manager_last_name
FROM
    employees worker,
    employees manager
WHERE
    worker.manager_id = manager.employee_id
AND worker.last_name = "Chen";

内连接

内连接，不用指定为Inner，因为多表连接默认就是内连接，求多表的交集。join表连接语句必须配合连接条件语句on，连接语句中的字段（列）称为连接列：

SELECT
    employees.employee_id,
    employees.last_name,
    departments.department_id
FROM
    employees
INNER JOIN departments ON employees.department_id = departments.department_id;

左外连接

返回左表全部，以及连接列为空的数据：

SELECT
    employees.employee_id,
    employees.last_name,
    departments.department_id
FROM
    employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

左外连接，配合where，返回仅存在于左表的数据：

SELECT
    employees.employee_id,
    employees.last_name,
    departments.department_id
FROM
    employees
LEFT JOIN departments ON employees.department_id = departments.department_id
WHERE
    employees.department_id IS NOT NULL;

右外连接

返回右表全部，以及连接列为空的数据：

SELECT
    employees.employee_id,
    employees.last_name,
    departments.department_id
FROM
    employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

右外连接，配合where，返回仅存在于右表的数据：

SELECT
    employees.employee_id,
    employees.last_name,
    departments.department_id
FROM
    employees
RIGHT JOIN departments ON employees.department_id = departments.department_id
WHERE
    employees.department_id IS NULL;

union语句

union语句用于拼接两个SQL查询(要求两个SQL查询返回的列字段的数量和类型完全一样)，此处返回的是仅存在于左表的数据，和仅存在于右表的数据。
另外，union all不会去重，所以比union效率更高：

SELECT
    employees.employee_id,
    employees.last_name,
    departments.department_id
FROM
    employees
LEFT JOIN departments ON employees.department_id = departments.department_id
WHERE
    employees.department_id IS NOT NULL
UNION ALL
    SELECT
        employees.employee_id,
        employees.last_name,
        departments.department_id
    FROM
        employees
    RIGHT JOIN departments ON employees.department_id = departments.department_id
    WHERE
        employees.department_id IS NULL;

求并集

# 返回左表数据和右表数据的并集
SELECT
    employees.employee_id,
    employees.last_name,
    departments.department_id
FROM
    employees
LEFT JOIN departments ON employees.department_id = departments.department_id
UNION ALL
    SELECT
        employees.employee_id,
        employees.last_name,
        departments.department_id
    FROM
        employees
    RIGHT JOIN departments ON employees.department_id = departments.department_id
    WHERE
        employees.department_id IS NULL;

也可以这样：

SELECT
    employees.employee_id,
    employees.last_name,
    departments.department_id
FROM
    employees
RIGHT JOIN departments ON employees.department_id = departments.department_id
UNION ALL
    SELECT
        employees.employee_id,
        employees.last_name,
        departments.department_id
    FROM
        employees
    LEFT JOIN departments ON employees.department_id = departments.department_id
    WHERE
        employees.department_id IS NULL;