[[377885]]

1. 前言

表查詢，也稱為多表連接查詢;作為關系型數據庫最主要的查詢方式，在日常工作中被廣泛使用

常見的多表查詢操作包含：子查詢、內連接、左外連接、右外連接、完全連接、交叉連接

本篇文章將利用一個實例逐一介紹這些操作

2. 準備

以 Mysql 數據庫為例，創建兩張數據表：

• student - 學生表
• record - 選課記錄表

其中，學生表 id 字段對應選課記錄表中的 student_id 字段

Sql 如下：

# 學生表：student 
create table student 
( 
    id   int          not null 
        primary key, 
    name varchar(255) null, 
    age  int          null 
) 
    comment '學生表'; 
 
# 選課記錄表：record 
create table record 
( 
    id         int          not null 
        primary key, 
    name       varchar(255) not null, 
    student_id int          not null, 
    time       datetime     null 
) 
    comment '選課記錄'; 

然后，向 2 張表中插入一些數據

# 學生表數據 
1,張三,18 
2,李四,23 
3,王五,30 
4,馬六,35 
5,孫七,40 
6,朱八,19 
7,黃九,53 
 
# 記錄表數據 
2021001,語文,1,2021-01-18 15:32:47 
2021002,數學,2,2021-01-18 15:33:41 
2021003,英語,3,2021-01-18 15:34:01 
2021004,物理,4,2021-01-18 15:34:33 
2021005,體育,5,2021-01-18 15:34:47 
2021006,化學,8,2021-01-18 15:35:12 
2021007,生物,9,2021-01-18 15:35:39 
2021008,音樂,10,2021-01-18 15:36:00 

3.1 子查詢

子查詢，又稱之為內查詢，是一種嵌套在其他 Sql 查詢的 Where 子句中的查詢

一般用于對查詢結果的進一步限制，返回所需要的數據;子查詢可以用在 SELECT、INSERT、UPDATE 和 DELETE 語句中

這里以 SELECT 語句為例，在兩張表中使用子查詢，篩選出滿足條件的記錄

# 子查詢 
select * from student where id in (select student_id from record where student_id<=3) 

查詢結果如下：

# 子查詢的結果 
1,張三,18 
2,李四,23 
3,王五,30) 

需要注意的是，子查詢必須包含在圓括號內，并且不能使用 ORDER BY 進行排序

3.2 內連接

內連接是通過關鍵字 inner join 連接兩張表，只返回滿足 on 條件的，兩張表的交集數據

# 內連接 
select * from student s inner join record r on s.id=r.student_id; 

查詢結果如下：

# 內連接查詢結果 
1,張三,18,2021001,語文,1,2021-01-18 15:32:47 
2,李四,23,2021002,數學,2,2021-01-18 15:33:41 
3,王五,30,2021003,英語,3,2021-01-18 15:34:01 
4,馬六,35,2021004,物理,4,2021-01-18 15:34:33 
5,孫七,40,2021005,體育,5,2021-01-18 15:34:47 

需要注意的是，如果內連接沒有通過 on 關鍵字指定條件的話，查詢結果和交叉連接查詢結果一樣，只是執行效率高于交叉連接

3.3 外連接

外連接包含：

• 左外連接
• 右外連接

其中，

左外連接：使用關鍵字 left join，以左表為準，返回左表的所有數據，右表滿足 on 條件的數據會全部顯示，否則用 null 值去填充

右外連接：和左外連接相反。使用關鍵 right join，以右表為準，返回右表的所有數據，左表滿足 on 條件的數據會全部顯示，否則用 null 值去填充

首先，我們來看左連接的實例

# 左外連接 
select * from student s left join record r on s.id=r.student_id; 

返回結果如下：

# 左外連接結果 
1,張三,18,2021001,語文,1,2021-01-18 15:32:47 
2,李四,23,2021002,數學,2,2021-01-18 15:33:41 
3,王五,30,2021003,英語,3,2021-01-18 15:34:01 
4,馬六,35,2021004,物理,4,2021-01-18 15:34:33 
5,孫七,40,2021005,體育,5,2021-01-18 15:34:47 
6,朱八,19,NULL,NULL,NULL,NULL 
7,黃九,53,NULL,NULL,NULL,NULL 

然后，我們再來看看右連接

# 右外連接 
select * from student s right join record r on s.id=r.student_id; 

返回結果如下：

# 右外連接結果 
1,張三,18,2021001,語文,1,2021-01-18 15:32:47 
2,李四,23,2021002,數學,2,2021-01-18 15:33:41 
3,王五,30,2021003,英語,3,2021-01-18 15:34:01 
4,馬六,35,2021004,物理,4,2021-01-18 15:34:33 
5,孫七,40,2021005,體育,5,2021-01-18 15:34:47 
NULL,NULL,NULL,2021006,化學,8,2021-01-18 15:35:12 
NULL,NULL,NULL,2021007,生物,9,2021-01-18 15:35:39 
NULL,NULL,NULL,2021008,音樂,10,2021-01-18 15:36:00 

3.4 完全連接

完全連接，是通過關鍵字 full join 連接兩張表，返回左表和右表的所有數據，并使用 null 值填充缺失的數據

# 完全連接 
select * from student s full join record r on s.id = r.student_id; 

需要注意的是，Mysql 并不支持完全連接，我們可以使用左連接 + union + 右連接的方式去模擬完全連接

select * from student left join record on student.id = record.student_id 
union 
select * from student right join record on student.id = record.student_id; 

查詢結果如下：

# 完全連接結果 
1,張三,18,2021001,語文,1,2021-01-18 15:32:47 
2,李四,23,2021002,數學,2,2021-01-18 15:33:41 
3,王五,30,2021003,英語,3,2021-01-18 15:34:01 
4,馬六,35,2021004,物理,4,2021-01-18 15:34:33 
5,孫七,40,2021005,體育,5,2021-01-18 15:34:47 
6,朱八,19,NULL,NULL,NULL,NULL 
7,黃九,53,NULL,NULL,NULL,NULL 
NULL,NULL,NULL,2021006,化學,8,2021-01-18 15:35:12 
NULL,NULL,NULL,2021007,生物,9,2021-01-18 15:35:39 
NULL,NULL,NULL,2021008,音樂,10,2021-01-18 15:36:00 

3.5 交叉連接

交叉連接，又稱之為笛卡爾積，使用關鍵字 cross join 連接兩張表進行查詢

如果不使用 where 加入限制條件，則返回兩張表行數的乘積;如果加入限制條件，則返回滿足條件表達式的數據并合成一行

以加入限制條件的交叉連接查詢為例

# 交叉連接 
select * from xag.student as s cross join xag.record as r where s.id=r.student_id; 

查詢結果如下：

# 交叉連接結果 
1,張三,18,2021001,語文,1,2021-01-18 15:32:47 
2,李四,23,2021002,數學,2,2021-01-18 15:33:41 
3,王五,30,2021003,英語,3,2021-01-18 15:34:01 
4,馬六,35,2021004,物理,4,2021-01-18 15:34:33 
5,孫七,40,2021005,體育,5,2021-01-18 15:34:47 

需要注意的是，交叉連接查詢如果帶有限制條件，它會先生成兩張表行數成績生成查詢結果集，然后再通過限制條件去過濾;因此，在數據量大的時候，查詢速度會很慢

4. 最后

相比單表查詢，多表查詢可以覆蓋更多業務場景，大大提升我們的工作效率!實際工作當中，可以根據需要選擇性的去使用!