Oracle的表连接之内链接，外连接

内链接：

1自然连接，natural join 需要两个表有相同字段名，且数据类型相同，首先先查看两张表的表结构

链接时连接的是两张表的相同字段的相同数据的行：具体内同如下

SQL> select department_id,location_id

2 from departments;

 

DEPARTMENT_ID LOCATION_ID

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

10 1700

20 1800

30 1700

40 2400

50 1500

60 1400

70 2700

80 2500

90 1700

100 1700

110 1700

120 1700

130 1700

140 1700

150 1700

160 1700

170 1700

180 1700

190 1700

200 1700

210 1700

220 1700

230 1700

240 1700

250 1700

260 1700

270 1700

 

27 rows selected.

 

SQL> select location_id,city

2 from locations;

 

LOCATION_ID CITY

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

1000 Roma

1100 Venice

1200 Tokyo

1300 Hiroshima

1400 Southlake

1500 South San Francisco

1600 South Brunswick

1700 Seattle

1800 Toronto

1900 Whitehorse

2000 Beijing

2100 Bombay

2200 Sydney

2300 Singapore

2400 London

2500 Oxford

2600 Stretford

2700 Munich

2800 Sao Paulo

2900 Geneva

3000 Bern

3100 Utrecht

3200 Mexico City

 

23 rows selected.

 

SQL> select department_id,location_id,city

2 from departments natural join locations;

 

 

DEPARTMENT_ID LOCATION_ID CITY

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

60 1400 Southlake

50 1500 South San Francisco

10 1700 Seattle

30 1700 Seattle

90 1700 Seattle

100 1700 Seattle

110 1700 Seattle

120 1700 Seattle

130 1700 Seattle

140 1700 Seattle

150 1700 Seattle

160 1700 Seattle

170 1700 Seattle

180 1700 Seattle

190 1700 Seattle

200 1700 Seattle

210 1700 Seattle

220 1700 Seattle

230 1700 Seattle

240 1700 Seattle

250 1700 Seattle

260 1700 Seattle

270 1700 Seattle

20 1800 Toronto

40 2400 London

80 2500 Oxford

70 2700 Munich

 

27 rows selected.

自然连接的公共字段不能添加限定指定是某个表中的字段入 employees.employee_id,别名也不可使用

自然连接是将相同字段全部关联，但是如果只想关联某一个字段时，便是用using字句

using字句不能与自然连接同时使用公共字段不能添加限定指定是某个表中的字段入 employees.employee_id,别名也不可使用，且两张表的相同字段名但是为非公共字段必须指明是那个表中的字段

 

ON 字句： 连接指定字段：（可以是不同字段名内容相同的字段）

SQL> select e.employee_id,e.last_name,e.department_id,d.department_id

2 from employees e join departments d

3 on (e.department_id=d.department_id)

4 where d.department_id=50;

 

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID

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

198 OConnell 50 50

199 Grant 50 50

120 Weiss 50 50

121 Fripp 50 50

122 Kaufling 50 50

123 Vollman 50 50

124 Mourgos 50 50

125 Nayer 50 50

126 Mikkilineni 50 50

127 Landry 50 50

128 Markle 50 50

 

等同于：

SQL> select e.employee_id,e.last_name,e.department_id,d.department_id

from employees e join departments d

on (e.department_id=d.department_id)

and d.department_id=50;

 

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID

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

198 OConnell 50 50

199 Grant 50 50

120 Weiss 50 50

121 Fripp 50 50

122 Kaufling 50 50

123 Vollman 50 50

124 Mourgos 50 50

125 Nayer 50 50

126 Mikkilineni 50 50

127 Landry 50 50

 

on字句在关联时，可以使用限定，而using字句不行，具体如下

情景：在employees表中有管理人员和员工两类人想制作一张关系对应表时，首先得对应id号和对应的名字，在同一张表中名字在查询时才会根据id对应，比如查询员工id和名字，以及管理人员和名字时是两张不同数据的表，展示如下

SQL> select employee_id,last_name from employees order by employee_id;

 

EMPLOYEE_ID LAST_NAME

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

100 King

101 Kochhar

102 De Haan

103 Hunold

104 Ernst

105 Austin

106 Pataballa

107 Lorentz

108 Greenberg

109 Faviet

110 Chen

SQL> select employee_id,manager_id,last_name from employees order by manager_id

2 ;

 

EMPLOYEE_ID MANAGER_ID LAST_NAME

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

201 100 Hartstein

101 100 Kochhar

102 100 De Haan

114 100 Raphaely

120 100 Weiss

121 100 Fripp

122 100 Kaufling

123 100 Vollman

124 100 Mourgos

145 100 Russell

146 100 Partners

如上所示，一个管理id可以对应多个人员id，但一个人员id只对应一个名字，所以只要关联管理者id即可看出对应关系和各管理id名字操作如下：

select e.employee_id,e.last_name,e.manager_id,m.employee_id,m.last_name

from employees e join employees m

on(e.manager_id=m.employee_id);

EMPLOYEE_ID LAST_NAME MANAGER_ID EMPLOYEE_ID LAST_NAME

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

173 Kumar 148 148 Cambrault

172 Bates 148 148 Cambrault

171 Smith 148 148 Cambrault

170 Fox 148 148 Cambrault

169 Bloom 148 148 Cambrault

168 Ozer 148 148 Cambrault

103 Hunold 102 102 De Haan

167 Banda 147 147 Errazuriz

166 Ande 147 147 Errazuriz

165 Lee 147 147 Errazuriz

164 Marvins 147 147 Errazuriz

163 Greene 147 147 Errazuriz

162 Vishney 147 147 Errazuriz

187 Cabrio 121 121 Fripp

186 Dellinger 121 121 Fripp

185 Bull 121 121 Fripp

如果只是查看所有管理员操作如下：

 

SQL> select employee_id,last_name

2 from employees

3 where employee_id in (select manager_id from employees);

 

EMPLOYEE_ID LAST_NAME

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

148 Cambrault

102 De Haan

147 Errazuriz

121 Fripp

108 Greenberg

外连接：当一张表的某个字段存在空数据时，内链接无法连接空值，时候连接有数据的值，如果想将空值也显示就用外连接

左连接，以左边的数据为主进行连接，右连接：以右边的数据为主进行连接，全外连接就是将两边的数值全部显示

以上是sql99的标准的写法。而Oracle自己的写法是将连接对象的对方结尾加加号，如下是左连接：

如下是右连接;

左连接加号在左边右连接加号在左边

注意没有全外连接

小技巧： 替换可用 C 命令： l#:表示显示上一个命令的第#行；SQL> l

1 select deptid,salary, row_number() OVER (PARTITION BY deptid ORDER BY salary)

2* from employee

SQL> l2

2* from employee

SQL> c/employee/employees;

2* from employees

SQL> l

1 select deptid,salary, row_number() OVER (PARTITION BY deptid ORDER BY salary)

2* from employees

笛卡儿积连接：及不需要任何条件进行连接

select employee_id,last_name,department_name

from employees cross join departments;

查看某句SQL的执行计划操作如下：

SQL> explain plan

2 for

3 select employee_id,last_name,department_name

4 from employees cross join departments;

 

Explained.

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1162840532

 

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 2889 | 69336 | 41 (0)| 00:00:01 |

| 1 | MERGE JOIN CARTESIAN| | 2889 | 69336 | 41 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 324 | 3 (0)| 00:00:01 |

| 3 | BUFFER SORT | | 107 | 1284 | 38 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1284 | 1 (0)| 00:00:01 |

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

 

11 rows selected.

 

第一步给到计划，第二部查看计划：