您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记
oracle-连接查询
发布时间：2017-11-23 15:07:57编辑：雪饮阅读（）
左连接
SQL> select * from boy left join girl on boy.bid=girl.gid;
BID NAME GID NAME
---------- ---------- ---------- ----------
48 boy54 48 girl48
49 boy54 49 girl49
50 boy54 50 girl50
44 boy44
46 boy46
41 boy41
43 boy43
45 boy45
40 boy40
42 boy42
47 boy47
11 rows selected.
所有的左表记录取出来，至于右边记录只取和左表关联的，若不关联的则留空
右连接
SQL> select * from boy right join girl on boy.bid=girl.gid;
BID NAME GID NAME
---------- ---------- ---------- ----------
48 boy54 48 girl48
49 boy54 49 girl49
50 boy54 50 girl50
53 girl53
54 girl54
51 girl51
52 girl52
7 rows selected.
内连接
SQL> select * from boy inner join girl on boy.bid=girl.gid;
BID NAME GID NAME
---------- ---------- ---------- ----------
48 boy54 48 girl48
49 boy54 49 girl49
50 boy54 50 girl50
找到双方表都互相关联的数据
全外连接
全外连接是左连接与右连接的并集
SQL> select * from boy full outer join girl on boy.bid=girl.gid;
BID NAME GID NAME
---------- ---------- ---------- ----------
48 boy54 48 girl48
49 boy54 49 girl49
50 boy54 50 girl50
44 boy44
46 boy46
41 boy41
43 boy43
45 boy45
40 boy40
42 boy42
47 boy47
BID NAME GID NAME
---------- ---------- ---------- ----------
53 girl53
54 girl54
51 girl51
52 girl52
15 rows selected.
关键字词：oracle,连接查询