15.多表查询语句
1.等值与不等值连接
--限定模糊名：
----1.使用前缀可提高性能
----2.表别名可替代完整的表前缀
----3.表别名给表一个简短的名字
----4.使代码更短，使用更少内存
----5.列别名区分不同列，但在不同表里
--自然连接
----自然连接基于两个表的所有列有相同的名字
----从两个表选取所有匹配列的相同值
----如果同名列数据类型不匹配就会返回错误
hr@ORCLPDB01 2023-02-19 16:29:42> select department_id,department_name,location_id,city
2 from departments
3 natural join locations;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID CITY
------------- ------------------------------ ----------- ------------------------------
60 IT 1400 Southlake
50 Shipping 1500 South San Francisco
10 Administration 1700 Seattle
30 Purchasing 1700 Seattle
90 Executive 1700 Seattle
100 Finance 1700 Seattle
110 Accounting 1700 Seattle
120 Treasury 1700 Seattle
130 Corporate Tax 1700 Seattle
140 Control And Credit 1700 Seattle
150 Shareholder Services 1700 Seattle
160 Benefits 1700 Seattle
170 Manufacturing 1700 Seattle
180 Construction 1700 Seattle
190 Contracting 1700 Seattle
200 Operations 1700 Seattle
210 IT Support 1700 Seattle
220 NOC 1700 Seattle
230 IT Helpdesk 1700 Seattle
240 Government Sales 1700 Seattle
250 Retail Sales 1700 Seattle
260 Recruiting 1700 Seattle
270 Payroll 1700 Seattle
20 Marketing 1800 Toronto
40 Human Resources 2400 London
80 Sales 2500 Oxford
70 Public Relations 2700 Munich
27 rows selected.
----如果个别列有相同名字，但数据类型不同可用using语句指定等值连接的列
----有多列时using只匹配一列
----natural join与using互斥
hr@ORCLPDB01 2023-02-19 16:35:57> select employee_id,last_name,location_id,department_id
2 from employees join departments
3* using (department_id)
EMPLOYEE_ID LAST_NAME LOCATION_ID DEPARTMENT_ID
----------- ------------------------- ----------- -------------
200 Whalen 1700 10
201 Hartstein 1800 20
202 Fay 1800 20
114 Raphaely 1700 30
115 Khoo 1700 30
116 Baida 1700 30
117 Tobias 1700 30
118 Himuro 1700 30
----join on语句
----连接的条件基本上时同名列的等值连接
----为了指定一条件或指定列用于连接可以用on子句
----连接条件是与其他的查询条件分开的
----on子句更加容易理解
hr@ORCLPDB01 2023-02-19 16:42:30> r
1 select e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
2 from employees e join departments d
3* on (e.department_id = d.department_id)
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
200 Whalen 10 10 1700
201 Hartstein 20 20 1800
202 Fay 20 20 1800
114 Raphaely 30 30 1700
115 Khoo 30 30 1700
116 Baida 30 30 1700
117 Tobias 30 30 1700
hr@ORCLPDB01 2023-02-19 16:45:56> select employee_id,department_name
2 from employees e
3 join departments d on d.department_id = e.department_id
4* join locations l on d.location_id = l.location_id
EMPLOYEE_ID DEPARTMENT_NAME
----------- ------------------------------
100 Executive
101 Executive
102 Executive
103 IT
104 IT
----where语句或and语句可附加在连接语法
hr@ORCLPDB01 2023-02-19 16:50:38> select e.employee_id,e.last_name,e.department_id,
2 d.department_id,d.location_id
3 from employees e join departments d
4 on (e.department_id = d.department_id)
5 and e.manager_id = 149;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
174 Abel 80 80 2500
175 Hutton 80 80 2500
179 Johnson 80 80 2500
177 Livingston 80 80 2500
176 Taylor 80 80 2500
Elapsed: 00:00:00.01
hr@ORCLPDB01 2023-02-19 16:51:55> 5
5* and e.manager_id = 149
hr@ORCLPDB01 2023-02-19 16:52:02> c/and/where
5* where e.manager_id = 149
hr@ORCLPDB01 2023-02-19 16:52:10> l
1 select e.employee_id,e.last_name,e.department_id,
2 d.department_id,d.location_id
3 from employees e join departments d
4 on (e.department_id = d.department_id)
5* where e.manager_id = 149
hr@ORCLPDB01 2023-02-19 16:52:11> r
1 select e.employee_id,e.last_name,e.department_id,
2 d.department_id,d.location_id
3 from employees e join departments d
4 on (e.department_id = d.department_id)
5* where e.manager_id = 149
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
174 Abel 80 80 2500
175 Hutton 80 80 2500
179 Johnson 80 80 2500
177 Livingston 80 80 2500
176 Taylor 80 80 2500
Elapsed: 00:00:00.00
--自连接
hr@ORCLPDB01 2023-02-19 16:53:45> select worker.last_name emp,
2 manager.last_name mgr
3 from employees worker join employees manager
4 on (worker.manager_id = manager.employee_id);
EMP MGR
------------------------- -------------------------
Bates Cambrault
Bloom Cambrault
Fox Cambrault
Kumar Cambrault
Ozer Cambrault
Smith Cambrault
Hunold De Haan
--外连接
----两个表的内连接，返回行包含在左【右】表中不匹配的行，就是左【右】外连接
----两个表的内连接，返回行包含左表右表中不匹配的行，就是完全外连接。
hr@ORCLPDB01 2023-02-19 17:03:42> select e.last_name,e.department_id,d.department_name
2 from employees e left outer join departments d
3 on (e.department_id = d.department_id);
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
Whalen 10 Administration
Hartstein 20 Marketing
Fay 20 Marketing
Raphaely 30 Purchasing
Khoo 30 Purchasing
Baida 30 Purchasing
Tobias 30 Purchasing
Himuro 30 Purchasing
Colmenares 30 Purchasing
Mavris 40 Human Resources
Weiss 50 Shipping
Fripp 50 Shipping
Kaufling 50 Shipping
Vollman 50 Shipping
Mourgos 50 Shipping
Nayer 50 Shipping
Mikkilineni 50 Shipping
Landry 50 Shipping
Markle 50 Shipping
Bissot 50 Shipping
Atkinson 50 Shipping
Marlow 50 Shipping
Olson 50 Shipping
Mallin 50 Shipping
Rogers 50 Shipping
Gee 50 Shipping
Philtanker 50 Shipping
Ladwig 50 Shipping
Stiles 50 Shipping
Seo 50 Shipping
Patel 50 Shipping
Rajs 50 Shipping
Davies 50 Shipping
Matos 50 Shipping
Vargas 50 Shipping
Taylor 50 Shipping
Fleaur 50 Shipping
Sullivan 50 Shipping
Geoni 50 Shipping
Sarchand 50 Shipping
Bull 50 Shipping
Dellinger 50 Shipping
Cabrio 50 Shipping
Chung 50 Shipping
Dilly 50 Shipping
Gates 50 Shipping
Perkins 50 Shipping
Bell 50 Shipping
Everett 50 Shipping
McCain 50 Shipping
Jones 50 Shipping
Walsh 50 Shipping
Feeney 50 Shipping
OConnell 50 Shipping
Grant 50 Shipping
Hunold 60 IT
Ernst 60 IT
Austin 60 IT
Pataballa 60 IT
Lorentz 60 IT
Baer 70 Public Relations
Russell 80 Sales
Partners 80 Sales
Errazuriz 80 Sales
Cambrault 80 Sales
Zlotkey 80 Sales
Tucker 80 Sales
Bernstein 80 Sales
Hall 80 Sales
Olsen 80 Sales
Cambrault 80 Sales
Tuvault 80 Sales
King 80 Sales
Sully 80 Sales
McEwen 80 Sales
Smith 80 Sales
Doran 80 Sales
Sewall 80 Sales
Vishney 80 Sales
Greene 80 Sales
Marvins 80 Sales
Lee 80 Sales
Ande 80 Sales
Banda 80 Sales
Ozer 80 Sales
Bloom 80 Sales
Fox 80 Sales
Smith 80 Sales
Bates 80 Sales
Kumar 80 Sales
Abel 80 Sales
Hutton 80 Sales
Taylor 80 Sales
Livingston 80 Sales
Johnson 80 Sales
King 90 Executive
Kochhar 90 Executive
De Haan 90 Executive
Greenberg 100 Finance
Faviet 100 Finance
Chen 100 Finance
Sciarra 100 Finance
Urman 100 Finance
Popp 100 Finance
Higgins 110 Accounting
Gietz 110 Accounting
Grant
107 rows selected.
hr@ORCLPDB01 2023-02-19 17:05:35> r
1 select e.last_name,e.department_id,d.department_name
2 from employees e right outer join departments d
3* on (e.department_id = d.department_id)
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
Whalen 10 Administration
Fay 20 Marketing
Hartstein 20 Marketing
Tobias 30 Purchasing
Colmenares 30 Purchasing
Baida 30 Purchasing
Raphaely 30 Purchasing
Khoo 30 Purchasing
Himuro 30 Purchasing
Mavris 40 Human Resources
Feeney 50 Shipping
Fleaur 50 Shipping
Fripp 50 Shipping
Gates 50 Shipping
Gee 50 Shipping
Geoni 50 Shipping
Grant 50 Shipping
Jones 50 Shipping
Kaufling 50 Shipping
Ladwig 50 Shipping
Everett 50 Shipping
Dilly 50 Shipping
Dellinger 50 Shipping
Davies 50 Shipping
Chung 50 Shipping
Cabrio 50 Shipping
Bull 50 Shipping
Bissot 50 Shipping
Bell 50 Shipping
Atkinson 50 Shipping
Landry 50 Shipping
Weiss 50 Shipping
Walsh 50 Shipping
Vollman 50 Shipping
Vargas 50 Shipping
Taylor 50 Shipping
Mallin 50 Shipping
Markle 50 Shipping
Marlow 50 Shipping
Matos 50 Shipping
McCain 50 Shipping
Mikkilineni 50 Shipping
Mourgos 50 Shipping
Nayer 50 Shipping
OConnell 50 Shipping
Olson 50 Shipping
Patel 50 Shipping
Perkins 50 Shipping
Philtanker 50 Shipping
Rajs 50 Shipping
Rogers 50 Shipping
Sarchand 50 Shipping
Seo 50 Shipping
Stiles 50 Shipping
Sullivan 50 Shipping
Austin 60 IT
Pataballa 60 IT
Ernst 60 IT
Lorentz 60 IT
Hunold 60 IT
Baer 70 Public Relations
Abel 80 Sales
Ande 80 Sales
Banda 80 Sales
Bates 80 Sales
Bernstein 80 Sales
Bloom 80 Sales
Cambrault 80 Sales
Cambrault 80 Sales
Doran 80 Sales
Errazuriz 80 Sales
Fox 80 Sales
Greene 80 Sales
Hall 80 Sales
Hutton 80 Sales
Johnson 80 Sales
King 80 Sales
Kumar 80 Sales
Lee 80 Sales
Livingston 80 Sales
Marvins 80 Sales
McEwen 80 Sales
Olsen 80 Sales
Ozer 80 Sales
Partners 80 Sales
Russell 80 Sales
Sewall 80 Sales
Smith 80 Sales
Smith 80 Sales
Sully 80 Sales
Taylor 80 Sales
Tucker 80 Sales
Tuvault 80 Sales
Vishney 80 Sales
Zlotkey 80 Sales
Kochhar 90 Executive
King 90 Executive
De Haan 90 Executive
Popp 100 Finance
Urman 100 Finance
Chen 100 Finance
Faviet 100 Finance
Sciarra 100 Finance
Greenberg 100 Finance
Gietz 110 Accounting
Higgins 110 Accounting
Treasury
Corporate Tax
Control And Credit
Shareholder Services
Benefits
Manufacturing
Construction
Contracting
Operations
IT Support
NOC
IT Helpdesk
Government Sales
Retail Sales
Recruiting
Payroll
hr@ORCLPDB01 2023-02-19 17:06:42> r
1 select e.last_name,e.department_id,d.department_name
2 from employees e full outer join departments d
3* on (e.department_id = d.department_id)
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ------------- ------------------------------
King 90 Executive
Kochhar 90 Executive
De Haan 90 Executive
Hunold 60 IT
Ernst 60 IT
Austin 60 IT
Pataballa 60 IT
Lorentz 60 IT
Greenberg 100 Finance
Faviet 100 Finance
Chen 100 Finance
Sciarra 100 Finance
Urman 100 Finance
Popp 100 Finance
Raphaely 30 Purchasing
Khoo 30 Purchasing
Baida 30 Purchasing
Tobias 30 Purchasing
Himuro 30 Purchasing
Colmenares 30 Purchasing
Weiss 50 Shipping
Fripp 50 Shipping
Kaufling 50 Shipping
Vollman 50 Shipping
Mourgos 50 Shipping
Nayer 50 Shipping
Mikkilineni 50 Shipping
Landry 50 Shipping
Markle 50 Shipping
Bissot 50 Shipping
Atkinson 50 Shipping
Marlow 50 Shipping
Olson 50 Shipping
Mallin 50 Shipping
Rogers 50 Shipping
Gee 50 Shipping
Philtanker 50 Shipping
Ladwig 50 Shipping
Stiles 50 Shipping
Seo 50 Shipping
Patel 50 Shipping
Rajs 50 Shipping
Davies 50 Shipping
Matos 50 Shipping
Vargas 50 Shipping
Russell 80 Sales
Partners 80 Sales
Errazuriz 80 Sales
Cambrault 80 Sales
Zlotkey 80 Sales
Tucker 80 Sales
Bernstein 80 Sales
Hall 80 Sales
Olsen 80 Sales
Cambrault 80 Sales
Tuvault 80 Sales
King 80 Sales
Sully 80 Sales
McEwen 80 Sales
Smith 80 Sales
Doran 80 Sales
Sewall 80 Sales
Vishney 80 Sales
Greene 80 Sales
Marvins 80 Sales
Lee 80 Sales
Ande 80 Sales
Banda 80 Sales
Ozer 80 Sales
Bloom 80 Sales
Fox 80 Sales
Smith 80 Sales
Bates 80 Sales
Kumar 80 Sales
Abel 80 Sales
Hutton 80 Sales
Taylor 80 Sales
Livingston 80 Sales
Grant
Johnson 80 Sales
Taylor 50 Shipping
Fleaur 50 Shipping
Sullivan 50 Shipping
Geoni 50 Shipping
Sarchand 50 Shipping
Bull 50 Shipping
Dellinger 50 Shipping
Cabrio 50 Shipping
Chung 50 Shipping
Dilly 50 Shipping
Gates 50 Shipping
Perkins 50 Shipping
Bell 50 Shipping
Everett 50 Shipping
McCain 50 Shipping
Jones 50 Shipping
Walsh 50 Shipping
Feeney 50 Shipping
OConnell 50 Shipping
Grant 50 Shipping
Whalen 10 Administration
Hartstein 20 Marketing
Fay 20 Marketing
Mavris 40 Human Resources
Baer 70 Public Relations
Higgins 110 Accounting
Gietz 110 Accounting
IT Support
Operations
Payroll
Construction
Government Sales
Retail Sales
Contracting
Recruiting
Control And Credit
NOC
Treasury
Manufacturing
Corporate Tax
IT Helpdesk
Shareholder Services
Benefits
--交叉连接
----连接条件省略
----连接条件无效
----一个表所有行连接到另外一个表所有行
--要避免笛卡尔积需要定义有效的连接条件