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.