Oracle数据库：自然连接natural join using语句注意避免写交叉连接

Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接

找工作是学历、能力和运气的超强结合体，遇到寒冬，大厂不招人，可能很多算法学生都得去找开发，测开

测开的话，你就得学数据库，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语句和规则

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

文章目录

Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接@[TOC](文章目录)oracle交叉连接：应该避免写这个东西oracle自然连接natural join是等值连接的另一种写法oracle using子句总结

oracle交叉连接：应该避免写这个东西

直接炸锅了卧槽

一定要避免这么写，炸了………………

select * from employees cross join departments;2889 rows selected

切忌这么写

炸锅

oracle自然连接natural join是等值连接的另一种写法

列名和数据类型

必须都一样

多个字段相等，那就且关系

所以呢，你建立表的时候，就要考虑这个问题

natural join的本质是等值连接的简化

性能一样

懂吧

最好是等值连接

这个一查就是所有列的等

departments表

locations表

SQL> desc locations;Name Type Nullable Default Comments-------------- ------------ -------- ------- ----------------------------------------------------------------------------------------------------------------------------------------- LOCATION_ID NUMBER(4) Primary key of locations table STREET_ADDRESS VARCHAR2(40) YStreet address of an office, warehouse, or production site of a company.Contains building number and street namePOSTAL_CODE VARCHAR2(12) YPostal code of the location of an office, warehouse, or production siteof a company. CITY VARCHAR2(30) A not null column that shows city where an office, warehouse, orproduction site of a company is located. STATE_PROVINCE VARCHAR2(25) YState or Province where an office, warehouse, or production site of acompany is located.COUNTRY_IDCHAR(2)YCountry where an office, warehouse, or production site of a company islocated. Foreign key to country_id column of the countries 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, PublicRelations, Sales, Finance, and Accounting. MANAGER_IDNUMBER(6) YManager_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_IDNUMBER(4) YLocation id where a department is located. Foreign key to location_id column of locations table.

试一下

SQL> select d.department_id,d.department_name,l.city from departments d,locations l where d.location_id = l.location_id;DEPARTMENT_ID DEPARTMENT_NAMECITY------------- ------------------------------ ------------------------------60 IT Southlake50 Shipping South San Francisco10 Administration Seattle30 Purchasing Seattle90 Executive Seattle100 Finance Seattle110 Accounting Seattle120 Treasury Seattle130 Corporate Tax Seattle140 Control And Credit Seattle150 Shareholder Services Seattle160 Benefits Seattle170 Manufacturing Seattle180 Construction Seattle190 ContractingSeattle200 Operations Seattle210 IT Support Seattle220 NOC Seattle230 IT HelpdeskSeattle240 Government SalesSeattleDEPARTMENT_ID DEPARTMENT_NAMECITY------------- ------------------------------ ------------------------------250 Retail Sales Seattle260 Recruiting Seattle270 Payroll Seattle20 Marketing Toronto40 Human ResourcesLondon80 SalesOxford70 Public RelationsMunich27 rows selected

这是等值连接的标准格式

简化一波

SQL> select d.department_id,d.department_name,l.city from departments d natural join locations l;DEPARTMENT_ID DEPARTMENT_NAMECITY------------- ------------------------------ ------------------------------60 IT Southlake50 Shipping South San Francisco10 Administration Seattle30 Purchasing Seattle90 Executive Seattle100 Finance Seattle110 Accounting Seattle120 Treasury Seattle130 Corporate Tax Seattle140 Control And Credit Seattle150 Shareholder Services Seattle160 Benefits Seattle170 Manufacturing Seattle180 Construction Seattle190 ContractingSeattle200 Operations Seattle210 IT Support Seattle220 NOC Seattle230 IT HelpdeskSeattle240 Government SalesSeattleDEPARTMENT_ID DEPARTMENT_NAMECITY------------- ------------------------------ ------------------------------250 Retail Sales Seattle260 Recruiting Seattle270 Payroll Seattle20 Marketing Toronto40 Human ResourcesLondon80 SalesOxford70 Public RelationsMunich27 rows selected

natural join

的用法就如此

联合俩表相等的列

相当于直接用location_id相同

那通过部门的location_id，去查city信息

所以设计表格的时候，你要设计好

否则就没法用

oracle using子句

using子句

左右表都有id，x列都相同

natural join就会做判断，id和x都得相同

如果用using id

那只看id，不看x

而且你不能用表的别名玩，这个是注意的地方

natural join和using子句是相互排斥的

指定using

不妨设别的可能还相同

那就要限制

行选城市id是1800的

SQL> select d.department_name,l.city from departments d join locations l using(location_id) where location_id=1800;DEPARTMENT_NAMECITY------------------------------ ------------------------------Marketing Toronto

natural不能要了哦

另外using加括号（）

然后限定行放屁股

只限定location_id链接，这就是using语句的好处

和natural冲突的

另外你要用location_id，还不表名或者别名了

不能限定了

你看

SQL> select d.department_name,l.city from departments d join locations l using(location_id) where location_id=1800;DEPARTMENT_NAMECITY------------------------------ ------------------------------Marketing TorontoSQL> select d.department_name,l.city from departments d join locations l using(location_id) where locations.location_id=1800;select d.department_name,l.city from departments d join locations l using(location_id) where locations.location_id=1800ORA-00904: "LOCATIONS"."LOCATION_ID": 标识符无效

懂了吧

using，特定列，直接用列名玩