转自 https://blog.csdn.net/u014682191/article/details/53009871
首先原数据库的数据有:
TEST_A表:和TEST_B表:
一、笛卡尔积:
(1)不带条件的笛卡尔积:
select * from TEST_A a,TEST_B b;
(2)带where条件id相等的笛卡尔积:
select * from TEST_A a,TEST_B b where a.ID = b.ID;
二、inner join:
select * from TEST_A a inner join TEST_B b on a.ID = b.ID;
三、left join:
select * from TEST_A a left join TEST_B b on a.ID = b.ID;
四、right join:
select * from TEST_A a right join TEST_B b on a.ID = b.ID;
五、full join:
select * from TEST_A a full join TEST_B b on a.ID = b.ID;
总结:
两表直接笛卡尔积的结果数量是两表的数据量相乘
带where条件id相等的笛卡尔积和inner join结果相同,但是inner join效率快一点
left join:TEST_A表的ID为空时拼接TEST_B表的内容为空,right join则相反
full join:等于left join和right join的并集