MySQL学习笔记之子查询

自连接方式

自连接就是表A连接表A，通过where关键字实现，比如查询工资比Abel高的员工信息：

SELECT
    e2.last_name,
    e2.salary
FROM
    employees e1,
    employees e2
WHERE
    e1.last_name = "Abel"
AND e2.salary > e1.salary;

子查询

亦称为嵌套查询：

SELECT
    last_name,
    salary
FROM
    employees
WHERE
    salary > (
        SELECT
            salary
        FROM
            employees
        WHERE
            last_name = "Abel"
    );

子查询写在括号内，放在比较条件的右侧。单行操作符对应单行子查询，多行操作符对应多行子查询。

子查询分类

• 角度1：
  单行子查询：只返回一行结果的子查询；
  多行子查询：返回多行结果的子查询。

• 角度2：
  相关子查询：子查询用到了主查询表的字段；
  不相关子查询：子查询用不到主查询表的字段。

相关子查询

SELECT
    last_name,
    salary,
    department_id
FROM
    employees e1
WHERE
    salary > (
        SELECT
            avg(salary)
        FROM
            employees e2
        WHERE
            e1.`department_id` = e2.department_id
    );

也可以在from中声明子查询：

SELECT
    employees.last_name,
    employees.salary,
    employees.department_id
FROM
    employees,
    (
        SELECT
            department_id,
            avg(salary)
        FROM
            employees
        GROUP BY
            department_id
    ) sal_dept
WHERE
    employees.department_id = sal_dept.department_id;

再看一个例子：查询在job_history中出现两次的employee_id对应的last_name、employee_id和job_id：

SELECT
    employees.last_name,
    employees.employee_id,
    employees.job_id
FROM
    employees,
    (
        SELECT
            employee_id,
            count(employee_id) employee_id_count
        FROM
            job_history
        GROUP BY
            employee_id
    ) employee_hist
WHERE
    employee_hist.employee_id = employees.employee_id
AND employee_hist.employee_id_count >= 2;

exists与not exists

exists关键字

exists关键字，用来检查在子查询中是否存在满足条件的行，一经发现，随即返回。
案例：查询公司管理者的信息：

# 方式1：
SELECT DISTINCT
    manager.employee_id,
    manager.last_name,
    manager.job_id,
    manager.department_id
FROM
    employees emp
JOIN employees manager ON emp.manager_id = manager.employee_id;

# 方式2：
SELECT
    employees.employee_id,
    employees.last_name,
    employees.job_id,
    employees.department_id
FROM
    employees,
    (
        SELECT DISTINCT
            manager_id
        FROM
            employees
    ) manager
WHERE
    employees.employee_id = manager.manager_id;

# 方式3：
SELECT
    e1.employee_id,
    e1.last_name,
    e1.job_id,
    e1.department_id
FROM
    employees e1
WHERE
    EXISTS (
        SELECT
            *
        FROM
            employees e2
        WHERE
            e2.manager_id = e1.employee_id
    );

not exists关键字

not exists关键字的含义和exists相反
案例：查询employees表中不存在的department_id和department_name：

# 方式1：
SELECT
    departments.department_id,
    departments.department_name
FROM
    departments
LEFT JOIN employees ON departments.department_id = employees.department_id
WHERE
    employees.department_id IS NULL;

# 方式2：
SELECT
    department_id,
    department_name
FROM
    departments
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            employees
        WHERE
            departments.department_id = employees.department_id
    );

如果一种查询既能用子查询实现，也能用自连接实现，一般选择后者，因为自连接效率更高。