总结:
JOIN(或 INNER JOIN)只返回两个表中匹配的记录。
LEFT JOIN(或 LEFT OUTER JOIN)从左表返回所有的记录,即使右表中没有匹配。如果右表中没有匹配,则结果中右表的部分将是 NULL。
RIGHT JOIN(或 RIGHT OUTER JOIN)从右表返回所有的记录,即使左表中没有匹配。如果左表中没有匹配,则结果中左表的部分将是 NULL。
$ db2 "select * from liys.test1"
ID NAME
----------- --------------------
1 test_01
2 test_02
3 test_03
3 record(s) selected.
$ db2 "select * from liys.test2"
ID NAME
----------- --------------------
3 test_03
4 test_04
5 test_05
3 test_03_03
4 record(s) selected.
$ db2 "select t1.id ,t1.name ,t2.id, t2.name from liys.test1 t1 inner join liys.test2 t2 on t1.id=t2.id "
ID NAME ID NAME
----------- -------------------- ----------- --------------------
3 test_03 3 test_03
3 test_03 3 test_03_03
2 record(s) selected.
$ db2 "select t1.id ,t1.name ,t2.id, t2.name from liys.test1 t1 left join liys.test2 t2 on t1.id=t2.id "
ID NAME ID NAME
----------- -------------------- ----------- --------------------
3 test_03 3 test_03
3 test_03 3 test_03_03
2 test_02 - -
1 test_01 - -
4 record(s) selected.
$ db2 "select t1.id ,t1.name ,t2.id, t2.name from liys.test1 t1 right join liys.test2 t2 on t1.id=t2.id "
ID NAME ID NAME
----------- -------------------- ----------- --------------------
3 test_03 3 test_03
3 test_03 3 test_03_03
- - 5 test_05
- - 4 test_04
4 record(s) selected.