MYSQL學習筆記26: 多表查詢|子查詢

多表查詢|子查詢

---

行子查詢

查詢與張無忌工資相同, 且直屬領導相同的員工

#寫法1
select * from emp
    where salary = (select salary from emp where name='張無忌')
    and managerId = (select managerId from emp where name='張無忌');
#可以合併起來,寫入一個集合
select * from emp
    where (salary, managerId) = (select salary,managerId from emp where name = '張無忌');

---

表子查詢

子查詢返回的結果是一張表, 多行多列

查詢與楊逍或小白薪資和職位相同的員工

#據說資料庫調優中能不用in就不用in
select * from emp
    where (salary, job) in (select salary,job from emp where name in ('楊逍','小白'));

查詢在2004-05-24之後入職的對應的員工和部門資訊

#where語句的書寫位置
#用表子查詢獲取的表代替emp
select *
from (select * from emp where entryDate>'2004-05-24') e
left join dept d
on e.dept_id = d.id;

---