SQL连接(内连接,左连接,右连接,自连接)详解

SQL连接(内连接,左连接,右连接,自连接)详解

博客分类：
• SQL

SQLOracle

create table department( id number primary key, name varchar2(30));

create table employee(  id number primary key,  name varchar2(30) not null,  salary number(6,2),  title varchar2(30),  manager number,  deptid number);

insert into department values(1,'人力资源');
insert into department values(2,'开发中心');
insert into department values(3,'总裁室');

insert into employee values(4,'赵六',9000,'项目经理',null,2);
insert into employee values(3,'王五',7000,'项目组长',4,2);
insert into employee values(1,'张三',6000,'程序员',3,2);
insert into employee values(2,'李四',7000,'HR',4,1);

不同的 SQL JOIN
    * JOIN: 如果表中有至少一个匹配，则返回行
    * LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
    * RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
    * FULL JOIN: 只要其中一个表中存在匹配，就返回行

INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "employee" 中的行在 "department" 中没有匹配，就不会列出这些行。

LEFT JOIN 关键字会从左表 (employee) 那里返回所有的行，即使在右表 (department) 中没有匹配的行。

RIGHT JOIN 关键字会从右表 (department) 那里返回所有的行，即使在左表 (employee) 中没有匹配的行。

FULL JOIN 关键字会从左表 (employee) 和右表 (department) 那里返回所有的行。如果 "employee" 中的行在表 "department" 中没有匹配，或者如果 "department" 中的行在表 "employee" 中没有匹配，这些行同样会列出。

注释：INNER JOIN 与 JOIN 是相同的。
注释：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。
注释：在某些数据库中， RIGHT JOIN 称为 RIGHT OUTER JOIN。
注释：在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN。


内连接:是最常用的链接,也叫等值链接
不管限制条件在on中还是在where中，内连接都得到笛卡尔积中满足所有条件的记录。对于inner join, on和where的效果是一样的。

1)当没有限制条件,此时为笛卡尔积：

select * from employee , department ;
select * from employee inner join department on 1=1;

得到的效果是一样的.



2)此时如果加上where限制条件,则对上面笛卡尔积的每一条记录看它是否满足限制条件，如果满足，则它在结果集中。

select * from employee emp , department dept where emp.deptid= dept.id;
select * from employee emp inner join department dept on emp.deptid= dept.id;

外连接
  则分为"左外连接(左连接)","右外连接(右连接)"两种情况: left join / right join

交叉连接（完全连接）cross join 不带 where 条件的

左连接(left join):
1)当没加限制条件时,得到笛卡尔积，同内连接没加限制条件一样
 

select * from employee left join department on 1=1;

2)当添加特定条件时:
  oracle数据库中可以用(+)来实现, 

select * from employee emp , department dept where emp.deptid= dept.id(+);

即“(+)”所在位置的另一侧为连接的方向，左连接说明等号左侧的所有记录均会被显示，无论其在右侧是否得到匹配;
等同于:

select * from employee emp left join department dept on emp.deptid= dept.id;

右连接:
  oracle数据库中可以用(+)来实现, 

select * from employee emp , department dept where emp.deptid(+)= dept.id;

即“(+)”所在位置的另一侧为连接的方向，右连接说明等号右侧的所有记录均会被显示，无论其在左侧是否得到匹配;
等同于:

select * from employee emp right join department dept on emp.deptid= dept.id;

全连接:会从左表 (employee) 和右表 (department) 那里返回所有的行。如果 "employee" 中的行在表 "department" 中没有匹配，或者如果 "department" 中的行在表 "employee" 中没有匹配，这些行同样会列出
 

连接分为三种：内连接、外连接、交叉连接  
 
1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

3、自然连接(仅仅是一种特的等值连接)：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
      
外连接：  
分为三种：左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种  
 
交叉连接(CROSS JOIN)   
没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积 
先返回 左表所有行，左表行在与右表行一一组合，等于两个表相乘

select * from employee emp cross join department dept

如果需要加条件,则使用where

select * from employee emp cross join department dept where emp.deptid= dept.id;
--等同于
select * from employee emp inner join department dept on emp.deptid= dept.id;

两个主要的连接类型是内连 接和外连 接。到目前为止，所有示例中使用的都是内连 接。内连 接只保留交叉积中满足连接条件的那些行。如果某行在一个表中存在，但在另一个表中不存在，则结果表中不包括该信息。
外连 接是内连 接和左表和/或右表中未包括内连 接中的那些行的并置。当对两个表执行外连 接时，可任意将一个表指定为左表而将另一个表指定为右表。外连 接有三种类型:

左外连 接包括内连 接和左表中未包括在内连 接中的那些行。

右外连 接包括内连 接和右表中未包括在内连 接中的那些行。

全外连 接包括内连 接以及左表和右表中未包括在内连 接中的行。
内连 接一般是检索两个表里连接字段都存在的数据。
左连接的意思是，查询左（语句前面）表里的所有内容，无论右边表里有没有。右边表里没有的内容用NULL代替。
右连接和左连接相反。

数据表的连接有:
1、内连 接(自然连接): 只有两个表相匹配的行才能在结果集中出现
2、外连 接: 包括
（1）左外连 接(左边的表不加限制)
（2）右外连 接(右边的表不加限制)
（3）全外连 接(左右两表都不加限制)
3、自连接(连接发生在一张基表内)