Oracle数据库:oracle外连接left/right/full outer join on,oracle扩展的左右外连接展示符号(+)

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,可以不考虑空间复杂度,但是面试既要考虑时间复杂度最优,也要考虑空间复杂度最优。

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

冰露可乐

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值