oracle-join用法

Oracle join

1. 条件连接(join) 2. T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression 3. T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list ) 4. T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 5. INNER 和 OUTER 对所有连接（join） 类型都是可选的．INNER 是缺省； LEFT，RIGHT，和 FULL 隐含外连接． 6. 7. 连接条件在ON或USING子句里声明， 或者用关键字NATURAL隐含地声明．连接条件判断来自两个源表 中的那些行是\匹配\的，这些我们将在下面详细解释． 8. 9. ON子句是最常见的连接条件的类型∶它接收一个和WHERE子句里用的一样的 布尔值表达式．如果两个分别来自T1和T2的行在ON表达式上运算的 结果为真，那么它们就算是匹配的行． 10. 11. USING是缩写的概念∶它接收一个用逗号分隔的字段名字列表， 这些字段必须是连接表共有的，最终形成一个连接条件，表示 这些字段对必须相同．最后，JOIN USING 的输出会为每一对相等 的输入字段输出一个字段，后面跟着来自各个表的所有其它字段． 因此，USING (a, b, c) 等效于 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 只不过是如果使用了ON，那么在结果里 a，b，和 c字段每个都会有两个， 而用USING的时候每个字段就只会有一个． 12. 13. 最后，NATURAL 是USING 的缩写形式∶它形成一个 USING 列表， 该列表由那些在两个表里都出现了的字段名字组成．和USING一样， 这些字段只在输出表里出现一次． 14. 15. 条件JOIN的可能类型是∶ 16. 17. 18. INNER JOIN 19. 对于 T1 的每一行 R1，生成的连接表都有一行对应 T2 中的 每一个满足和 R1 的连接条件的行． 20. 21. LEFT OUTER JOIN 22. 首先，执行一次内连接．然后，为 T1 里那些和 T2 里任何一行都不满足连接条件的行返回一个连接行， 同时该连接行里对应 T2 的列用空值补齐．因此， 生成的连接表里无条件地包含来自 T1 里的每一行至少 一个副本． 23. 24. RIGHT OUTER JOIN 25. 首先，执行一次内连接．然后，为 T2 里那些和 T1 里任何一行都不满足连接条件的行返回一个连接行， 同时该连接行里对应 T1 的列用空值补齐．因此， 生成的连接表里无条件地包含来自 T2 里的每一行． 26. 27. FULL OUTER JOIN 28. 首先，执行一次内连接．然后，为 T1 里那些和 T2 里任何一行都不满足连接条件的行返回一个连接行， 同时该连接行里对应 T2 的列用空值补齐． 同样，为 T2 里那些和 T1 里的任何行都不满足连接条件的 行返回一个连接行，该行里对应 T1 的列用空值补齐． 29. 30. 如果 T1 和 T2 有一个或者都是可以连接(join)的表， 那么所有类型的连接都可以串在一起或嵌套在一起． 你可以在JOIN子句周围使用圆括弧来控制连接顺序， 如果没有圆括弧，那么JOIN子句是从左向右嵌套的． 31. 32. 为了解释这些问题，假设我们有一个表 t1 33. 34. num | name 35. -----+------ 36. 1 | a 37. 2 | b 38. 3 | c 39. 和 t2 40. 41. num | value 42. -----+------- 43. 1 | xxx 44. 3 | yyy 45. 5 | zzz 46. 然后我们用不同的连接方式可以获得各种结果： 47. 48. => SELECT * FROM t1 CROSS JOIN t2; 49. num | name | num | value 50. -----+------+-----+------- 51. 1 | a | 1 | xxx 52. 1 | a | 3 | yyy 53. 1 | a | 5 | zzz 54. 2 | b | 1 | xxx 55. 2 | b | 3 | yyy 56. 2 | b | 5 | zzz 57. 3 | c | 1 | xxx 58. 3 | c | 3 | yyy 59. 3 | c | 5 | zzz 60. (9 rows) 61. 62. => SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num; 63. num | name | num | value 64. -----+------+-----+------- 65. 1 | a | 1 | xxx 66. 3 | c | 3 | yyy 67. (2 rows) 68. 69. => SELECT * FROM t1 INNER JOIN t2 USING (num); 70. num | name | value 71. -----+------+------- 72. 1 | a | xxx 73. 3 | c | yyy 74. (2 rows) 75. 76. => SELECT * FROM t1 NATURAL INNER JOIN t2; 77. num | name | value 78. -----+------+------- 79. 1 | a | xxx 80. 3 | c | yyy 81. (2 rows) 82. 83. => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num; 84. num | name | num | value 85. -----+------+-----+------- 86. 1 | a | 1 | xxx 87. 2 | b | | 88. 3 | c | 3 | yyy 89. (3 rows) 90. 91. => SELECT * FROM t1 LEFT JOIN t2 USING (num); 92. num | name | value 93. -----+------+------- 94. 1 | a | xxx 95. 2 | b | 96. 3 | c | yyy 97. (3 rows) 98. 99. => SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num; 100. num | name | num | value 101. -----+------+-----+------- 102. 1 | a | 1 | xxx 103. 3 | c | 3 | yyy 104. | | 5 | zzz 105. (3 rows) 106. 107. => SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num; 108. num | name | num | value 109. -----+------+-----+------- 110. 1 | a | 1 | xxx 111. 2 | b | | 112. 3 | c | 3 | yyy 113. | | 5 | zzz 114. (4 rows) 115. 116. 用 ON 声明的连接条件也可以包含与连接不直接相关 的条件。这种功能可能对某些查询很有用，但是需要我们仔细想想。 比如： 117. 118. => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx'; 119. num | name | num | value 120. -----+------+-----+------- 121. 1 | a | 1 | xxx 122. 2 | b | | 123. 3 | c | | 124. (3 rows)

假设有 a表 id 1 2 3 4 b表 id 1 2 3

希望出来结果

a.id b.id 1 1 2 2 3 3 4

用left join 的

SQL code select a.id,b.id form a leftjion b on a.id=b.id

用(+)的

SQL code select a.id,b.id form a ,b

where a.id=b.id(+)

用(+)的语法简洁，容易读懂，但只是ORACLE支持，LEFT JOIN是通用的，其它没有差别

zengdan84：

select * from a, b where a.id = b.id;

对于外连接，Oracle中可以使用“(+)”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN，下面将配合实例一一介绍。

1. LEFT OUTER JOIN：左外关联

SELECT e.last_name, e.department_id, d.department_name FROM employees e

LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

等价于

SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d

WHERE e.department_id=d.department_id(+);

结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录。

2. RIGHT OUTER JOIN：右外关联

SELECT e.last_name, e.department_id, d.department_name FROM employees e

RIGHT OUTER JOIN departments d

ON (e.department_id = d.department_id); 等价于

SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d

WHERE e.department_id(+)=d.department_id;

结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。

3. FULL OUTER JOIN：全外关联

SELECT e.last_name, e.department_id, d.department_name FROM employees e

FULL OUTER JOIN departments d

ON (e.department_id = d.department_id);

结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。

chensi05：

说简单点就是哪边少加号放哪边

wuchunyu002： 外连接:

除了显示匹配相等连接条件的数据外,还可以显示某一个表中无法匹配相等连接条件的记录! ------------------------------------------------ 1) 左条件(+) = 右条件

左条件所在的表必须严格进行相等连接条件的匹配,而右条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据! 也称为右外连接.

-------------------------------- 可以用下列语句取代:

SELECT...FROM 表1 RIGHT OUTER JOIN 表2 ON 条件;

2) 左条件 = 右条件(+)

右条件所在的表必须严格进行相等连接条件的匹配,而左条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据! 也称为左外连接.

--------------------------------

可以用下列语句取代:

SELECT...FROM 表1 LEFT OUTER JOIN 表2 ON 条件;

REM 除了相等连接之外,显示没有员工的部门信息.

SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;

SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; REM 除了相等连接之外,显示没有部门的员工信息.

SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO(+);

SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

shenliang1985(沈亮)：

Oracle 建议你用在From语句后用Outer Join语法而不是Oracle的Join操作符(+).而且(+)是要受下面的规则限制的，但Outer Join语法则不受的～～

1）你不可以在查询块中使用(+) 当它同时包含 join的from语句中 2）(+)只是在where语句中,并且只能对应一个表或视图的一行字段

3）如果A和B做联接时有多个条件，那么(+)必须完善所有的匹配条件， 如果没有 ,oracle不会警告你～只是结果自然不同的

4）不可以在作出一个表外查询另张表内查询的(+)联接操作～～ 5）不可以用（+）外联接到自己当然Self Join是可以的 6）含（+）的Where后的注意 OR不可用 IN不可用 子查询不可用

以下给些个例子： SQL code SQL> desc part

Name Null? Type

----------------------------------------- -------- ----------------- PART_ID NOT NULL

VARCHAR2(4)

SUPPLIER_ID VARCHAR2(4)

SQL> select *

from part;

PART SUPP ---- ---- P1 S1 P2 S2 P3 P4

SQL> desc supplier

Name Null? Type

----------------------------------------- -------- ----------------- SUPPLIER_ID NOT NULL

VARCHAR2(4)

SUPPLIER_NAME NOT

NULL VARCHAR2(20)

SQL> select *

from supplier;

SUPP SUPPLIER_NAME ---- -------------------- S1 Supplier#1 S2 Supplier#2 S3 Supplier#3

SQL>

select p.part_id, s.supplier_name 2

from part p, supplier s 3

where p.supplier_id=s.supplier_id (+);

PART SUPPLIER_NAME ---- -------------------- P1 Supplier#1 P2 Supplier#2 P3 P4

--(+)是单向的

SQL>

select p.part_id, s.supplier_name 2

from part p, supplier s

3

where p.supplier_id (+) =s.supplier_id (+); where p.supplier_id (+) =s.supplier_id (+) *

ERROR at line 3:

ORA-01468: a predicate may reference only one outer-joined table --实现Full Join的方法

SQL>

select p.part_id, s.supplier_name

2

from part p, supplier s 3

where p.supplier_id=s.supplier_id (+) 4 union 5

select p.part_id, s.supplier_name 6

from part p, supplier s 7

where p.supplier_id (+) =s.supplier_id;

PART SUPPLIER_NAME ---- -------------------- P1 Supplier#1 P2 Supplier#2 P3 P4

Supplier#3 --现在的语法

SQL>

select p.part_id, s.supplier_name 2

from part p full outer

join supplier s

3

on p.supplier_id=s.supplier_id;

PART SUPPLIER_NAME ---- -------------------- P1 Supplier#1 P2 Supplier#2 P4 P3 Supplier#3

另外的EG：

SQL code SQL> select *

from testa;

ID NAME ADDR AGE

---------- ------ ------ ----------

3电子南京23 5物理北京23 6物理河北25 1电脑上海23 2电子北京22 4物理芜湖22 7艺术上海21 8歌剧苏州16 9沈亮上海22

已选择9行。

SQL> select *

from testb;

ID ADDR

---------- ---------- 7上海 1北京 3上海 5合肥 --匹配完全

select testa.id,testa.addr from testa,testb where testa.id(+)=testb.id and testa.addr(+)=testb.addr;

ID ADDR ---------- ------ 7上海

现在的Outer Join方法是Oracle9i时开始引用的 ANSI标准的联接语法，现在的则比较直观简单

通过上边Full Join的实现方式不难看到ANSI的联接语法要比用含Union的(+)的执行效率更高～～

本文来源：https://www.bwwdw.com/article/yd2x.html