ORACLE外连接小结~

分类：?ORACLE开发2009-11-22 20:40?536人阅读?评论(0)?收藏?举报

好几次想用到外连接?的时候都忘了具体的用法是怎样的，比如说（+）该加在等号的哪一端，或者LFET OUTER JOIN该用在整条语句中的哪个部分。今天正好又碰到一个相关的问题，借此机会总结一下，以后也方便查询，不用每次都去百度了。

?

[c-sharp]?view plaincopy

1. //table1和table2为两个测试表?随便插入几条数据??
2. SQL>?select?*?from?table1;??
3. ????????ID?NAME??
4. ----------?--------------------??
5. ?????????1?wh??
6. ?????????2?wp??
7. ?????????3?wq??
8. SQL>?select?*?from?table2;??
9. ????????ID?NAME??
10. ----------?--------------------??
11. ?????????4?wr??
12. ?????????1?wh??
13. //正常查询??
14. SQL>?select?a.name,b.name??
15. ??2??from?table1?a,table2?b??
16. ??3??where?a.id=b.id;??
17. NAME?????????????????NAME??
18. --------------------?--------------------??
19. wh???????????????????wh??
20. //显示出table1中的所有记录?table2中无相应记录则置NULL??
21. SQL>?select?a.name,b.name??
22. ??2??from?table1?a,table2?b??
23. ??3??where?a.id=b.id(+);??
24. NAME?????????????????NAME??
25. --------------------?--------------------??
26. wh???????????????????wh??
27. wq??
28. wp??
29. //显示出table2中的所有记录?table1中无相应记录则置NULL??
30. SQL>?select?a.name,b.name??
31. ??2??from?table1?a,table2?b??
32. ??3??where?a.id(+)=b.id;??
33. NAME?????????????????NAME??
34. --------------------?--------------------??
35. wh???????????????????wh??
36. ?????????????????????wr??
37. //呵呵,这里本想尝试一下全外连接,不过使用(+)好像不行??
38. SQL>?select?a.name,b.name??
39. ??2??from?table1?a,table2?b??
40. ??3??where?a.id(+)=b.id(+);??
41. where?a.id(+)=b.id(+)??
42. ?????????????*??
43. ERROR?at?line?3:??
44. ORA-01468:?a?predicate?may?reference?only?one?outer-joined?table??
45. //左外连接??
46. SQL>?select?a.name,b.name??
47. ??2??from?table1?a??
48. ??3??left?outer?join?table2?b??
49. ??4??on?a.id=b.id;??
50. NAME?????????????????NAME??
51. --------------------?--------------------??
52. wh???????????????????wh??
53. wq??
54. wp??
55. //右外连接??
56. SQL>?select?a.name,b.name??
57. ??2??from?table1?a??
58. ??3??right?outer?join?table2?b??
59. ??4??on?a.id=b.id;??
60. NAME?????????????????NAME??
61. --------------------?--------------------??
62. wh???????????????????wh??
63. ?????????????????????wr??
64. //右外连接???
65. SQL>?select?a.name,b.name??
66. ??2??from?table2?b??
67. ??3??right?outer?join?table1?a??
68. ??4??on?a.id=b.id;??
69. NAME?????????????????NAME??
70. --------------------?--------------------??
71. wh???????????????????wh??
72. wq??
73. wp??
74. //左外连接??
75. SQL>?select?a.name,b.name??
76. ??2??from?table2?b??
77. ??3??left?outer?join?table1?a??
78. ??4??on?a.id=b.id;??
79. NAME?????????????????NAME??
80. --------------------?--------------------??
81. wh???????????????????wh??
82. ?????????????????????wr??
83. //试一下FULL?OUTER?JOIN??
84. SQL>?select?a.name,b.name??
85. ??2??from?table2?b??
86. ??3??full?outer?join?table1?a??
87. ??4??on?a.id=b.id;??
88. NAME?????????????????NAME??
89. --------------------?--------------------??
90. wh???????????????????wh??
91. ?????????????????????wr??
92. wq??
93. wp??
94. SQL>?select?a.name,b.name??
95. ??2??from?table1?a??
96. ??3??full?outer?join?table2?b??
97. ??4??on?a.id=b.id;??
98. NAME?????????????????NAME??
99. --------------------?--------------------??
100. wh???????????????????wh??
101. wq??
102. wp??
103. ?????????????????????wr??

?

小结一下

左连接就是左边的表全有值，右边表的值可以为空（+）

右连接是左边表值可以为空（+），右边表的值全有

?

至于使用OUTER JOIN的方法就稍微直观一些了，LEFT就是显示左边的表的全部内容，RIGHT就是显示右边的表的全部内容，FULL就是两边表内容都全显示。

?

既然说到这了已经，那么再来看一下自然连接?。

?

[c-sharp]?view plaincopy

1. //使用using子句指定两个表相关联的列??
2. //注:被using?子句所引用的列,在sql?语句中的任何地方不能使用表名或者别名作为前缀??
3. SQL>?select?a.name,b.name??
4. ??2??from?table1?a??
5. ??3??join?table2?b??
6. ??4??using(id);??
7. NAME?????????????????NAME??
8. --------------------?--------------------??
9. wh???????????????????wh??
10. SQL>?select?name??
11. ??2??from?table1??
12. ??3??natural?join?table2;??
13. NAME??
14. --------------------??
15. wh??
16. //这里好像不让指定别名??
17. SQL>?select?a.name,b.name??
18. ??2??from?table1?a??
19. ??3??natural?join?table2?b;??
20. select?a.name,b.name??
21. ??????????????*??
22. ERROR?at?line?1:??
23. ORA-25155:?column?used?in?NATURAL?join?cannot?have?qualifier??

?

?

最后，来看一下交叉连接?，也就是笛卡尔积?。

?

[c-sharp]?view plaincopy

1. SQL>?select?a.name,b.name??
2. ??2??from?table1?a??
3. ??3??cross?join?table2?b;??
4. NAME?????????????????NAME??
5. --------------------?--------------------??
6. wh???????????????????wr??
7. wp???????????????????wr??
8. wq???????????????????wr??
9. wh???????????????????wh??
10. wp???????????????????wh??
11. wq???????????????????wh??
12. 6?rows?selected.??

?

?

下面是几个容易让人误认为不一样的东西，其实不然。

inner join = join

left outer join = left join

right outer join = right join