sql学习 inner join,left join,right join
总结:
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.