分分鐘 搞懂 各種類型的關聯

在測試工作中，有時需要測試資料庫數據經過sql計算後的結果是否滿足某一功能查詢得到的返回值。

針對某些需要功能需要聯查多張表，此時 關聯 的作用就異常重要了，而針對多表關聯，其中 關聯條件的重要性不言而喻，

不同的關聯條件會得到不同的結果集。

　　廢話不多說，下麵開始做個實驗。

建表 data_stock1, data_stock2

drop table if exists data_stock1;
drop table if exists data_stock2;

-- 區分二表，通過amount取不同欄位

create table data_stock1(
account varchar(20),
amount1 int(10),
init_date varchar(10)
);

create table data_stock1(
account varchar(20),
amount2 int(10),
init_date varchar(10)
);

insert into data_stock1(account,amount1,init_date) values('2001',200,'20170101');
insert into data_stock1 (account,amount1,init_date) values('2001',30,'20170102');
insert into data_stock1 (account,amount1,init_date) values('2002',210,'20170102');
insert into data_stock1 (account,amount1,init_date) values('2003',70,'20170102');
insert into data_stock1(account,amount1,init_date) values('2002',10,'20170101');

表data_stock1，select * from data_stock1;

表data_stock2,自行插入值吧，數據如下，select * from data_stock2;

---------------------------------------------------我是分割線-------------------------------------

一切準備就緒；下麵實驗開始；將兩個表進行關聯，關聯 分為 外聯，內聯。

內聯 inner join ,內聯 的結果集 是data_stock2，data_stock1表中共同存在的結果；data  in (data_stock2,data_stock1)

外聯 outer join ,分為全聯（full outer join），左聯(left join)，右聯(right join)，區別如下：

-- --左關聯, a.account=b.account

select * 

from data_stock1 a left join data_stock2 b on (a.account=b.account);

-- --左關聯,a.account=b.account and a.init_date=b.init_date
select *
from data_stock1 a left join data_stock2 b on (a.account=b.account and a.init_date=b.init_date);

-- --左關聯,a.account=b.account,再做聚合 求平均值

select a.account,(AVG(a.amount1)+AVG(b.amount2)) as dispersion
from data_stock1 a left join data_stock2 b on (a.account=b.account )
group by a.account;

-- --左關聯,a.account=b.account,再做聚合 求平均值

select a.account,(AVG(a.amount1)+AVG(b.amount2)) as dispersion
from data_stock1 a left join data_stock2 b on (a.account=b.account and a.init_date=b.init_date)
group by a.account;

-- --右關聯, a.account=b.account
select *
from data_stock1 a RIGHT JOIN data_stock2 b on (a.account=b.account);

-- --右關聯,a.account=b.account and a.init_date=b.init_date
select *
from data_stock1 a RIGHT JOIN data_stock2 b on (a.account=b.account and a.init_date=b.init_date);

-- --內聯, a.account=b.account

--寫法1 ，select *
from data_stock1 a INNER JOIN data_stock2 b on a.account=b.account;

--寫法2， select *
from data_stock1 a , data_stock2 b where a.account=b.account;

-- --內聯,a.account=b.account and a.init_date=b.init_date
select *
from data_stock1 a INNER JOIN data_stock2 b on (a.account=b.account and a.init_date=b.init_date);

select *
from data_stock1 a ,data_stock2 b where (a.account=b.account and a.init_date=b.init_date);

-- --右關聯,a.account=b.account,再做聚合 求平均值

select a.account,(AVG(a.amount1)+AVG(b.amount2)) as dispersion
from data_stock1 a RIGHT JOIN data_stock2 b on (a.account=b.account )
group by a.account;

-- --右關聯,a.account=b.account,再做聚合 求平均值,再做聚合 求平均值

select a.account,(AVG(a.amount1)+AVG(b.amount2)) as dispersion
from data_stock1 a RIGHT JOIN data_stock2 b on (a.account=b.account and a.init_date=b.init_date)
group by a.account;   

註意，此時出現了 NULL賬號，是因為右聯的時候，結果集為NULL,而以 group by a.account做聚合，會有NULL