Join中on条件是null的问题讨论

MySQL [dbs]> select * from test1;              
+----+--------+---------+
| id | stu_id | stu_age |
+----+--------+---------+
|  1 |      1 |      25 |
|  2 |      1 |    NULL |
+----+--------+---------+
2 rows in set (0.00 sec)

MySQL [dbs]> select * from test2;
+----+--------+---------+
| id | stu_id | stu_age |
+----+--------+---------+
|  1 |      1 |      25 |
|  2 |      1 |    NULL |
+----+--------+---------+
2 rows in set (0.00 sec)

MySQL [dbs]> select * from test1 
    -> left join test2 
    -> on test1.stu_id = test2.stu_id 
    -> and test1.stu_age = test2.stu_age;
+----+--------+---------+------+--------+---------+
| id | stu_id | stu_age | id   | stu_id | stu_age |
+----+--------+---------+------+--------+---------+
|  1 |      1 |      25 |    1 |      1 |      25 |
|  2 |      1 |    NULL | NULL |   NULL |    NULL |
+----+--------+---------+------+--------+---------+
2 rows in set (0.00 sec)

MySQL [dbs]> select * from test1 
    -> left join test2 
    -> on test1.stu_id = test2.stu_id 
    -> and test1.stu_age = test2.stu_age
    -> union all 
    -> select * from test1
    -> right join test2
    -> on test1.stu_id = test2.stu_id 
    -> and test1.stu_age = test2.stu_age;
+------+--------+---------+------+--------+---------+
| id   | stu_id | stu_age | id   | stu_id | stu_age |
+------+--------+---------+------+--------+---------+
|    1 |      1 |      25 |    1 |      1 |      25 |
|    2 |      1 |    NULL | NULL |   NULL |    NULL |
|    1 |      1 |      25 |    1 |      1 |      25 |
| NULL |   NULL |    NULL |    2 |      1 |    NULL |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)

从第三个join中可以看到，在on条件当中，只要有任何一个字段为null，则另外一边肯定都是null，即1 null 并不等于1 null.