关于的外连接(left join或right join)我们常犯的一个严重错误

关于的外连接(left join或right join)我们常犯的一个严重错误
left join,right join ,我们常犯的一个错误就是以为条件写在on 后面和写在where里是一样的,我前几天刚遇到这样的问题,起初觉得很奇怪,明明觉得正确的sql却得到了意外的结果. 下表我们假设是testLeftJoin ,有两列,共三行数据:
id | name --------------------------------------------------------------------------------1  | aaa
 2  | bbb
3  | ccc
测试如下几个sql 看结果有什么不同:
1,SELECT * FROM testLeftJoin a LEFT OUTER JOIN      testLeftJoin b ON a.id = b.id
2,SELECT * FROM testLeftJoin a LEFT OUTER JOIN      testLeftJoin b ON a.id = 1
3,SELECT *FROM testLeftJoin a LEFT OUTER JOIN      testLeftJoin b ON a.id = b.id WHERE (a.id = 1)
4,SELECT *FROM testLeftJoin a LEFT OUTER JOIN      testLeftJoin b ON a.id = b.id and  (a.id = 1)
测试的解决如下：
sql1:3条记录,没有什么争议;
sql2: 5条记录,估计大家都想不到,如图所示!
sql3:  只有1条记录,也没有争议,大家都明白;
sql4:  3条记录,如图所示.     

 

 

 left join 是首先按照左边的表的第一条记录开始,逐一和右边的表的每一行按照条件去匹配,符合on后面条件的,就显示出来,如果不符合on后的条件就将有关字段显示成null,如果不考虑where后的条件,那么不管on的条件怎么样, left join后,生成的结果集的记录数一定大于或等于left jion前的表的记录数.SELECT * FROM testLeftJoin a LEFT OUTER JOIN      testLeftJoin b ON a.id = 1中就是这样。结果中的前三行符合a.id=1的条件，所有右边的行全部显示出来了，而后面两条就是不合符a.id=1的条件，所以右边为null（一个问题出来了，那应该是9条记录啊，不过可能是distinct了吧！）
内联接（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students 和 courses 表中学生标识号相同的所有行。

外联接。外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定：

LEFT JOIN 或 LEFT OUTER JOIN。
左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

RIGHT JOIN 或 RIGHT OUTER JOIN。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

FULL JOIN 或 FULL OUTER JOIN。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

交叉联接。
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。