0%

MySQL_Join

sample tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
table1: customers
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(100) | YES | | NULL | |
| last_name | varchar(100) | YES | | NULL | |
| email | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+

table2: orders
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| order_date | date | YES | | NULL | |
| amount | decimal(8,2) | YES | | NULL | |
| customer_id | int(11) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+

如何關聯兩個table

1
2
3
4
5
6
7
8
9
10
可透過設定foreign key去關聯其他table
$ alter table orders add foreign key (customer_id) references customers(id);
****+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| order_date | date | YES | | NULL | |
| amount | decimal(8,2) | YES | | NULL | |
| customer_id | int(11) | YES | MUL | NULL | |
+-------------+--------------+------+-----+---------+----------------+

交集

圖片來源:https://www.akademiaarchitektait.pl/sql-joins/

Inner Join(顯示A/B兩個table交集的資料)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ select * from customers inner join orders where customers.id=orders.customer_id;
+----+------------+-----------+----------------+----+------------+--------+-------------+
| id | first_name | last_name | email | id | order_date | amount | customer_id |
+----+------------+-----------+----------------+----+------------+--------+-------------+
| 1 | Robin | Jackman | roj@gmail.com | 1 | 2001-10-12 | 99.12 | 1 |
| 2 | Taylor | Edward | taed@gmail.com | 2 | 2001-09-21 | 110.99 | 2 |
| 1 | Robin | Jackman | roj@gmail.com | 3 | 2001-10-13 | 12.19 | 1 |
| 3 | Vivian | Dickens | vidi@gmail.com | 4 | 2001-11-29 | 88.09 | 3 |
| 4 | Harley | Gilbert | hgi@gmail.com | 5 | 2001-11-11 | 205.01 | 4 |
+----+------------+-----------+----------------+----+------------+--------+-------------+

可以用group by再對join之後的table做整理運算
$ select first_name, last_name, SUM(amount) as total_amount from customers inner
join orders where customers.id=orders.customer_id group by customers.id;
+------------+-----------+--------------+
| first_name | last_name | total_amount |
+------------+-----------+--------------+
| Robin | Jackman | 111.31 |
| Taylor | Edward | 110.99 |
| Vivian | Dickens | 88.09 |
| Harley | Gilbert | 205.01 |
+------------+-----------+--------------+

Left Join(完整顯示A table的資料)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
- 先插入一個沒有order的人customer資料
$ insert into customers(first_name, last_name) values ("Phil", "Lee");

- 再做left join,即使沒有剛剛加進去的customer沒有order,也會被完整顯示出來。
$ select * from customers left join orders on customers.id=orders.customer_id;
+----+------------+-----------+----------------+------+------------+--------+-------------+
| id | first_name | last_name | email | id | order_date | amount | customer_id |
+----+------------+-----------+----------------+------+------------+--------+-------------+
| 1 | Robin | Jackman | roj@gmail.com | 1 | 2001-10-12 | 99.12 | 1 |
| 2 | Taylor | Edward | taed@gmail.com | 2 | 2001-09-21 | 110.99 | 2 |
| 1 | Robin | Jackman | roj@gmail.com | 3 | 2001-10-13 | 12.19 | 1 |
| 3 | Vivian | Dickens | vidi@gmail.com | 4 | 2001-11-29 | 88.09 | 3 |
| 4 | Harley | Gilbert | hgi@gmail.com | 5 | 2001-11-11 | 205.01 | 4 |
| 5 | Phil | Lee | NULL | NULL | NULL | NULL | NULL |
+----+------------+-----------+----------------+------+------------+--------+-------------+

- 用group by做整理
select CONCAT(first_name, " ", last_name) as full_name, SUM(amount) as total_amount
from customers
left join orders
on customers.id=orders.customer_id
group by customers.id;
+----------------+--------------+
| full_name | total_amount |
+----------------+--------------+
| Robin Jackman | 111.31 |
| Taylor Edward | 110.99 |
| Vivian Dickens | 88.09 |
| Harley Gilbert | 205.01 |
| Phil Lee | NULL |
+----------------+--------------+

- 如果是null,可以用case設條件,顯示為0
select first_name, last_name,
case
when SUM(amount) is Null then 0
else SUM(amount)
end as total_amount
from customers
left join orders
on customers.id=orders.customer_id
group by customers.id
order by total_amount desc;
+------------+-----------+--------------+
| first_name | last_name | total_amount |
+------------+-----------+--------------+
| Harley | Gilbert | 205.01 |
| Robin | Jackman | 111.31 |
| Taylor | Edward | 110.99 |
| Vivian | Dickens | 88.09 |
| Phil | Lee | 0 |
+------------+-----------+--------------+

- 如果要再更精簡一點,可以使用IFNULL
select first_name, last_name,
ifnull(SUM(amount), 0) total_amount
from customers
left join orders
on customers.id=orders.customer_id
group by customers.id
order by total_amount desc;
+------------+-----------+--------------+
| first_name | last_name | total_amount |
+------------+-----------+--------------+
| Harley | Gilbert | 205.01 |
| Robin | Jackman | 111.31 |
| Taylor | Edward | 110.99 |
| Vivian | Dickens | 88.09 |
| Phil | Lee | 0.00 |
+------------+-----------+--------------+

Right Join(完整顯示B table的資料)

基本上概念跟left join一樣,因為我們在orders這個table裡面設置了FK,所以每一筆order都會有對應的customer,如果我們使用right join,查到的結果會跟inner join一樣。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select first_name, last_name,
ifnull(SUM(amount), 0) total_amount
from customers
right join orders
on customers.id=orders.customer_id
group by customers.id
order by total_amount desc;
+------------+-----------+--------------+
| first_name | last_name | total_amount |
+------------+-----------+--------------+
| Harley | Gilbert | 205.01 |
| Robin | Jackman | 111.31 |
| Taylor | Edward | 110.99 |
| Vivian | Dickens | 88.09 |
+------------+-----------+--------------+