多表查詢有兩大類(lèi)：連接查詢和子查詢。
連接查詢又分為隱式/顯式內(nèi)連接和左/右外連接。
子查詢又分為標(biāo)量子查詢、列子查詢、行子查詢和表子查詢。
多表查詢是建立在單表查詢的基礎(chǔ)之上的，因此需要熟練單表查詢?？梢詤⒖?#xff1a;【JavaWeb后端學(xué)習(xí)筆記】MySQL的數(shù)據(jù)查詢語(yǔ)言（Data Query Language，DQL）
本文案例所需資料來(lái)自黑馬程序員：多表查詢
開(kāi)始前需要運(yùn)行01.數(shù)據(jù)準(zhǔn)備.sql腳本中的代碼，準(zhǔn)備表和數(shù)據(jù)。

1、連接查詢

1.1 內(nèi)連接

內(nèi)連接相當(dāng)于查詢A、B兩個(gè)表交集部分?jǐn)?shù)據(jù)。
內(nèi)連接有兩種：隱式內(nèi)連接、顯示內(nèi)連接。他們的語(yǔ)法如下：

-- 隱式內(nèi)連接語(yǔ)法
select 字段列表 from 表1,表2 where 連接條件...;-- 顯示內(nèi)連接語(yǔ)法
select 字段列表 from 表1 [inner] join 表2 on 連接條件...;

由于現(xiàn)在是多表查詢的情況，因此在返回字段列表中，需要使用表名.字段名的格式。
連接條件是用來(lái)消除多余的笛卡爾積。一般會(huì)使用邏輯外鍵消除笛卡爾積。

下面分別使用隱式內(nèi)連接和現(xiàn)實(shí)內(nèi)連接的方式查詢員工的姓名以及所屬部門(mén)名稱(chēng)。

-- A. 查詢員工的姓名 , 及所屬的部門(mén)名稱(chēng) (隱式內(nèi)連接實(shí)現(xiàn))
-- tb_emp表中的dept_id字段是關(guān)聯(lián)到tb_dept表的邏輯外鍵，可以通過(guò)邏輯外鍵消除多余的笛卡爾積。
select tb_emp.name as name, tb_dept.name as department from tb_emp, tb_dept where tb_emp.dept_id = tb_dept.id;-- B. 查詢員工的姓名 , 及所屬的部門(mén)名稱(chēng) (顯式內(nèi)連接實(shí)現(xiàn))
-- 顯式內(nèi)連接只是在代碼編寫(xiě)上將內(nèi)連接展現(xiàn)出來(lái)，效果與隱式內(nèi)連接相同
-- 顯示內(nèi)連接的連接條件寫(xiě)在 on 后面。
select tb_emp.name as name, tb_dept.name as department from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;

這里可以注意一點(diǎn)，員工表中有17條數(shù)據(jù)，但是查出來(lái)的結(jié)果只有16條，缺少了員工姓名為“陳友諒”的數(shù)據(jù)，這是因?yàn)椤瓣愑颜彙钡膁ept_id為null，他沒(méi)有對(duì)應(yīng)的部門(mén)，在部門(mén)表中沒(méi)有id為null的數(shù)據(jù)，因此在內(nèi)連接查詢中，連接條件是不成立的，所以查詢不到“陳友諒”的數(shù)據(jù)。但是為了保證員工數(shù)據(jù)的完整性，雖然“陳友諒”沒(méi)有工作，也任然需要把他查詢出來(lái)，這個(gè)時(shí)候就需要使用外連接查詢。

1.2 外連接

外連接分為：左外連接和右外連接。
左外連接：查詢左表所有數(shù)據(jù)（包括兩張表的交集）；
右外連接：查詢右表所有數(shù)據(jù)（包括兩張表的交集）；
外連接語(yǔ)法如下：

-- 左外連接語(yǔ)法
select 字段列表 from 表1 left [outer] join 表2 on 連接條件...;-- 右外連接語(yǔ)法
select 字段列表 from 表1 right [outer] join 表2 on 連接條件...;

外連接的連接條件也是寫(xiě)在 “on” 后面。
下面分別使用左外連接和右外連接查詢員工表所有員工的姓名, 和對(duì)應(yīng)的部門(mén)名稱(chēng)：

-- A. 查詢員工表 所有 員工的姓名, 和對(duì)應(yīng)的部門(mén)名稱(chēng) (左外連接)
-- 使用左外連接，為了查詢出所有員工數(shù)據(jù)，將員工表tb_emp放在左邊
select tb_emp.name as 員工姓名, tb_dept.name as 部門(mén)名稱(chēng) from tb_emp left join tb_dept on tb_emp.dept_id = tb_dept.id;-- B. 查詢部門(mén)表 所有 部門(mén)的名稱(chēng), 和對(duì)應(yīng)的員工名稱(chēng) (右外連接)
-- 使用右外連接，為了查詢出所有員工數(shù)據(jù)，將員工表tb_emp放在右邊
select tb_emp.name as 員工姓名, tb_dept.name as 部門(mén)名稱(chēng) from tb_dept right join tb_emp on tb_emp.dept_id = tb_dept.id;

從查詢結(jié)果可以發(fā)現(xiàn)，雖然“陳友諒”的dept_id為null，但是該員工任然查詢出來(lái)了。這是因?yàn)橥膺B接保證了查詢出某一邊表格的所有數(shù)據(jù)。

2、子查詢

SQL語(yǔ)句中嵌套select語(yǔ)句，稱(chēng)為嵌套查詢，又稱(chēng)子查詢。
子查詢語(yǔ)法：

-- 子查詢語(yǔ)法(形式不固定)
select * from t1 where column1 = (select column2 from t2 ...);select * from (select * from t1 where 條件查詢條件) t left join t2 on 連接條件;......

子查詢外部的語(yǔ)句可以是insert/update/delete/select的任何一個(gè)，最常見(jiàn)的是select

子查詢	返回結(jié)果及常用操作符
標(biāo)量子查詢	返回的結(jié)果為單個(gè)值；常用操作符：= 、<>、>、>=、<、<=
列子查詢	返回的結(jié)果為一列；常用操作符：in、not in等
行子查詢	返回的結(jié)果為一行；常用操作符：=、<>、in、not in
表子查詢	返回的結(jié)果為多行多列；常用操作符：in

2.1 標(biāo)量子查詢

標(biāo)量子查詢返回的結(jié)果為單個(gè)值。
可以與返回結(jié)果比較大小，因此常用操作符有：= 、<>、>、>=、<、<=。

案例1：查詢 “教研部” 的所有員工信息。
分析：首先需要查詢出教研部的id，然后使用員工表中的dept_id與教研部id判斷是否相等。

-- 查詢 "教研部" 的所有員工信息
-- (select id from tb_dept where name = '教研部') 標(biāo)量子查詢查詢出教研部id
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');

案例2：查詢?cè)?“方東白” 入職之后的員工信息。
分析：首先查詢出姓名為 "方東白"的員工的入職時(shí)間，然后與該入職時(shí)間比較。

-- 查詢?cè)?"方東白" 入職之后的員工信息
-- (select entrydate from tb_emp where name = '方東白') 標(biāo)量子查詢查詢出"方東白" 入職時(shí)間
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方東白');

2.2 列子查詢

列子查詢返回的結(jié)果為一列。
列子查詢返回結(jié)果相當(dāng)于一個(gè)列表或者集合，可以判斷某個(gè)字段值是否在這個(gè)列表之中，因此常用操作符有：in、not in等。

案例：查詢 “教研部” 和 “咨詢部” 的所有員工信息。
分析：需要查詢出"教研部" 和 "咨詢部"的id，組成一個(gè)集合。通過(guò)判斷員工所屬的dept_id是否在這個(gè)集合內(nèi)來(lái)查詢結(jié)果。

-- 查詢 "教研部" 和 "咨詢部" 的所有員工信息。
-- (select id from tb_dept where tb_dept.name in ('教研部', '咨詢部') 列子查詢返回'教研部' 和 '咨詢部'的id集合
select * from tb_emp where dept_id in (select id from tb_dept where tb_dept.name in ('教研部', '咨詢部'));

2.3 行子查詢

行子查詢返回的結(jié)果為一行。
行子查詢返回結(jié)果為表中一行完整數(shù)據(jù)或部分?jǐn)?shù)據(jù)，可以與這一行數(shù)據(jù)的任意字段進(jìn)行比較，常用操作符有：=、<>、in、not in。

案例：查詢與 “韋一笑” 的入職日期 及 職位都相同的員工信息 。
分析：首先需要查詢出 “韋一笑” 的入職日期 及 職位。然后根據(jù)這些信息進(jìn)行判斷。由于要對(duì)兩個(gè)字段同時(shí)進(jìn)行判斷，因此可以用 ( ) 將這兩個(gè)字段括起來(lái)同時(shí)判斷。

-- 查詢與 "韋一笑" 的入職日期 及 職位都相同的員工信息 ;-- 首先嘗試采用標(biāo)量子查詢，分別查詢出"韋一笑"的入職日期 及 職位，但是這樣就需要兩次子查詢，性能較低。
select * from tb_emp where entrydate = (select entrydate from tb_emp where name = '韋一笑') and job = (select job from tb_emp where name = '韋一笑') and name != '韋一笑';-- 采用行子查詢，查詢出的結(jié)果為一行，可以將需要比較的字段用 ( ) 括起來(lái)進(jìn)行比較
select * from tb_emp where (entrydate, job) = (select entrydate, job from tb_emp where name = '韋一笑') and name != '韋一笑';

2.4 表子查詢

表子查詢返回的結(jié)果為多行多列。
常用操作符為：in。

案例：查詢?nèi)肼毴掌谑?“2006-01-01” 之后的員工信息 , 及其部門(mén)名稱(chēng)
分析：有兩種思路。第一種，先建立員工表與部門(mén)表的外連接，通過(guò)邏輯外鍵消除多余的笛卡爾積，然后判斷入職時(shí)間。第二種，先通過(guò)select返回入職時(shí)間在 “2006-01-01” 之后的員工子表，然后建立外連接，通過(guò)邏輯外鍵消除多余的笛卡爾積。推薦使用外連接，保證員工信息完整。

--查詢?nèi)肼毴掌谑?"2006-01-01" 之后的員工信息 , 及其部門(mén)名稱(chēng)-- 先建立外連接，后判斷入職時(shí)間
select tb_emp.*, tb_dept.name as 部門(mén)名稱(chēng) from tb_emp left join tb_dept on tb_emp.dept_id = tb_dept.id where tb_emp.entrydate > '2006-01-01';-- 表子查詢。先查詢出入職時(shí)間在 "2006-01-01" 之后員工信息子表，然后建立外連接。
select e.*, d.name 部門(mén)名稱(chēng) from (select * from tb_emp where entrydate > '2006-01-01') e left join tb_dept d on e.dept_id = d.id;

3、多表查詢案例

準(zhǔn)備數(shù)據(jù)：執(zhí)行01.多表查詢數(shù)據(jù)準(zhǔn)備.sql腳本。
從案例-多表查詢.vsdx中可以看到四張表的詳細(xì)信息。

-- 1. 查詢價(jià)格低于 10元 的菜品的名稱(chēng) 、價(jià)格 及其 菜品的分類(lèi)名稱(chēng) .
-- 子查詢
select d.name 名稱(chēng), d.price 價(jià)格, c.name 分類(lèi) 
from (select * from dish where price < 10) d left join category c on d.category_id = c.id;
-- 內(nèi)連接
select d.name 菜品名稱(chēng), d.price 價(jià)格, c.name 菜品分類(lèi) 
from dish d, category c 
where d.category_id = c.id and d.price < 10;-- 2. 查詢所有價(jià)格在 10元(含)到50元(含)之間 且 狀態(tài)為'起售'的菜品名稱(chēng)、價(jià)格 及其 菜品的分類(lèi)名稱(chēng) (即使菜品沒(méi)有分類(lèi) , 也需要將菜品查詢出來(lái)).
-- 子查詢
select d.name 名稱(chēng), d.price 價(jià)格, c.name 分類(lèi) 
from (select * from dish where price between 10 and 50 and status = 1) d left join category c on d.category_id = c.id;
-- 左外連接
select d.name 菜品名稱(chēng), d.price 價(jià)格, c.name 菜品分類(lèi) 
from dish d left join category c on d.category_id = c.id 
where d.price between 10 and 50 and d.status = 1;-- 3. 查詢每個(gè)分類(lèi)下最貴的菜品, 展示出分類(lèi)的名稱(chēng)、最貴的菜品的價(jià)格 .
select c.name 分類(lèi)名稱(chēng), max(d.price) 最大價(jià)格 
from dish d, category c 
where d.category_id = c.id 
group by c.name;-- 4. 查詢各個(gè)分類(lèi)下 狀態(tài)為 '起售' , 并且 該分類(lèi)下菜品總數(shù)量大于等于3 的 分類(lèi)名稱(chēng) .
select c.name 分類(lèi)名稱(chēng) 
from dish d, category c 
where d.category_id = c.id and d.status = 1 
group by c.name 
having count(*) >= 3;-- 5. 查詢出 "商務(wù)套餐A" 中包含了哪些菜品 （展示出套餐名稱(chēng)、價(jià)格, 包含的菜品名稱(chēng)、價(jià)格、份數(shù)）.
select s.name 套餐名稱(chēng),s.price 套餐價(jià)格,d.name 菜品名稱(chēng),d.price 菜品價(jià)格,sd.copies 菜品份數(shù)
from dish d,setmeal_dish sd,setmeal s
where d.id = sd.dish_idand sd.setmeal_id = s.idand s.name = '商務(wù)套餐A';-- 6. 查詢出低于菜品平均價(jià)格的菜品信息 (展示出菜品名稱(chēng)、菜品價(jià)格).
select name, price 
from dish 
where price < (select avg(price) from dish);