外部連結(Outer Join) Left Join, Right Join,COUNT, Group By 範例

外部連結 – 從一個表格中取得所有的列,再加上其它表格符合的列,沒有符合的資料,那些欄位會返回NULL值。
Left Join – 由左表到右表
Right Join – 由右表到左表

建立兩張資料表,一張是球隊資訊,另一張是球員資料表,以下為欄位格式及資料

mysql> describe team;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| sn    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe bowler;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| sn      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255)     | NO   |     | NULL    |                |
| team_sn | int(11)          | YES  |     | 0       |                |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from team;
+----+-----------------------+
| sn | name                  |
+----+-----------------------+
|  1 | 阿米果保齡球隊 |
|  2 | 戰斧保齡球隊    |
+----+-----------------------+
2 rows in set (0.00 sec)

mysql> select * from bowler;
+----+---------+---------+
| sn | name    | team_sn |
+----+---------+---------+
|  1 | 陳x蓁 |       1 |
|  2 | 陳x融 |       1 |
|  3 | 許x奕 |       2 |
|  4 | 江x一 |       2 |
|  5 | 林x毅 |       1 |
|  6 | 王x冠 |       2 |
|  7 | 方x哲 |       1 |
|  8 | 蔡x成 |       2 |
|  9 | 黃x凱 |       0 |
| 10 | 楊x弘 |       0 |
+----+---------+---------+
10 rows in set (0.00 sec)

 

外部連結(Outer Join),使用 Left Join

以左邊的球員資料表為主結合右邊的球隊資料表,若沒有符合的資料,那些欄位會返回NULL值

mysql> select * from bowler as t1 left join team as t2 on t1.team_sn = t2.sn;
+----+---------+---------+------+-----------------------+
| sn | name    | team_sn | sn   | name                  |
+----+---------+---------+------+-----------------------+
|  1 | 陳x蓁 |       1 |    1 | 阿米果保齡球隊 |
|  2 | 陳x融 |       1 |    1 | 阿米果保齡球隊 |
|  3 | 許x奕 |       2 |    2 | 戰斧保齡球隊    |
|  4 | 江x一 |       2 |    2 | 戰斧保齡球隊    |
|  5 | 林x毅 |       1 |    1 | 阿米果保齡球隊 |
|  6 | 王x冠 |       2 |    2 | 戰斧保齡球隊    |
|  7 | 方x哲 |       1 |    1 | 阿米果保齡球隊 |
|  8 | 蔡x成 |       2 |    2 | 戰斧保齡球隊    |
|  9 | 黃x凱 |       0 | NULL | NULL                  |
| 10 | 楊x弘 |       0 | NULL | NULL                  |
+----+---------+---------+------+-----------------------+
10 rows in set (0.00 sec)

 

外部連結(Outer Join),使用 Right Join

以右邊的球隊資料表為主結合左邊的球員資料表,會排除沒有歸屬球隊的球員

mysql> select * from bowler as t1 right join team as t2 on t1.team_sn = t2.sn;
+------+---------+---------+----+-----------------------+
| sn   | name    | team_sn | sn | name                  |
+------+---------+---------+----+-----------------------+
|    1 | 陳x蓁 |       1 |  1 | 阿米果保齡球隊 |
|    2 | 陳x融 |       1 |  1 | 阿米果保齡球隊 |
|    5 | 林x毅 |       1 |  1 | 阿米果保齡球隊 |
|    7 | 方x哲 |       1 |  1 | 阿米果保齡球隊 |
|    3 | 許x奕 |       2 |  2 | 戰斧保齡球隊    |
|    4 | 江x一 |       2 |  2 | 戰斧保齡球隊    |
|    6 | 王x冠 |       2 |  2 | 戰斧保齡球隊    |
|    8 | 蔡x成 |       2 |  2 | 戰斧保齡球隊    |
+------+---------+---------+----+-----------------------+
8 rows in set (0.00 sec)

 

使用LEFT JOIN 或 RIGHT JOIN 加上 COUNT 和 GROUP BY 來區分各支球隊的人員

以左表為主結合右表,左表為球員資料表,以球員資料為主來計算每支球隊人數,未加入球隊的人也會出現

mysql> select t2.name,count(t1.sn) as total from bowler as t1 left join team as t2 on t1.team_sn = t2.sn group by t2.name;
+-----------------------+-------+
| name                  | total |
+-----------------------+-------+
| NULL                  |     2 |
| 戰斧保齡球隊    |     4 |
| 阿米果保齡球隊 |     4 |
+-----------------------+-------+
3 rows in set (0.00 sec)

以右表為主結合左表,右表為球隊資料表,以球隊資料為主來計算每支球隊的人數,不會出現未加入球隊的人

mysql> select t2.name,count(t1.sn) as total from bowler as t1 right join team as t2 on t1.team_sn = t2.sn group by t2.name;
+-----------------------+-------+
| name                  | total |
+-----------------------+-------+
| 戰斧保齡球隊    |     4 |
| 阿米果保齡球隊 |     4 |
+-----------------------+-------+
2 rows in set (0.00 sec)

這部份使用 SQL 來做會比把資料倒給 PHP,然後 PHP 再使用迴圈等方式計算來的快,所以應該直接向 MySQL 資料庫詢問取得結果。