相关子查询

相关子查询：

• 相关子查询执行流程：

                           如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，

                           并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，

                           这样的子查询就称之为 关联子查询 。相关子查询按照一行接一行的顺序执行，

                           主查询的每一行都执行一次子查询。

 

 

# 回顾：查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary>(
                SELECT AVG(salary)
                FROM employees
             );
​
#查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式1：
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary>(
                SELECT AVG(salary)
                FROM employees
                WHERE department_id=e1.`department_id`
             );

#查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
           SELECT department_name
           FROM departments d
           WHERE e.`department_id`= d.`department_id` 
         );
#结论：在SELECT中，除了GROUP BY 和 LIMIT 之外，其它位置都可以声明子查询！

 

#若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同
#id的员工的employee_id,last_name和其job_id
SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2<=(
            SELECT COUNT(*)
                        FROM job_history j
                        WHERE e.`employee_id`= j.`employee_id` 
                 );

 

• EXISTS 与 NOT EXISTS关键字：

           关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

            如果在子查询中不存在满足条件的行：

                                                   条件返回 FALSE

                                                   继续在子查询中查找

                                                   如果在子查询中存在满足条件的行：

                                                   不在子查询中继续查找

                                                   条件返回 TRUE

          NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

#EXISTS 与 NOT EXISTS关键字
​
#查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式1：自连接
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id=mgr.employee_id;
#方式2：子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN(
                    SELECT DISTINCT manager_id
                     FROM employees
                    );
#方式3：使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
              SELECT *
                            FROM employees e2
                            WHERE e1.`employee_id`=e2.`manager_id`
                            )

 

#查询departments表中，不存在于employees表中的部门的department_id和department_name
#方式1：
SELECT d.department_id,d.department_name
FROM departments d LEFT JOIN employees e
ON e.`department_id`= d.`department_id`
WHERE e.`department_id` IS NULL;
​
#方式2：
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
            SELECT *
                        FROM employees e
            WHERE d.department_id=e.department_id
                                                
                        )