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

最新推荐文章于 2023-08-07 16:53:51 发布

原创最新推荐文章于 2023-08-07 16:53:51 发布 · 置顶 · 2.8k 阅读

2 ·

CC 4.0 BY-SA版权

SQLServer数据库专栏收录该内容

6 篇文章

订阅专栏

本文通过具体的MySQL查询示例，深入解析了左连接(left join)如何在两个表之间匹配记录，特别是在处理NULL值时的行为。同时，展示了使用UNION ALL结合左连接和右连接，来获取更全面的数据集的过程。

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)