前言
对于关系型数据库来说,对于复杂的查询表连接是必不可少的,下面妹子就来介绍下表连接的方式
内连接(Inner Join)
Inner
指的是 intersect
相交,顾名思义就是取两表的交集。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*inner-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a
INNER JOIN `b_borrow_deductions` AS b
ON a.per_id=b.per_id;/*51条*/
表a有63条数据,表b有10条数据,两表都有键值per_id,用INNER JOIN取交集为51条
外连接(Outer Join)
Outer
指的是 union
集合,就是取两表的并集。
其中外连接分为3种:
Full outer join
full outer join 得到两表的乘集,也可叫做笛卡尔乘积。如果a表中的行在b表中没有对应的部分,b的部分将是 null, 反之亦然。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*full-outer-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a
JOIN `b_borrow_deductions` AS b /*630条*/
取两表的乘集,则a表的行数乘以b表的行数,63*10=630(条)
Left join
左连接,取表a的所有行,和b表中匹配的值,没有匹配的则以null值取代。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*left-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a
LEFT JOIN `b_borrow_deductions` AS b
ON a.per_id=b.per_id;/*63条*/
a表有63行,b表中键值per_id在a表中都有。
Right join
右连接,取表b的所有行,和a表中匹配的值,没有匹配的则以null值取代。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*right-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a
RIGHT JOIN `b_borrow_deductions` AS b
ON a.per_id=b.per_id;/*51条*/
b表有10行,a表中有重复键值per_id,所以有51条
反连接(Anti Join)
Anti
指的是 英语词根反对
的意思,与inner join
正好相反。
多用于!= not in 等查询;如果找到满足条件(!= not in)的不返回,不满足条件(!= not in)的返回。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*anti-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a ,`b_borrow_deductions` AS b
WHERE a.per_id!=b.per_id;/*579条*/
相当于a表与b表的乘级减去a表与b表的交集,即(63*10)-51=579条
半连接(Semi Join)
半连接多在子查询exists中使用,找到存在的键值之后就不再查找内部其他的键值了。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*semi-join */
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a
WHERE EXISTS(
SELECT NULL FROM `b_borrow_deductions` AS b WHERE a.per_id=b.per_id)/*51条*/
星型连接(Star Join)
是指一张大的事实表和多个小表关联得出所需的数据,往四周发散,所以称为星型链接。
要使用星型转换,必须要求初始化参数star_transforation_enabled
设为TRUE。
SELECT ...
FROM really_big_fact f -- 50 million rows
, dim1 d1 -- 50 rows
, dim2 d2 -- 100 rows
, dim3 d3 -- 10 rows
, dim4 d4 -- 15 rows
WHERE f.key1 = d1.key1
AND f.key2 = d2.key2
AND f.key3 = d3.key3
AND f.key4 = d4.key4
现在只有Oracle
才有
表链接的方式
表链接的方式有:排序合并连接(Sort Merge join)、嵌套循环连接(Nested Logo join)、哈希连接(Mash join)、笛卡尔链接(Cross join)
参考资料
Happy coding .. :)
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。