连接方式

  • 须知
  • 内连接
  • 等值连接
  • ON条件与WHERE条件的区别
  • 自然连接
  • 不等值连接
  • 外连接
  • 左连接与右连接
  • 左连接
  • 左外连接
  • 右连接
  • 右外连接
  • 全外连接


须知

要更好地理解连接查询，需了解笛卡尔积的概念。
提高查询效率的关键在于消除笛卡尔积。

内连接

两个表（或连接）中某一数据项相等的连接称为内连接。连接的结果是形成一个新的数据表。可以理解为取左连接与右连接的交集，即两边都满足匹配条件时取出数据。

select *from Student INNER JOIN Score ON Score.s_id=Student.s_id

等值连接

(where子句中使用=等号为限定条件)
在连接条件中使用‘’=‘’运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。连接条件是一个表中的一个字段必须等于另一个表中的一个字段。

select * from stu t1 INNER JOIN stu_depart t2 ON t1.deptno= t2.deptno WHERE t2.deptno =t2.deptno;
select * from stu t1,t_depart t2 WHERE t2.deptno =t2.deptno;

ON条件与WHERE条件的区别

  1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
  2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
  3. on条件先对次表进行筛选过滤，然后将过滤的结果和主表进行匹配关联（这种匹配也是基于on条件的匹配，比如tab1.size = tab2.size）。而on后面的where是对条件关联后的临时表从整体上进行条件筛选。

自然连接

自然连接也是一种等值连接，在开发中使用较少，可以理解为去掉重复列的等值连接。不需要写on条件。自然连接一定是等值连接，但等值连接不一定是自然连接。
需要两个关系表中进行比较的属性列名字与属性（类型）相同，不需要写连接条件，并结果中不会出现重复的属性列，从而自然连接以进行多表查询。如果两个关系中有多组这样的属性，默认是全部比较的。

//创建表A，插入两行数据，包含姓名信息
create table A(
aid number(9) primary key,
aname varchar2(90)
);
insert into A values(1,'John');
insert into A values(2,'Mary');
//创建表B，插入两行数据，包含性别信息
create table B(
aid number(9) primary key,
asex varchar2(90)
);
insert into B values(1,'male');
insert into B values(2,'female');
//将表A与表B自然连接，得到查询结果为姓名、性别，
select aname,asex from A NATURAL JOIN B;

不等值连接

在连接的条件中可以使用小于（<）、大于（>）、不等于（<>）等运算符，而且还可以使用LIKE、BETWEEN AND等运算符，甚至还可以使用函数。
以下为一个示例，表名为emp，包含name，job，grade；
根据salgrade表中最高工资与最低工资之间的范围进行查询

select name,job,grade from emp e,salgrade grade where e.sal between grade.losal and grade.hisal;

外连接

左连接与右连接

student(左表）

id

name

sex

college_id

1

a

m

1

2

b

f

1

3

c

m

2

4

d

null

college（右表）

college_id

college_name

1

AU

2

BU

3

CU

等值连接时：

id

name

sex

college_id

college_name

1

a

m

1

AU

2

b

f

1

AU

3

c

m

2

BU

左外连接时：

id

name

sex

college_id

college_name

1

a

m

1

AU

2

b

f

1

AU

3

c

m

2

BU

4

d

null

左连接

A不改变，B表匹配A表。
以左表为主表列出所有数据，右表如匹配无数据为null。
将返回右表的所有行。如果左表的某行在右表中没有匹配行，则将为右表返回空值左连接：

select *from Student  LEFT JOIN college ON Student.s_id=Score.s_id
左外连接
select *from Student LEFT OUTER JOIN college ON Student.s_id=Score.s_id

右连接

B不改变，A表匹配B表。返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值;
以右表为主表，左表中没数据的为null

select *from Student RIGHT JOIN college ON Student.s_id=Score.s_id
右外连接
select *from Student RIGHT OUTER JOIN college ON Student.s_id=Score.s_id

全外连接

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

select *from Student FULL  JOIN Score ON Student.s_id=Score.s_id

select *from Student FULL OUTER JOIN Score ON Student.s_id=Score.s_id