内联，左外联，右外联，全连接，交叉连接 的区别

连接分为：内连接、外连接、交叉连接
一、内连接——最常用
定义：仅将两个表中满足连接条件的行组合起来作为结果集。
在内连接中，只有在两个表中匹配的行才能在结果集中出现
关键词：INNER JOIN
格式：SELECT 列名表 FROM 表名1 [INNER] JOIN 表名2 ON或WHERE 条件表达式
说明：
（1）列名表中的列名可以出自后面的两个表，但如果两个表中有同名列，应在列名前标明出处，格式为：表名.列名
（2）若连接的两个表名字太长，可以为它们起个别名。 格式为：表名 AS 别名
（3）INNER是默认方式，可以省略

eg:

select *
from   t_institution i
inner join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = "5801"
其中inner可以省略。
等价于早期的连接语法
select *
from t_institution i, t_teller t
where i.inst_no = t.inst_no
and i.inst_no = "5801"

二、外连接
1、左（外）连接
定义：在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL
关键字：LEFT JOIN

eg:

select *
from   t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
其中outer可以省略。

注意:

当在内连接查询中加入条件是，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况就不同了。当把条件加入到 join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。如果将条件放到where子句 中，SQL Server将会首先进行连接操作，然后使用where子句对连接后的行进行筛选。下面的两个查询展示了条件放置位子对执行结果的影响：
条件在join子句
select *
from   t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
and i.inst_no = “5801”
结果是：
inst_no     inst_name             inst_no     teller_no   teller_name
5801        天河区                5801        0001        tom
5801        天河区                5801        0002        david
5802        越秀区
5803        白云区
条件在where子句
select *
from   t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = “5801”
结果是：
inst_no     inst_name             inst_no     teller_no   teller_name
5801           天河区                    5801          0001               tom
5801           天河区                    5801          0002              david

2、右（外）连接
定义：在内连接的基础上，还包含右表中所有不符合条件的数据行，并在其中的左表列填写NULL
关键字：RIGHT JOIN
3、完全连接
定义：在内连接的基础上，还包含两个表中所有不符合条件的数据行，并在其中的左表、和右表列填写NULL
关键字：FULL JOIN
三、交叉连接
定义：将两个表的所有行进行组合，连接后的行数为两个表的乘积数。（笛卡尔积）
关键词：CROSS JOIN
格式：FROM 表名1 CROSS JOIN 表名2

四, 自身连接
自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据。例如人力资源数据库中雇员与老板的关系。
下面例子是在机构表中查找本机构和上级机构的信息。
select s.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_name
from t_institution i
join t_institution s
on i.superior_inst = s.inst_no

结果是：
superior_inst sup_inst_name         inst_no     inst_name
800                             广州市                5801        天河区
800                             广州市                5802        越秀区
800                             广州市                5803        白云区

 

 

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接) 只返回两个表中联结字段相等的行

 

 

 

举例如下：

--------------------------------------------

表A记录如下：

aID　　　　　aNum

1　　　　　a20050111

2　　　　　a20050112

3　　　　　a20050113

4　　　　　a20050114

5　　　　　a20050115

表B记录如下:

bID　　　　　bName

1　　　　　2006032401

2　　　　　2006032402

3　　　　　2006032403

4　　　　　2006032404

8　　　　　2006032408

--------------------------------------------

1.left join

sql语句如下:

select * from A

left join B

on A.aID = B.bID

结果如下:

aID　　　　　aNum　　　　　bID　　　　　bName

1　　　　　a20050111　　　　1　　　　　2006032401

2　　　　　a20050112　　　　2　　　　　2006032402

3　　　　　a20050113　　　　3　　　　　2006032403

4　　　　　a20050114　　　　4　　　　　2006032404

5　　　　　a20050115　　　　NULL　　　　　NULL

（所影响的行数为 5 行）

结果说明:

left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.

换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).

B表记录不足的地方均为NULL.

--------------------------------------------

2.right join

sql语句如下:

select * from A

right join B

on A.aID = B.bID

结果如下:

aID　　　　　aNum　　　　　bID　　　　　bName

1　　　　　a20050111　　　　1　　　　　2006032401

2　　　　　a20050112　　　　2　　　　　2006032402

3　　　　　a20050113　　　　3　　　　　2006032403

4　　　　　a20050114　　　　4　　　　　2006032404

NULL　　　　　NULL　　　　　8　　　　　2006032408

（所影响的行数为 5 行）

结果说明:

仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

--------------------------------------------

3.inner join

sql语句如下:

select * from A

innerjoin B

on A.aID = B.bID

结果如下:

aID　　　　　aNum　　　　　bID　　　　　bName

1　　　　　a20050111　　　　1　　　　　2006032401

2　　　　　a20050112　　　　2　　　　　2006032402

3　　　　　a20050113　　　　3　　　　　2006032403

4　　　　　a20050114　　　　4　　　　　2006032404

结果说明:

很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.

--------------------------------------------

注:

LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。

语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2

说明：table1, table2参数用于指定要将记录组合的表的名称。

field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。

compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。

如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误.

 

@时间漩涡
用上面提到的A、B表为例：
select * from A
left join
(
select bID,bName from B where bID <= 3
)T
on T.bID = A.aID