柚子茶室
紀錄自學大小事
首頁🏠 標籤🏷️ 系列文📖 關於我🐣
Hugo Theme Diary by Rise
移植自 Makito's Journal.

© Yoziming
- 目錄 -
首頁🏠 標籤🏷️ 系列文📖 關於我🐣
- 目錄 -
子查詢Subquery
尚硅谷MySQL筆記-05
folder MySQL線上課   label SQL  

子查詢Subquery

查中有查，以某查完的結果作為比較項目來查

  • 用一對()括號包起來
  • 子查詢在主查詢之前先執行了一次
  • 子查詢放在比較條件的右側
  • 多行操作就對應多行子查詢

分類

  • 依照內查詢返回的條目數:

    • 單行:返回一條結果
    • 多行:返回多行結果

  • 依照內查詢是否被執行多次:

    • 相關:查詢工資是否大於其部門平均(每個人部門不同，都要查各自的)

    • 不相關:查詢工資是否大於2000(每個人固定跟2000比較)

單行子查詢

  • 內查詢返回一條結果，外查詢跟這條結果比較
  • 比較符:=等於、大小於等於、不等於
  • 舉例:
-- 查詢薪水比"員工ID143號的薪水"還多的
select salary, job_id, employee_id
from employees
where salary > (
    select salary
    from employees
    where employee_id = 143
);

-- 返回"job_id與141號員工相同"且"salary比143號員工多的"
select salary, job_id, employee_id
from employees
where salary > (
    select salary
    from employees
    where employee_id = 143
)
  and job_id = (
    select job_id
    from employees
    where employee_id = 141
);

-- 查詢工資最少的
select last_name,job_id,salary
from employees
where salary=(
    select min(salary)
    from employees
    );

-- 查詢管理人與部門ID跟編號141相同的
select employee_id,manager_id,department_id
from employees
where manager_id=(
    select manager_id
    from employees
    where employee_id= 141
    )
and department_id=(
    select department_id
    from employees
    where employee_id =141
    )
and employee_id !=141;  -- 注意排除自己本身

-- 返回每個部門的最低工資，條件限制"比部門編號50的最低薪資"還高
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(
    select min(salary)
    from employees
    where department_id=50
    );
  • HAVING、CASE也都可以使用
  • 如果子查詢結果為null，那不返回任何東西 (與null比較必為null)

多行子查詢

  • 也稱為集合比較子查詢
  • 內查詢返回多行結果
  • 使用多行比較操作符，因為返回很多條所以沒得等於，不然要等於哪個?
操作符 含義
IN 等於列表中的任意一個
ANY 需要和單行比較操作符一起使用，和子查詢返回的某一個值比較
ALL 需要和單行比較操作符一起使用，和子查詢返回的所有值比較
SOME 實際上是ANY的別名，作用相同，一般常使用ANY
  • 舉例:
-- 返回其它job_id中比job_id為IT_PROG任一工資低的員工的員工號、job_id 以及salary**
-- 其實相當於< IT_PROG中工資的max
select employee_id,job_id,salary
from employees
where salary < any (
    select salary
    from employees
    where job_id = 'IT_PROG'
)
    and job_id != 'IT_PROG';

/* 同理，任一改成所有，即是any改成all
則相當於< IT_PROG中工資的min
*/


-- 查詢平均工資最低的部門id
-- 方法1
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				) dept_avg_sal
			)

-- 方法2
select department_id
from employees
group by department_id
having avg(salary)<=all (
    select avg(salary)
    from employees
    group by department_id
)
  • 如果子查詢結果中包含null，那不返回任何東西
    • 在子查詢中加入is not null可以避免

相關子查詢

  • 內查詢被執行多次，比如:查詢員工工資是否大於該員工的部門平均
    • 每個員工部門可能不同，要先查出他的部門平均，再來比較

在FROM中聲明子查詢

-- 查詢員工工資是否大於該員工的部門平均
select *
from employees e
         natural join (
    select department_id, avg(salary) avg_sal
    from employees
    group by department_id
) t_dept_avg -- 將查詢結果作為一張暫時表，必須起別名
where e.salary > t_dept_avg.avg_sal;

在ORDER BY中聲明子查詢

-- 依照department_name來排序員工
select *
from employees e
order by (
             select department_name
             from departments d
             where e.department_id = d.department_id
         );
  • 除了GROUP BY與LIMIT之外，其他地方都可以聲明子查詢

EXIST與NOT EXIST

返回是否存在

-- 查詢沒有員工的部門
select department_id, d.department_name
from departments d
where not exists(
        select *
        from employees e
        where e.department_id = d.department_id
    );

效率問題

  • 多表連接(比如自連接)效率比子查詢高
  • 能自連接就自連接

課後練習

#1.查詢和Zlotkey相同部門的員工姓名和工資
select e.last_name, e.salary, e.department_id
from employees e
where department_id = (
    select department_id
    from employees
    where last_name = 'Zlotkey'
);

#2.查詢工資比公司平均工資高的員工的員工號，姓名和工資。
select e.employee_id, e.last_name, e.salary
from employees e
where salary > (
    select avg(salary)
    from employees
);

#3.選擇工資大於所有JOB_ID = 'SA_MAN'的員工的工資的員工的last_name, job_id, salary
select last_name, job_id, salary
from employees
where salary > all (
    select salary
    from employees
    where job_id = 'SA_MAN'
);

#4.查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名
select employee_id, last_name
from employees
where department_id in (
    select distinct department_id
    from employees
    where last_name like '%u%'
);

#5.查詢在部門的location_id為1700的部門工作的員工的員工號
select employee_id
from employees
where department_id in (
    select department_id
    from departments
    where location_id = 1700
);

#6.查詢管理者是King的員工姓名和工資
select *
from employees e1,
     employees e2
where e1.manager_id = e2.employee_id
  and e2.last_name = 'King';


#7.查詢工資最低的員工資訊: last_name, salary
select *
from employees
where salary in (
    select min(salary)
    from employees
);

#8.查詢平均工資最低的部門資訊
select *, (SELECT AVG(salary) FROM employees WHERE department_id = departments.department_id)
from departments
where department_id = (
    select employees.department_id
    from employees
    group by employees.department_id
    having avg(salary) <= all (
        select avg(salary)
        from employees
        group by employees.department_id
    )
);

#10.查詢平均工資最高的 job 資訊
select *
from jobs
where job_id = (
    select employees.job_id
    from employees
    group by jobs.job_id
    having avg(salary) >= all (
        select avg(salary)
        from employees
        group by jobs.job_id
    ));


#11.查詢平均工資高於公司平均工資的部門有哪些?
select department_id
from employees
where department_id is not null
group by department_id
having avg(salary) >= (
    select avg(salary)
    from employees
);

#13.各個部門中 最高工資中最低的那個部門的 最低工資是多少?
select *
from departments;

select min(salary)
from employees
where department_id = (
    select department_id
    from employees
    group by department_id
    having max(salary) <= all (
        select max(salary)
        from employees
        group by department_id
    )
);

#14.查詢平均工資最高的部門的 manager 的詳細資訊: last_name, department_id, email, salary
select *
from employees
where employee_id=any(
    select distinct manager_id -- distinct很關鍵，因為平均工資最高的部門的manager可能是複數同一個
    from employees
    where department_id=(
        select department_id
        -- 平均工資最高的部門
        from employees
        group by department_id
        having avg(salary)>=all(
            select avg(salary)
            from employees
            group by department_id
        )
        )
);


#15. 查詢部門的部門號，其中不包括job_id是"ST_CLERK"的部門號
select *
from departments
where not exists(
        select *
        from employees
        where departments.department_id = employees.department_id
          and job_id = 'ST_CLERK'
    );

#16. 選擇所有沒有管理者的員工的last_name
select last_name
from employees
where manager_id is null
;

#17．查詢員工號、姓名、雇用時間、工資，其中員工的管理者為 'De Haan'
select *
from employees
where manager_id in (
    select manager_id
    from employees
    where last_name = 'De Haan'
);

#18.查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資（相關子查詢）
select *
from employees e1
where salary > (
    select avg(salary)
    from employees e2
    where e1.department_id = e2.department_id
);


#19.查詢每個部門下的部門人數大於 5 的部門名稱（相關子查詢）
select *
from departments d
where 5 < (
    select count(*)
    from employees e
    where e.department_id = d.department_id
);


#20.查詢每個國家下的部門個數大於 2 的國家編號（相關子查詢）

select *
from locations;

select *
from locations
where 2 < (
    select count(*)
    from departments d
    where d.location_id = locations.location_id
);

上次修改於 2021-12-20