MySQL之表聯結

1. 簡單聯結
①內部聯結
內部聯結又稱為等值聯結。ANSI SQL規範首選INNER JOIN語法。
內部聯結分為隱式內聯結和顯式內聯結，二者查詢效果相同，僅僅是語法不一樣而已。

隱式內聯結：WHERE子句
FROM tb1_name, tb2_name, tb3_name
WHERE condition_1 AND condition_2
顯式內聯結：INNER JOIN語法
FROM tb1_name
INNER JOIN tb2_name ON condition_1
INNER JOIN tb3_name ON condition_2

應用場景：統計參加了考試的學生及其各科成績
# 隱式內聯結
mysql> SELECT a.stu_id, stu_name, a.lesson_id, lesson_name, score
-> FROM t_score a, t_stu_profile b, t_lesson c
-> WHERE a.stu_id = b.stu_id AND a.lesson_id = c.lesson_id
-> ORDER BY stu_id, lesson_id
-> ;
+--------+----------+-----------+-------------+-------+
| stu_id | stu_name | lesson_id | lesson_name | score |
+--------+----------+-----------+-------------+-------+
| 1 | 郭東 | L001 | 語文 | 90 |
| 1 | 郭東 | L002 | 資料 | 86 |
| 2 | 李西 | L001 | 語文 | 84 |
| 2 | 李西 | L002 | 資料 | 90 |
| 2 | 李西 | L003 | 英語 | 86 |
| 2 | 李西 | L004 | 物理 | 75 |
| 2 | 李西 | L005 | 化學 | 77 |
| 3 | 張北 | L001 | 語文 | 100 |
| 3 | 張北 | L002 | 資料 | 91 |
| 3 | 張北 | L003 | 英語 | 85 |
| 4 | 錢南 | L001 | 語文 | 99 |
| 4 | 錢南 | L002 | 資料 | 88 |
| 4 | 錢南 | L003 | 英語 | 66 |
| 4 | 錢南 | L005 | 化學 | 98 |
+--------+----------+-----------+-------------+-------+

# 顯式內聯結
mysql> SELECT a.stu_id, stu_name, a.lesson_id, lesson_name, score
-> FROM t_score a
-> INNER JOIN t_stu_profile b
-> INNER JOIN t_lesson c
-> ON a.stu_id = b.stu_id AND a.lesson_id = c.lesson_id
-> ORDER BY stu_id, lesson_id
-> ;

2. 高階聯結
①自聯結
自聯結通常作為外部語句用來替代從相同表中檢索資料時使用的子查詢語句。雖然最終的結果是相同的，但有時候處理聯結遠比處理子查詢快得多。

應用場景：發現成績表上有個100分的成績存在問題，需查詢100分的這門課程的其它同學的成績是否存在問題
子查詢：先找到100分的這門課程的名稱（lesson_id），然後找出這門課程的其他同學的成績。

mysql> SELECT stu_id, score
-> FROM t_score
-> WHERE lesson_id = (
-> SELECT lesson_id
-> FROM t_score
-> WHERE score = 100)
-> ;
+--------+-------+
| stu_id | score |
+--------+-------+
| 1 | 98 |
| 2 | 84 |
| 3 | 100 |
| 4 | 99 |
+--------+-------+

自聯結：此查詢中需要的兩個表實際上是相同的表，因此t_score表在FROM 子句中出現了兩次。雖然這是合法的，但對t_score表的引用具有二義性，因為MySQL不知道引用的是t_score表的哪個例項。

mysql> SELECT a.stu_id, a.score
-> FROM t_score a, t_score b
-> WHERE a.lesson_id = b.lesson_id AND b.score = 100
-> ;
+--------+-------+
| stu_id | score |
+--------+-------+
| 1 | 98 |
| 2 | 84 |
| 3 | 100 |
| 4 | 99 |
+--------+-------+

②自然聯結
無論何時對錶進行聯結，應該至少有一個列出現在不止一個表中（被聯結的列）。標準的聯結返回所有資料，甚至相同的列多次出現。
自然聯結排除多次出現，使每個列只返回一次。

萬用字元*只對表t_stu_profile使用，所有其它列明確列出，所以沒有重複的列被檢索出來。

mysql> SELECT b.*, a.lesson_id, c.lesson_name, a.score
-> FROM t_score a, t_stu_profile b, t_lesson c
-> WHERE a.stu_id = b.stu_id AND a.lesson_id = c.lesson_id
-> ;

事實上，迄今為止建立的每個內部聯結都是自然聯結，很可能永遠都不會用到不是自然聯結的內部聯結。

③外部聯結
許多聯結將一個表中的行與另一個表中的行相關聯，但有時候會需要包含沒有關聯行的那些行。例如，需要對每個客戶下了多少訂單進行計數，包括那些至今尚未下訂單的客戶。
那麼，聯結包含了那些在相關表中沒有關聯行的行，這種型別的聯結稱為外部聯結。

應用場景：統計每位同學的成績，包括沒參加考試的同學
外部聯結：外部聯結分為左外部聯結（LEFT OUTER JOIN）和右外部聯結（RIGHT OUTER JOIN）

mysql> SELECT a.stu_id, stu_name, lesson_id, score
-> FROM t_score a
-> RIGHT OUTER JOIN t_stu_profile b
-> ON a.stu_id = b.stu_id
-> ;
+--------+----------+-----------+-------+
| stu_id | stu_name | lesson_id | score |
+--------+----------+-----------+-------+
| 1 | 郭東 | L001 | 98 |
| 1 | 郭東 | L002 | 86 |
| 1 | 郭東 | L003 | 79 |
| 1 | 郭東 | L004 | 88 |
| 1 | 郭東 | L005 | 98 |
| 2 | 李西 | L001 | 84 |
| 2 | 李西 | L002 | 90 |
| 2 | 李西 | L003 | 86 |
| 2 | 李西 | L004 | 75 |
| 2 | 李西 | L005 | 77 |
| 3 | 張北 | L001 | 100 |
| 3 | 張北 | L002 | 91 |
| 3 | 張北 | L003 | 85 |
| 3 | 張北 | L004 | 79 |
| 3 | 張北 | L005 | 85 |
| 4 | 錢南 | L001 | 99 |
| 4 | 錢南 | L002 | 88 |
| 4 | 錢南 | L003 | 66 |
| 4 | 錢南 | L004 | 66 |
| 4 | 錢南 | L005 | 98 |
| NULL | 王五 | NULL | NULL |
| NULL | 趙七 | NULL | NULL |
+--------+----------+-----------+-------+
---------------------
作者：lulin916
來源：CSDN
原文：https://blog.csdn.net/weixin_39010770/article/details/87886673
版權宣告：本文為博主原創文章，轉載請附上博文連結！

MySQL之表聯結

相關文章