MySQL七种join详解
MySQL七种join详解
关系数据库的join理论
下面先对join连接的原理进行分析，只有在理解了join的原理才能更透彻的明白这其中join的过程，以及如何mysql如何高效的进行join操作。
Join算法原理
MySQL的join算法原理基于嵌套循环查询。但是由于简单的嵌套循环查询存在效率低下，频繁与磁盘进行I/O的问题，故MySQL会分别从减少内层循环次数，以及减少I/O次数两个层面对join的简单嵌套循环查询进行优化。
从本质上来说，MySQL的join算法基于简单嵌套循环查询，即外层表的记录作为条件，循环遍历内层表进行查询，返回内层循环中满足条件的记录。对于SQL语句: select * from c left join b on c.id=b.id。
for(i from 外层表){
for(j from 内层表){
if(i.id = j.id){
return;
}
}
}
但是简单的嵌套循环查询效率比较低，本质上是因为每次查询时都是从外层表取出一次记录，即进行一个I/O，并且对内层表的查询是全表扫描，故可以从这两个角度对简单嵌套循环查询进行优化。
内层循环的查询可以通过索引进行优化，通过索引进行查找的次数只为索引B树的高度。这就是索引嵌套循环连接。对于上述的SQL语句:select * from c left join b on c.id=b.id，如果在b表对id字段建立了索引，则外层的循环查询在内层循环中会使用到索引，从而减少了查询的次数。
另外一种优化的方法就是从优化I/O的角度，每次取外层表的多条记录进行缓存，多条记录同时在内层循环中进行查找。
七种Join的详解
为了更好的展示下面的例子，下面有两个测试表。
左连接
左连接将左表作为主表，右表作为从表，左表作为外层循环，在右表中进行匹配，如果左表的记录在右表中没有匹配，则将该左表记录的右表项补空值。
该图示用MySQL实现为: select * from class c left join book b on c.id=b.cid;
右连接
右连接将右表作为主表，左表作为从表，右表作为外层循环，在左表中进行匹配，如果右表的记录在左表中没有匹配，则将该右表记录的左表项补空值。
该图示用MySQL实现为:select * from class c right join book b on b.cid=b.id;
内连接
内连接将左表和右表对于条件相匹配的项进行组合，在结果中只会出现同时在左表和右表出现的项。
该图示用MySQL实现为:select * from class c inner join book b on c.id=b.cid;
左外连接
左外连接选择将左表作为主表，右表作为从表，循环遍历右表，查找与条件满足的项，如果在右表中没有匹配的项，则补空值，并且在结果集中选择只在左表中存在的数据。
该图示用MySQL实现为:select * from class c left join book b on c.id=b.cid where b.cid is null;
右外连接
右外连接选择将右表作为主表，左表作为从表，循环遍历左表，查找与join条件满足的项，如果在左表中没有匹配的项，则补空值，并且在结果集中选择只在右表中存在的数据。
该图示用MySQL实现为:select * from class c right join book b on b.cid=c.id where c.id is null;
全连接
全外连接将左表和右表的所有记录进行匹配，如果在另外表项中不存在记录，则补空值。
该图示用MySQL实现为:select * from class c left join book b on c.id=b.cid
union
select * from class c right join book b on b.cid=c.id;
全外连接
全外连接将全连接中左右表相交的部分排除。
该图示用MySQL实现为:select * from class c left join book b on c.id=b.cid where b.cid is null
union
select * from class c right join book b on b.cid=c.id where c.id is null;
