BLOG ������

MYSQL JOIN ON

Join on 內的篩選條件會影響Join內容，但不會影響Join總筆數。

Join 後的 where 條件則會影響Join總筆數，但Join內容不受影響。

例如：
table_a
id num
1 3
2 5
3 1

table_b
id num
1 9
2 8
4 10

select a.* , b.num
from table_a a left join table_b b
on a.id = b.id

id num num
1 3 9
2 5 8
3 1 null

select a.* , b.num
from table_a a left join table_b b
on a.id = b.id and a.num > 4

id num num
1 3 null
2 5 8
3 1 null
：原本可以對應出table_b id=1 ,num = 9的資料，但是因為在on子句內多加了 a.num > 4，導致table_a id = 1 , num = 3的資料不會去對應table_b id=1 , num = 9的資料。

select a.* , b.num
from table_a a left join table_b b
on a.id = b.id
where a.num > 4

id num num
1 3 9
2 5 8
3 1 null

id num num
2 5 8

Relate Post : 初學MySQL必須知道的事 How To Measure MySQL Query Performance with mysqlslap MySQL雙主高可用架構之MMM實戰 MySQL block host connection Fix MySQL connection error – Host ” is blocked because of many connection errors MYSQL master master replication error (Error_code: 1236) MySQL max_connections Website with HA and Clustering MySQL Order By Rand()效率 Exporting and importing data between MySQL and Microsoft excel
Comments :
No Comments

Post your comment:

Post your comment by Guest :

Verify Code :