一根稻草会压死牛,我实在忍受不了我的拖延症了!
(一)left join(left outer join)
SQL> select * from test1;
ID STATE NAME
---------- ---------- ----------
1 2 wang
2 3 li
3 4 zhou
5 6 yang
1 7 guo
SQL> select * from test2;
ID STATE NAME
---------- ---------- ----------
1 2 luo
2 3 liang
3 5 lin
4 6 peng
SQL> select * from test1 a left join test2 b on a.id=b.id; ---A表有B表没有的,B表显示空
ID STATE NAME ID STATE NAME
---------- ---------- ---------- ---------- ---------- ----------
1 7 guo 1 2 luo
1 2 wang 1 2 luo
2 3 li 2 3 liang
3 4 zhou 3 5 lin
5 6 yang null null(解释)
ID STATE NAME
---------- ---------- ----------
4 6 peng
(三)inner join
SQL> select * from test1 a inner join test2 b on a.id=b.id;
ID STATE NAME ID STATE NAME
---------- ---------- ---------- ---------- ---------- ----------
1 2 wang 1 2 luo
2 3 li 2 3 liang
3 4 zhou 3 5 lin
1 7 guo 1 2 luo
SQL> select a.* from test1 a inner join test2 b on a.id=b.id;
ID STATE NAME
---------- ---------- ----------
1 2 wang
2 3 li
3 4 zhou
1 7 guo
SQL> select * from test1 a inner join test2 b on a.id=b.id and a.state=b.state;
ID STATE NAME ID STATE NAME
---------- ---------- ---------- ---------- ---------- ----------
1 2 wang 1 2 luo
2 3 li 2 3 liang
百度说法:
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
(一)left join(left outer join)
SQL> select * from test1;
ID STATE NAME
---------- ---------- ----------
1 2 wang
2 3 li
3 4 zhou
5 6 yang
1 7 guo
SQL> select * from test2;
ID STATE NAME
---------- ---------- ----------
1 2 luo
2 3 liang
3 5 lin
4 6 peng
SQL> select * from test1 a left join test2 b on a.id=b.id; ---A表有B表没有的,B表显示空
ID STATE NAME ID STATE NAME
---------- ---------- ---------- ---------- ---------- ----------
1 7 guo 1 2 luo
1 2 wang 1 2 luo
2 3 li 2 3 liang
3 4 zhou 3 5 lin
5 6 yang null null(解释)
SQL> select * from test1 a left join test2 b on a.id=b.id and a.state=b.state; ----两个字段也一样
ID STATE NAME ID STATE NAME
---------- ---------- ---------- ---------- ---------- ----------
1 2 wang 1 2 luo
2 3 li 2 3 liang
5 6 yang
3 4 zhou
1 7 guo
SQL> select a.* from test1 a left join test2 b on a.id=b.id where b.id is null; -----取A表有B表没有的数据
ID STATE NAME
---------- ---------- ----------
5 6 yang
(二)right join
SQL> select * from test1 a right join test2 b on a.id=b.id;--A表没有的数据显示为空
ID STATE NAME ID STATE NAME
---------- ---------- ---------- ---------- ---------- ----------
1 2 wang 1 2 luo
2 3 li 2 3 liang
3 4 zhou 3 5 lin
1 7 guo 1 2 luo
4 6 peng
SQL> select b.* from test1 a right join test2 b on a.id=b.id where a.id is null; --取 B表有A表没有的数据
ID STATE NAME ID STATE NAME
---------- ---------- ---------- ---------- ---------- ----------
1 2 wang 1 2 luo
2 3 li 2 3 liang
5 6 yang
3 4 zhou
1 7 guo
SQL> select a.* from test1 a left join test2 b on a.id=b.id where b.id is null; -----取A表有B表没有的数据
ID STATE NAME
---------- ---------- ----------
5 6 yang
(二)right join
SQL> select * from test1 a right join test2 b on a.id=b.id;--A表没有的数据显示为空
ID STATE NAME ID STATE NAME
---------- ---------- ---------- ---------- ---------- ----------
1 2 wang 1 2 luo
2 3 li 2 3 liang
3 4 zhou 3 5 lin
1 7 guo 1 2 luo
4 6 peng
ID STATE NAME
---------- ---------- ----------
4 6 peng
(三)inner join
SQL> select * from test1 a inner join test2 b on a.id=b.id;
ID STATE NAME ID STATE NAME
---------- ---------- ---------- ---------- ---------- ----------
1 2 wang 1 2 luo
2 3 li 2 3 liang
3 4 zhou 3 5 lin
1 7 guo 1 2 luo
SQL> select a.* from test1 a inner join test2 b on a.id=b.id;
ID STATE NAME
---------- ---------- ----------
1 2 wang
2 3 li
3 4 zhou
1 7 guo
SQL> select * from test1 a inner join test2 b on a.id=b.id and a.state=b.state;
ID STATE NAME ID STATE NAME
---------- ---------- ---------- ---------- ---------- ----------
1 2 wang 1 2 luo
2 3 li 2 3 liang
百度说法:
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30289795/viewspace-2139731/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30289795/viewspace-2139731/