实例分析join、left join、right join、fulljoin间的区别

分析INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN的ON、WHERE后面跟条件的区别

准备表结构及数据
新建表TAB1和TAB2，TAB1字段ID，SIZ；TAB2字段SIZ，NAME

SELECT * FROM TAB1 LEFT JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ

SELECT * FROM TAB1 RIGHT JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ

SELECT * FROM TAB1 INNER JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ

SELECT * FROM TAB1 FULL JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ

JOIN连接会生成一张临时表

左连接
SELECT * FROM TAB1 LEFT JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ AND NAME='AAAAAA';

结论：条件在ON之后，左边全显示、右边只显示条件匹配的记录
SELECT * FROM TAB1 LEFT JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ WHERE NAME='AAAAAA';

结论：条件在WHERE之后，过滤临时表中符合条件的记录

右连接
SELECT * FROM TAB1 RIGHT JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ AND NAME='AAAAAA';

结论：右边全显示，左边只显示与NAME匹配到的记录
SELECT * FROM TAB1 RIGHT JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ WHERE NAME='AAAAAA';

结论：条件在WHERE之后，过滤临时表中符合条件的记录

内连接
SELECT * FROM TAB1 INNER JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ AND NAME='AAAAAA';
SELECT * FROM TAB1 INNER JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ WHERE NAME='AAAAAA';

结论：条件在ON和WHERE之后结果都一致

全连接
SELECT * FROM TAB1 FULL JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ AND NAME='AAAAAA';

结论：左连接、右连接的结果并集
SELECT * FROM TAB1 FULL JOIN TAB2 ON TAB1.SIZ = TAB2.SIZ WHERE NAME='AAAAAA';

结论：只显示左右匹配记录

总结
上述结果的关键原因是由于left join，right join，full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。