Oracle数据库：oracle外连接left/right/full outer join on,oracle扩展的左右外连接展示符号(+)
2022找工作是学历、能力和运气的超强结合体，遇到寒冬，大厂不招人，可能很多算法学生都得去找开发，测开
测开的话，你就得学数据库，sql，oracle，尤其sql要学，当然，像很多金融企业、安全机构啥的，他们必须要用oracle数据库
这oracle比sql安全，强大多了，所以你需要学习，最重要的，你要是考网络警察公务员，这玩意你不会就别去报名了，耽误时间！
oracle系列文章：
【1】Oracle数据库：啥是oracle数据库？你为啥要学oracle？
【2】Oracle数据库：oracle 11g安装教程，已安装好的oracle各个文件夹的作用，oracle用户权限怎么样
【3】Oracle数据库：oracle启动，oracle客户端工具plsql安装教程和使用方法
【4】Oracle数据库：创建表空间，创建新用户，给用户分配对象、角色和系统权限，登录新用户建表
【5】Oracle数据库：链接配置，包括sqlnet.ora里面的transnames.ora配置数据库标识符SID，listener暂时简单了解
【6】Oracle数据库：net configureation assistant工具配置监听listener，配置本地网络访问服务器上的数据库
【7】Oracle数据库：oracle字符类型、数字类型、创建表表名的命名规则
【8】Oracle数据库：约束条件：主键约束、唯一约束、检查约束、非空约束、外键约束、默认值填写
【9】Oracle数据库：表的关系：一对多，一对一，多对多，一一继承和修改的关系，表不是重点，重点是数据和约束关系
【10】Oracle数据库：sql语言结构，数据查询语言DQL，select * from table；算术，别名，连接，去重等操作
【11】Oracle数据库：约束行限制where语句，判断条件，比较条件，字符串日期格式，in,like,escape,null语句
【12】Oracle数据库：逻辑运算and，or，not和各种运算的优先级控制
【13】Oracle数据库：排序order by语句，select from where order by的执行先后顺序，各种样例
【14】Oracle数据库：oracle函数，单行函数，多行函数，upper，lower，initcap，字符串函数
【15】Oracle数据库：数字函数，日期函数，round，trunc，mod，months_between,add_months,next_day,last_day,sysdate
【16】Oracle数据库：oracle数据类型转换to_char()日期和数字转字符,to_number()字符转数字,to_date()字符转日期函数
【17】Oracle数据库：oracle函数嵌套，nvl函数，nvl2函数，nullif函数，coalesce合并函数
【18】Oracle数据库：条件表达式case when then else end，decode函数，oracle单行函数练习示例
【19】Oracle数据库：oracle多表查询，等值连接，非等值连接，自连接的sql语句和规则
oracle外连接
left
right
full
outer join
sql99的标准
on子句
在from子句后面
啥是on子句呢？？？？？
没见过
SQL> desc employees;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE_ID NUMBER(6) Primary key of employees table.
FIRST_NAME VARCHAR2(20) Y First name of the employee. A not null column.
LAST_NAME VARCHAR2(25) Last name of the employee. A not null column.
EMAIL VARCHAR2(25) Email id of the employee
PHONE_NUMBER VARCHAR2(20) Y Phone number of the employee; includes country code and area code
HIRE_DATE DATE Date when the employee started on this job. A not null column.
JOB_ID VARCHAR2(10) Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.
SALARY NUMBER(8,2) Y Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)
COMMISSION_PCT NUMBER(2,2) Y Commission percentage of the employee; Only employees in sales
department elgible for commission percentage
MANAGER_ID NUMBER(6) Y Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)
DEPARTMENT_ID NUMBER(4) Y Department id where employee works; foreign key to department_id
column of the departments table
SQL> desc departments;
Name Type Nullable Default Comments
--------------- ------------ -------- ------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEPARTMENT_ID NUMBER(4) Primary key column of departments table.
DEPARTMENT_NAME VARCHAR2(30) A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting.
MANAGER_ID NUMBER(6) Y Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.
LOCATION_ID NUMBER(4) Y Location id where a department is located. Foreign key to location_id column of locations table.
俩表
那几个关键词用在俩表之间，就很简单了
> select from employees e left outer join departments d on条件;
连接e和d表
SQL> select e.last_name,d.department_name from employees e left outer join departments d on e.department_id = d.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Whalen Administration
Fay Marketing
Hartstein Marketing
Colmenares Purchasing
Himuro Purchasing
Tobias Purchasing
Baida Purchasing
Khoo Purchasing
Raphaely Purchasing
Mavris Human Resources
Grant Shipping
OConnell Shipping
Feeney Shipping
Walsh Shipping
Jones Shipping
McCain Shipping
Everett Shipping
Bell Shipping
Perkins Shipping
Gates Shipping
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Dilly Shipping
Chung Shipping
Cabrio Shipping
Dellinger Shipping
Bull Shipping
Sarchand Shipping
Geoni Shipping
Sullivan Shipping
Fleaur Shipping
Taylor Shipping
Vargas Shipping
Matos Shipping
Davies Shipping
Rajs Shipping
Patel Shipping
Seo Shipping
Stiles Shipping
Ladwig Shipping
Philtanker Shipping
Gee Shipping
Rogers Shipping
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Mallin Shipping
Olson Shipping
Marlow Shipping
Atkinson Shipping
Bissot Shipping
Markle Shipping
Landry Shipping
Mikkilineni Shipping
Nayer Shipping
Mourgos Shipping
Vollman Shipping
Kaufling Shipping
Fripp Shipping
Weiss Shipping
Lorentz IT
Pataballa IT
Austin IT
Ernst IT
Hunold IT
Baer Public Relations
Johnson Sales
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Livingston Sales
Taylor Sales
Hutton Sales
Abel Sales
Kumar Sales
Bates Sales
Smith Sales
Fox Sales
Bloom Sales
Ozer Sales
Banda Sales
Ande Sales
Lee Sales
Marvins Sales
Greene Sales
Vishney Sales
Sewall Sales
Doran Sales
Smith Sales
McEwen Sales
Sully Sales
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
King Sales
Tuvault Sales
Cambrault Sales
Olsen Sales
Hall Sales
Bernstein Sales
Tucker Sales
Zlotkey Sales
Cambrault Sales
Errazuriz Sales
Partners Sales
Russell Sales
De Haan Executive
Kochhar Executive
King Executive
Popp Finance
Urman Finance
Sciarra Finance
Chen Finance
Faviet Finance
Greenberg Finance
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Gietz Accounting
Higgins Accounting
Grant
107 rows selected
看见了吗？？？？
where被换成on了
where被换成on了
where被换成on了
这你不学怎么知道呢哈哈哈哈哈……
所以这个oracle还是得学习，否则你只会用where咋行呢
你瞅瞅，那个最后一个员工没有部门名称，也显示了，这个孤儿数据是e表中名字里面没有部门的数据
SQL> select last_name,department_id from employees where last_name='Grant';
LAST_NAME DEPARTMENT_ID
------------------------- -------------
Grant 50
Grant
是因为左表中它没有部门id，也查不到部门名称，所以就是孤儿数据
而要了左表这个数据，就成为左外连接left outer join
这个情况就是有很多部门，但是没有雇员
所以右边就有孤儿数据
你看看
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
27 rows selected
没有经理id的就是没有雇员，属于孤儿数据
然后右外连接
SQL> select e.last_name,d.department_name from employees e right outer join departments d on e.department_id = d.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Whalen Administration
Fay Marketing
Hartstein Marketing
Tobias Purchasing
Colmenares Purchasing
Baida Purchasing
Raphaely Purchasing
Khoo Purchasing
Himuro Purchasing
Mavris Human Resources
Feeney Shipping
Fleaur Shipping
Fripp Shipping
Gates Shipping
Gee Shipping
Geoni Shipping
Grant Shipping
Jones Shipping
Kaufling Shipping
Ladwig Shipping
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Everett Shipping
Dilly Shipping
Dellinger Shipping
Davies Shipping
Chung Shipping
Cabrio Shipping
Bull Shipping
Bissot Shipping
Bell Shipping
Atkinson Shipping
Landry Shipping
Weiss Shipping
Walsh Shipping
Vollman Shipping
Vargas Shipping
Taylor Shipping
Mallin Shipping
Markle Shipping
Marlow Shipping
Matos Shipping
McCain Shipping
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Mikkilineni Shipping
Mourgos Shipping
Nayer Shipping
OConnell Shipping
Olson Shipping
Patel Shipping
Perkins Shipping
Philtanker Shipping
Rajs Shipping
Rogers Shipping
Sarchand Shipping
Seo Shipping
Stiles Shipping
Sullivan Shipping
Austin IT
Pataballa IT
Ernst IT
Lorentz IT
Hunold IT
Baer Public Relations
Abel Sales
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Ande Sales
Banda Sales
Bates Sales
Bernstein Sales
Bloom Sales
Cambrault Sales
Cambrault Sales
Doran Sales
Errazuriz Sales
Fox Sales
Greene Sales
Hall Sales
Hutton Sales
Johnson Sales
King Sales
Kumar Sales
Lee Sales
Livingston Sales
Marvins Sales
McEwen Sales
Olsen Sales
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Ozer Sales
Partners Sales
Russell Sales
Sewall Sales
Smith Sales
Smith Sales
Sully Sales
Taylor Sales
Tucker Sales
Tuvault Sales
Vishney Sales
Zlotkey Sales
Kochhar Executive
King Executive
De Haan Executive
Popp Finance
Urman Finance
Chen Finance
Faviet Finance
Sciarra Finance
Greenberg Finance
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Gietz Accounting
Higgins 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
122 rows selected
这后面一堆有部门
但是没有雇员
那要是同时展示两边的孤儿数据，那就是full
SQL> select e.last_name,d.department_name from employees e full outer join departments d on e.department_id = d.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
King Executive
Kochhar Executive
De Haan Executive
Hunold IT
Ernst IT
Austin IT
Pataballa IT
Lorentz IT
Greenberg Finance
Faviet Finance
Chen Finance
Sciarra Finance
Urman Finance
Popp Finance
Raphaely Purchasing
Khoo Purchasing
Baida Purchasing
Tobias Purchasing
Himuro Purchasing
Colmenares Purchasing
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Weiss Shipping
Fripp Shipping
Kaufling Shipping
Vollman Shipping
Mourgos Shipping
Nayer Shipping
Mikkilineni Shipping
Landry Shipping
Markle Shipping
Bissot Shipping
Atkinson Shipping
Marlow Shipping
Olson Shipping
Mallin Shipping
Rogers Shipping
Gee Shipping
Philtanker Shipping
Ladwig Shipping
Stiles Shipping
Seo Shipping
Patel Shipping
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Rajs Shipping
Davies Shipping
Matos Shipping
Vargas Shipping
Russell Sales
Partners Sales
Errazuriz Sales
Cambrault Sales
Zlotkey Sales
Tucker Sales
Bernstein Sales
Hall Sales
Olsen Sales
Cambrault Sales
Tuvault Sales
King Sales
Sully Sales
McEwen Sales
Smith Sales
Doran Sales
Sewall Sales
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Vishney Sales
Greene Sales
Marvins Sales
Lee Sales
Ande Sales
Banda Sales
Ozer Sales
Bloom Sales
Fox Sales
Smith Sales
Bates Sales
Kumar Sales
Abel Sales
Hutton Sales
Taylor Sales
Livingston Sales
Grant
Johnson Sales
Taylor Shipping
Fleaur Shipping
Sullivan Shipping
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Geoni Shipping
Sarchand Shipping
Bull Shipping
Dellinger Shipping
Cabrio Shipping
Chung Shipping
Dilly Shipping
Gates Shipping
Perkins Shipping
Bell Shipping
Everett Shipping
McCain Shipping
Jones Shipping
Walsh Shipping
Feeney Shipping
OConnell Shipping
Grant Shipping
Whalen Administration
Hartstein Marketing
Fay Marketing
Mavris Human Resources
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Baer Public Relations
Higgins Accounting
Gietz Accounting
NOC
Manufacturing
Government Sales
IT Support
Benefits
Shareholder Services
Retail Sales
Control And Credit
Recruiting
Operations
Treasury
Payroll
Corporate Tax
Construction
Contracting
IT Helpdesk
123 rows selected
相当于是合并了left和right
外连接很重要！！！！
外连接很重要！！！！
外连接很重要！！！！
将来我们会说权限控制
比如
左表是用户，右边是资源访问权限的链接
1对多的关系
请你查询用户对应的有资源的数据
你会发现23用户没有资源，怎么查，那就需要外连接的左链接
也要查表2的右链接
后续我们慢慢搞，不急
oracle外连接扩展（+）
没有雇员的部门
显示右表的数据
SQL> select e.last_name,d.department_name from employees e,departments d where e.department_id = d.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Whalen Administration
Fay Marketing
Hartstein Marketing
Tobias Purchasing
Colmenares Purchasing
这个是查了所有的信息
有雇员的部门都在
但是没有雇员的部门呢？
需要链接第二个表
在等值连接条件的前面或者后面加？(+)
左侧加(+)表示不显示左侧的孤儿数据，而显示右侧的
右侧加(+)表示不显示右侧的孤儿数据，而显示左侧的
那这里就应该加在左侧，这样显示右侧的没有雇员的部门
一定把这个逻辑屡清楚
SQL> select e.last_name,d.department_name from employees e,departments d where e.department_id(+) = d.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Whalen Administration
Fay Marketing
Hartstein Marketing
Tobias Purchasing
Colmenares Purchasing
Baida Purchasing
Raphaely Purchasing
Khoo Purchasing
Himuro Purchasing
Mavris Human Resources
Feeney Shipping
Fleaur Shipping
Fripp Shipping
Gates Shipping
Gee Shipping
Geoni Shipping
Grant Shipping
Jones Shipping
Kaufling Shipping
Ladwig Shipping
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Everett Shipping
Dilly Shipping
Dellinger Shipping
Davies Shipping
Chung Shipping
Cabrio Shipping
Bull Shipping
Bissot Shipping
Bell Shipping
Atkinson Shipping
Landry Shipping
Weiss Shipping
Walsh Shipping
Vollman Shipping
Vargas Shipping
Taylor Shipping
Mallin Shipping
Markle Shipping
Marlow Shipping
Matos Shipping
McCain Shipping
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Mikkilineni Shipping
Mourgos Shipping
Nayer Shipping
OConnell Shipping
Olson Shipping
Patel Shipping
Perkins Shipping
Philtanker Shipping
Rajs Shipping
Rogers Shipping
Sarchand Shipping
Seo Shipping
Stiles Shipping
Sullivan Shipping
Austin IT
Pataballa IT
Ernst IT
Lorentz IT
Hunold IT
Baer Public Relations
Abel Sales
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Ande Sales
Banda Sales
Bates Sales
Bernstein Sales
Bloom Sales
Cambrault Sales
Cambrault Sales
Doran Sales
Errazuriz Sales
Fox Sales
Greene Sales
Hall Sales
Hutton Sales
Johnson Sales
King Sales
Kumar Sales
Lee Sales
Livingston Sales
Marvins Sales
McEwen Sales
Olsen Sales
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Ozer Sales
Partners Sales
Russell Sales
Sewall Sales
Smith Sales
Smith Sales
Sully Sales
Taylor Sales
Tucker Sales
Tuvault Sales
Vishney Sales
Zlotkey Sales
Kochhar Executive
King Executive
De Haan Executive
Popp Finance
Urman Finance
Chen Finance
Faviet Finance
Sciarra Finance
Greenberg Finance
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Gietz Accounting
Higgins 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
122 rows selected
看见了没
SQL> select e.last_name,d.department_name from employees e,departments d where e.department_id(+) = d.department_id;
在等号左侧那个部门条件后侧加（+）
则显示另一个
很骚啊
哈哈哈哈哈
没有部门的雇员
SQL> select e.last_name,d.department_name from employees e,departments d where e.department_id = d.department_id(+);
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Whalen Administration
Fay Marketing
Hartstein Marketing
Colmenares Purchasing
Himuro Purchasing
Tobias Purchasing
Baida Purchasing
Khoo Purchasing
Raphaely Purchasing
Mavris Human Resources
Grant Shipping
OConnell Shipping
Feeney Shipping
Walsh Shipping
Jones Shipping
McCain Shipping
Everett Shipping
Bell Shipping
Perkins Shipping
Gates Shipping
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Dilly Shipping
Chung Shipping
Cabrio Shipping
Dellinger Shipping
Bull Shipping
Sarchand Shipping
Geoni Shipping
Sullivan Shipping
Fleaur Shipping
Taylor Shipping
Vargas Shipping
Matos Shipping
Davies Shipping
Rajs Shipping
Patel Shipping
Seo Shipping
Stiles Shipping
Ladwig Shipping
Philtanker Shipping
Gee Shipping
Rogers Shipping
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Mallin Shipping
Olson Shipping
Marlow Shipping
Atkinson Shipping
Bissot Shipping
Markle Shipping
Landry Shipping
Mikkilineni Shipping
Nayer Shipping
Mourgos Shipping
Vollman Shipping
Kaufling Shipping
Fripp Shipping
Weiss Shipping
Lorentz IT
Pataballa IT
Austin IT
Ernst IT
Hunold IT
Baer Public Relations
Johnson Sales
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Livingston Sales
Taylor Sales
Hutton Sales
Abel Sales
Kumar Sales
Bates Sales
Smith Sales
Fox Sales
Bloom Sales
Ozer Sales
Banda Sales
Ande Sales
Lee Sales
Marvins Sales
Greene Sales
Vishney Sales
Sewall Sales
Doran Sales
Smith Sales
McEwen Sales
Sully Sales
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
King Sales
Tuvault Sales
Cambrault Sales
Olsen Sales
Hall Sales
Bernstein Sales
Tucker Sales
Zlotkey Sales
Cambrault Sales
Errazuriz Sales
Partners Sales
Russell Sales
De Haan Executive
Kochhar Executive
King Executive
Popp Finance
Urman Finance
Sciarra Finance
Chen Finance
Faviet Finance
Greenberg Finance
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Gietz Accounting
Higgins Accounting
Grant
107 rows selected
两个都显示呢？？？
难道就是两个都加呗！
错
SQL> select e.last_name,d.department_name from employees e,departments d where e.department_id(+) = d.department_id(+);
select e.last_name,d.department_name from employees e,departments d where e.department_id(+) = d.department_id(+)
ORA-01468: 一个谓词只能引用一个外部联接的表
既然是压制本侧，显示另一个侧
那你不能俩都加
否则gg
哈哈哈哈哈
这里就是where，但不是on
要区分清楚哦
（+）代表菜单收起来
和文件夹显示的类似
懂吧？？？
为毛这个+用在这里了
很好记忆
总结
提示：重要经验：
1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
731
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
