Day.22 SQL應用 - 表與表之間的連結 (JOIN) - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

2021 iThome 鐵人賽

DAY 22

Software Development

MYSQL-相關實務操作學習紀錄系列第 22 篇

Day.22 SQL應用 - 表與表之間的連結 (JOIN)

13th鐵人賽

Siang

團隊神龍特攻隊 - it 宏的逆襲

2021-09-30 08:33:10

2137 瀏覽

分享至

今天來介紹關連式資料庫查詢方便的地方-JOIN

多表之間的連結(JOIN 用法)

用以下3個表的紀錄內容來解釋，使用不同JOIN方法在資料表之中的資料顯示差異～

mysql>  select * from act;
+------------+--------+------------+------------+----------------+------------+------------+
| actives_id | status | start_time | end_time   | platform_id    | updated_at | created_at |
+------------+--------+------------+------------+----------------+------------+------------+
|          1 |      1 | 1611504000 | 1632931200 | 10868213102191 | 1632499200 | 1632499200 |
+------------+--------+------------+------------+----------------+------------+------------+
1 row in set (0.00 sec)

mysql> select * from act_game;
+------------+-----------------+-------------------------------+------+------------+
| actives_id | game_name       | support_rules                 | type | created_at |
+------------+-----------------+-------------------------------+------+------------+
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] |    7 | 1632499200 |
|          2 | INVEST-ITHOME99 | ["z920"]                      |    5 | 1632499200 |
+------------+-----------------+-------------------------------+------+------------+
2 rows in set (0.00 sec)

mysql> select * from act_name;
+------------+--------------+----------+------------+
| actives_id | actives_name | language | created_at |
+------------+--------------+----------+------------+
|          1 | test2        | ch       | 1632499205 |
|          1 | test1        | en       | 1632499200 |
|          1 | test3        | jp       | 1632499209 |
|          3 | test01       | ch       | 1632499220 |
|          3 | test02       | en       | 1632499225 |
|          3 | test03       | jp       | 1632499230 |
+------------+--------------+----------+------------+
6 rows in set (0.00 sec)

INNER JOIN(等值連接) -> 查詢結果只會返回符合2邊資料表連接條件的資料

使用: 連結act_game&act_name取得遊戲內容包含使用語言資訊。


語法:
SELECT table_column1, table_column2...
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name;

mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language
FROM act_game
INNER JOIN act_name
ON act_game.actives_id=act_name.actives_id;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name       | support_rules                 | language |
+------------+-----------------+-------------------------------+----------+
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp       |
+------------+-----------------+-------------------------------+----------+
3 rows in set (0.00 sec)

LEFT JOIN -> 返回左側資料表中所有資料列(就算沒有符合連接的條件)，右側資料表中如果沒有能匹配的資料值會顯示為NULL。

使用: 連結act_game&act_name取得遊戲內容包含使用語言資訊。


語法:
SELECT column1, column2...
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name;

mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language 
FROM act_game 
LEFT JOIN act_name 
ON act_game.actives_id=act_name.actives_id;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name       | support_rules                 | language |
+------------+-----------------+-------------------------------+----------+
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp       |
|          2 | INVEST-ITHOME99 | ["z920"]                      | NULL     |
+------------+-----------------+-------------------------------+----------+
4 rows in set (0.00 sec)

RIGHT JOIN -> 返回右側資料表中所有資料列(就算沒有符合連接的條件)，左側資料表中如果沒有匹配的資料值會顯示為NULL。

使用: 連結act_game&act_name取得遊戲內容包含使用語言資訊。


語法:
SELECT column1, column2...
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name;

mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language 
FROM act_game 
RIGHT JOIN act_name 
ON act_game.actives_id=act_name.actives_id;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name       | support_rules                 | language |
+------------+-----------------+-------------------------------+----------+
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp       |
|       NULL | NULL            | NULL                          | ch       |
|       NULL | NULL            | NULL                          | en       |
|       NULL | NULL            | NULL                          | jp       |
+------------+-----------------+-------------------------------+----------+
6 rows in set (0.00 sec)

CROSS JOIN -> 不指定搜尋條件，返回兩個資料表中所有可能的排列組合。

使用: 連結act_game&act_name取得遊戲內容包含使用語言資訊。

範例中: act_game (2筆記錄) * act_name (6筆記錄) = 返回12筆結果。


語法:
SELECT table_column1, table_column2...
FROM table_name1
CROSS JOIN table_name2;


mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language 
FROM act_game 
CROSS JOIN act_name;

+------------+-----------------+-------------------------------+----------+
| actives_id | game_name       | support_rules                 | language |
+------------+-----------------+-------------------------------+----------+
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch       |
|          2 | INVEST-ITHOME99 | ["z920"]                      | ch       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en       |
|          2 | INVEST-ITHOME99 | ["z920"]                      | en       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp       |
|          2 | INVEST-ITHOME99 | ["z920"]                      | jp       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch       |
|          2 | INVEST-ITHOME99 | ["z920"]                      | ch       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en       |
|          2 | INVEST-ITHOME99 | ["z920"]                      | en       |
|          1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp       |
|          2 | INVEST-ITHOME99 | ["z920"]                      | jp       |
+------------+-----------------+-------------------------------+----------+
12 rows in set (0.00 sec)

UNION -> 將兩個(以上)SQL查詢結果合併起來，查詢的各別SQL語句使用欄位要是相同的資料型別及順序。

ps.MYSQL沒有FULL JOIN，可以用UNION來達到類似作用。

差異點:
- JOIN: 橫向結合 (合併多個資料表的各欄位)。
- UNION: 垂直結合 (合併多個資料表中的紀錄)。

使用: 查詢act_game&act_name的紀錄。

1. UNION 會顯示2表含幾個不同的actives_id。

2. UNION ALL 會列出所有的資料，不論資料是否重複。

語法:
SELECT column_name... FROM table_name1
UNION
SELECT column_name... FROM table_name2;

mysql> SELECT actives_id FROM act_game
UNION
SELECT actives_id FROM act_name;
+------------+
| actives_id |
+------------+
|          1 |
|          2 |
|          3 |
+------------+
3 rows in set (0.00 sec)

mysql> SELECT actives_id FROM act_game UNION ALL  SELECT actives_id FROM act_name;
+------------+
| actives_id |
+------------+
|          1 |
|          2 |
|          1 |
|          1 |
|          1 |
|          3 |
|          3 |
|          3 |
+------------+
8 rows in set (0.00 sec)

介紹完以上資料表連結的方式後，最後來個3表之間的連結查詢當作結尾

使用: 連結 act & act_game & act_name 取得活動ID=1的完整內容。



3張表的全部欄位資訊:
SELECT act.* , act_game.* , act_name.* 
FROM `act` 
left join act_game on act.actives_id = act_game.actives_id 
left join act_name on act.actives_id = act_name.actives_id 
WHERE (act.actives_id = 1);

過濾其他不需要的欄位後:
SELECT act.actives_id, act.status, act.start_time, act.end_time, act.platform_id,  act_game.game_name, act_game.support_rules, act_game.type, act_name.actives_name, act_name.language FROM `act` left join act_game on act.actives_id = act_game.actives_id  left join act_name on act.actives_id = act_name.actives_id WHERE (act.actives_id = 1);