1.inner、left、right join的区别?
这里暂时不讨论这几种join的原理,与效率上的区别。
只考虑同样的查询语句,会不会导致不同的查询结果。
这3种关联的区别是,当两个表有不关联(即存在不符合on条件的条目)的条目存在时,得出的结果才会有差别。
比如a、b两个表,关联关系为a.b_id = b.id
如果a中的每个a.b_id在b中都有对应的,并且,b中的b.id在a中都有对应的。
那么从查询结果上来说,三个join是没有区别的。
2.决定一个sql语句有多少条条目的是什么?
先不考虑where,只说on
有多少个条目可以符合on这个条目,就会列出多少行。
具体的说就是在on xxx = yyy,
最后有的条目就是max{xxx, yyy}
这是在没有不相关联的记录的情况下,在有不相关联的情况下,left、right join在此基础上还要列出不相关联的条目。
3.优化选择
关于这个inner join 和 left join的效率问题,我目前的认识是这样的,inner join 效率上来说,因为需要匹配的数量更少,所以效率上肯定要比left join高。
那为什么要使用left join呢?那是因为业务逻辑的原因,有些条目,就算没有匹配到也应该显示出来,所以应该使用left join。
当然如果,本身left 和 inner在底层语句上就有很大的原理不同,那这个效率就另说了。
基于我现在的认识,给出如下的优化建议。
(1)尽量用小表去left join大表
(2)如果有条件判断,可以用子查询语句查询了,得到一张临时小表,然后再去join。
(3)inner join和where是可以等价转换的,而left join在某些情况下应该转换为inner join
举个栗子A表有10万个不同条目,而B表有10个不同条目,A中有10个条目是和B中一一对应的。
A表 属性有id, bid ,name
B表 属性有id, x-name
恰好的是B表中的x-name有5条是“xx”,有5条是“yy”
那么这么一个语句
select name from A left join B on bid = B.id where x-name = "xx" ------①
这条语句如果使用inner join
select name from A inner join B on bid = B.id where x-name = "xx" ------②
语句①会从10W个语句匹配where条件然后选出where x-name = "xx"
语句②会从10个语句中匹配where条件然后选出where x-name = “xx”
这样效率明显有差异。
(4)将left转换为inner join
那么问题来了,到底什么时候应该将left 用inner代替呢?
(1)业务逻辑本身就只需要匹配的行
(2)业务逻辑本身后面的where有约束,约束来其实和inner没区别。
其实left和inner的区别不外乎就是显不显示不关联的条目,如果通过where的约束下,最后本来就不会有不关联的条目的话,那之前就用inner join就会提升性能。
那么问题又来了,怎么才能知道where的约束会不会导致最后就是没有不匹配的条目呢?
假如有表A,表B。
A left join B on A.a = B.b where xxx
①如果xxx条件是以B的某个字段为条件的
比如B.x > yyy这样的