1、概述
连接是(JOIN) 是根据两个或者多个表之间的列建立关系, 获取所需要的数据,在Oracle数据库中,提供了自连接也称内连接(inner join或者join),自然连接(natural join),左连接(left join或者left outer join ),右连接(right join或者 right outer join),全连接(full join或者 full outer join)以及笛卡尔积(cross join)
基本语法:select table1.column,table2.column from table1 [inner | left | right | full ] join table2 on table1.column1 = table2.column2;
为了更好的再后面阐述:我们首先建立了stu以及class两张表如下:
STU表如下:
create table stu( stu_id varchar2( 10), name varchar2(10 ), class_id varchar( 10) );
STU表中数据为:
CLASS创建如下:
create table class( class_id varchar2( 10), class_name varchar2( 10) );
数据有:
2、Oracle连接
2.1、内连接(inner jion)
Oracle 的join连接默认就是inner join,所以在写内连接时可以把inner省略, 这种连接返回的是两表交集的部分,即如果表中至少有一行匹配则但会行。可以用下面蓝色部分表示:
我们用实例来说明什么是内连接,我们可以在表stu和表class 通过class_id建立连接如下,下面三种方式是等价的:
select s.stu_id,s.name,s.class_id,c.class_name from stu s, class c where s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s inner join class c on s.class_id =c.class_id order by stu_id ;
我们可以看到结果返回的是两表中class_id相同的那些记录:
用inner jion中我们是根据两表中的class_id字段来匹配,还有一种连接可以根据量表自动去匹配相同的字段,并返回数据行,这种连接称作 自然连接(natural join),我们可以把它看成是内连接的一种,用法如下:
select stu_id,name ,class_id,c.class_name from stu s natural join class c
自然连接需要注意一下亮点:
1、如果自然连接的多个字段的名称和类型都匹配,那么他们都会作为自然连接的连接条件;
2、若自然连接的连个表仅字段相同,但是类型不同将会返回一个错误。
2.2、外连接
在Oracle中外连接主要有 左外连接、右外连接以及全连接三种
2.2.1、左外连接(left join或者left outer join)
左外连接:返回的记录行数与左表相同,即使右表中没有匹配行,也从左表中返回所有行;如下图所示:
在oracle中,我们可以建立左连接如下,以下这三种方式建立的左连接时等价的:
select s.stu_id,s.name,s.class_id,c.class_name from stu s left join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s left outer join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s ,class c where s.class_id =c.class_id(+) order by stu_id;
返回记录行如下,从返回结果中可以看出左连接返回了stu的所有行,如class表中无数据的字段则为null
说明:
1、使用(+)建立左连接时,只能在where 条件中使用,且(+)要放在右表后
2、当使用(+)建立左连接时,如果where 有多个条件那么所有的条件都要加上(+)
3、(+)只使用列,不适合用在表达式上
4、(+)不能和in、not in 以及 or 操作符一起使用
5、(+)只适用左连接和右连接,不能实现全连接, 但是可以通过 左连接 UNION 右连接实现。
2.2.2、右连接(right join或者right outer join)
右连接会返回右表中的所有行,即使左表中没有匹配行,也返回右表的所有行,如下图所示:
类似左连接,在oracle中实现右连接有一下几种方式,它们亦是等价的:
select s.stu_id,s.name,s.class_id,c.class_name from stu s right join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s right outer join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s , class c where s.class_id(+) =c.class_id order by stu_id ;
返回的记录行是如下,返回结果中可以看出左连接返回了class的所有行,如stu表中无数据的字段则为null
说明:当用(+)表示右连接时,说明基本和左连接一样,只是(+)需要用在左表的字段上。
2.2.3、全连接(full join 或者full outer join)
全连接:返回的是两表的全集,无论匹配不匹配都返回行,如下图所示:
在oracle实现全连接的方式如下,下面这两种方式是等价的:
select s.stu_id,s.name,s.class_id,c.class_name from stu s full join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s full outer join class c on s.class_id =c.class_id order by stu_id ;
返回的记录行如下,两表中没匹配且在其中一张表无数据,返回null
说明:全连接不能用(+)表示,但是可以用左连接 UNION 右连接表示,注意:使用的不是UNION ALL(请参考UNION 与UNION的区别)
select s.stu_id,s.name,s.class_id,c.class_name from stu s , class c where s.class_id =c.class_id(+) union select s.stu_id,s.name,s.class_id,c.class_name from stu s , class c where s.class_id(+) =c.class_id order by stu_id ;
2.3、笛卡尔积(cross join)
笛卡尔积 :返回的是两表的乘积,返回的行数为两表各自行数的乘积,使用方法如下:
select s.stu_id,s.name,s.class_id,c.class_name from stu s cross join class c
可以得到返回的记录为:
补充:
还有一种oracle并没有关键字的连接,用使用等值以外的条件作为连接条件,可以表示如下:
select stu_id,name ,c.class_name from stu s , class c where s.class_id !=c.class_id
3、总结
可以把以上各个连接用一张图表示(这张图是引用别人的博客,具体博客地址没找到,对原作者十分抱歉)
把之前的sql也整理如下:
--jion inner join select s.stu_id,s.name,s.class_id,c.class_name from stu s,class c where s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s inner join class c on s.class_id =c.class_id order by stu_id ; -- left jion select s.stu_id,s.name,s.class_id,c.class_name from stu s left join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s left outer join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s ,class c where s.class_id =c.class_id(+) order by stu_id ; --right jion select s.stu_id,s.name,s.class_id,c.class_name from stu s right join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s right outer join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s , class c where s.class_id(+) =c.class_id order by stu_id ; --full jion select s.stu_id,s.name,s.class_id,c.class_name from stu s full join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s full outer join class c on s.class_id =c.class_id order by stu_id ; select s.stu_id,s.name,s.class_id,c.class_name from stu s , class c where s.class_id =c.class_id(+) union select s.stu_id,s.name,s.class_id,c.class_name from stu s , class c where s.class_id(+) =c.class_id order by stu_id ; --cross jion select s.stu_id,s.name,s.class_id,c.class_name from stu s cross join class c --natural join select stu_id,name ,class_id,c.class_name from stu s natural join class c --补 select stu_id,name ,c.class_name from stu s , class c where s.class_id !=c.class_id
相关推荐
ORACLE的优化器共有3种 ... 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器
缺省情况下是inner join,开发中使用的left join和right join属于outer join,另外outer join还包括full join.下面我通过图标让大家认识它们的区别。 现有两张表,Table A 是左边的表。Table B 是右边的表。其各有四条...
连接无非是这几个 --内连接和where相同 inner join ... left join --右向外连接, right join --完整外部连接, full join --交叉连接,也称笛卡儿积。 cross join .......
sql练习中易错笔记,order by ,group by,distinct,内连接,外连接等,一些mysql和oracle使用的区别:sql4种连接 两个表中有相同的列,根据共有的列值匹配行 left join左外连接,不管左表是否在右表中有匹配行,都...
[LEFT | RIGHT | FULL OUTER JOIN 表名称2]; 1. 交叉连接:CROSS JOIN,主要功能是产生笛卡儿积,简单实现多表查询; SELECT * FROM emp CROSS JOIN dept; 2. 自然连接:NATURAL JOIN,自动使用关联字段消除...
外连接: 全连接(full join)、左连接(left join)、右连接(right join)。 交叉联接(cross join)。 外连接与内连接不一样,外连接返回的查询结果中不仅包含符合条件的行,还包括左表(左外连接),右表(右外连接)...
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今五十年前。简单来说是本身可视...
自连接是一种在单一标准化表格上使用的JOIN声明,实质上,你可以使用自连接在一个表格内获得行与行之间的等级关系。你必须使用别名作为表格的两个实例来描述表格,然后将其连接起来。 使用自连接的实例可以体现在列...
内连接(inner join)和外连接(left join/right join/full join) 排序(Order By) 条件(Where) 分组(Group By) 分组条件(Having) 计算字段 SQL查询表 SQL查询子句 丰富的函数 表别名 字段别名 联合(Union...
3.7.1 SQL 92 标准:Inner Join、Left Join、Right Join、Full Join, 3.7.2 特殊语法:*=、=*、*=*(MS_SQL,Sybase),(+)(Oracle) 3.8 联合 (Union [All],Minus,Intersect) 3.9 字段别名,数据表别名 ...
对于外连接,Oracle中可以使用“(+)”来表示,9i可以使用LEFT/RIGHT/FULL OUTER JOIN,下面将配合实例一一介绍。
这是我个人通过长时间的实践与收集最后总结出来的基本关系表的连接操作实际应用,以及在某些情况下可以用普通的sql语句代替某些连接操作(即实现同样的功能)其中文件夹中已经包含了测试所需数据库,这是备份形式需...
3.7.1 SQL 92 标准:Inner Join、Left Join、Right Join、Full Join, 3.7.2 特殊语法:*=、=*、*=*(MS_SQL,Sybase),(+)(Oracle) 3.8 联合 (Union [All],Minus,Intersect) 3.9 字段别名,数据表别名 ...
实例参考如下: SQL的Join语法有很多, inner join(等值连接) 只返回两个表中联结字段相等的⾏, left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录, right join(右联接) 返回包括右表中的...
B:right outer join: 右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 C:full outer join: 全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 其次...
1. ASP与Access数据库连接: dim conn,mdbfile mdbfile=server.mappath("数据库名称.mdb") set conn=server.createobject("adodb.connection") conn.open "driver={microsoft access driver (*.mdb)};uid=admin;...
B:right (outer) join: 右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 C:full/cross (outer) join: 全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录...
B:right (outer) join: 右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 C:full/cross (outer) join: 全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录...
B:right (outer) join: 右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 C:full/cross (outer) join: 全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录...
1.6.4 全外连接(full outer join) 1.6.4.1 说明 完整外部联接返回左表和右表中的所有行;当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值;如果表之间有匹配行,则整个结果集行包含基表的数据值...