SQL的join大家族
本文記錄Inner Join,Left Join, Right Join , Full Join有什么區別
先創建測驗資料庫與表
CREATE DATABASE family_join;
USE family_join;
CREATE TABLE people (
id INT(2) AUTO_INCREMENT,
NAME VARCHAR(10),
age INT(3),
PRIMARY KEY (id)
)
CREATE TABLE people_profession(
p_id INT(2) AUTO_INCREMENT,
profession VARCHAR(10),
id INT(2),
PRIMARY KEY (p_id)
)
//來點資料
INSERT INTO people VALUES(NULL , '小芳' , 18),(NULL,'小源',25) ,(NULL , '小靜' ,25);
INSERT INTO people_profession VALUES(NULL, '寫代碼的' , 1) , (NULL , '清理下水道的' , 2) ,(NULL , '理發的' ,4);
Inner Join
啊，那我就簡單說兩句，這個INNER JOIN 和JOIN是一樣的
SELECT ,NAME , profession FROM people INNER JOIN people_profession ON people.`id` = people_profession.`id`
SELECT NAME , profession FROM people JOIN people_profession ON people.`id` = people_profession.`id`
//兩個得出一樣的結果
+------+--------------+
| NAME | profession |
+------+--------------+
| 小芳 | 寫代碼的 |
| 小源 | 清理下水道的 |
+------+--------------+
Left Join
SELECT NAME , profession FROM people LEFT JOIN people_profession ON people.`id` = people_profession.`id`
//Left Join會完整的將左表的內容獲取，無論右表和他有沒有匹配的'id'（people_profession表的id沒有匹配小靜的id）
+------+--------------+
| NAME | profession |
+------+--------------+
| 小芳 | 寫代碼的 |
| 小源 | 清理下水道的 |
| 小靜 | NULL |
+------+--------------+
Right Join
SELECT NAME , profession FROM people RIGHT JOIN people_profession ON people.`id` = people_profession.`id`
//Right Join與上面的同理只不過是右表全顯示
+------+--------------+
| NAME | profession |
+------+--------------+
| 小芳 | 寫代碼的 |
| 小源 | 清理下水道的 |
| NULL | 理發的 |
+------+--------------+
Full Join
SELECT people.name , people_profession.profession FROM people LEFT JOIN people_profession ON people.`id` = people_profession.`id`
UNION
SELECT people.name , people_profession.profession FROM people RIGHT JOIN people_profession ON people.`id` = people_profession.`id`
因為本人用的是mysql不支持Full Join 暫用這樣的句式代替 （Oracle 支持Full Join）效果相同
+------+--------------+
| name | profession |
+------+--------------+
| 小芳 | 寫代碼的 |
| 小源 | 清理下水道的 |
| 小靜 | NULL |
| NULL | 理發的 |
+------+--------------+
小總結
- JOIN: 如果表中有至少一個匹配，則回傳行
- LEFT JOIN: 即使右表中沒有匹配，也從左表回傳所有的行
- RIGHT JOIN: 即使左表中沒有匹配，也從右表回傳所有的行
- FULL JOIN: 只要其中一個表中存在匹配，就回傳行
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/329277.html
標籤：區塊鏈