文章目录

  • 1. 内连接
  • 1.1 显式的和隐式的
  • 1.1.1 显示的
  • 1.1.2 隐式的
  • 2. 外连接
  • 2.1 左(外)连接 Left outer join
  • 2.2 右(外)连接 right outer join
  • 2.3 全外连接 Full outer join
  • 3. 交叉连接(迪卡尔积)
  • 3.1 笛卡尔积
  • 3.2 交叉连接实例
  • 3.3 交叉连接的作用


连接分为：内连接、外连接、交叉连接

1. 内连接

内连接：也称为等值连接，返回两张表都满足条件的部分

doc连接mysql数据库 mysql数据库内连接_图示


关键词：INNER JOIN

格式：

显示的：SELECT 列名表 FROM 表名1 [INNER] JOIN 表名2 ON 条件表达式

隐式的：SELECT 列名表 FROM 表名1 ，表名2 WHERE 条件表达式
说明：
（1）列名表中的列名可以出自后面的两个表，但如果两个表中有同名列，应在列名前标明出处，格式为：表名.列名
（2）若连接的两个表名字太长，可以为它们起个别名。 格式为：表名 AS 别名
（3）INNER是默认方式，可以省略

1.1 显式的和隐式的

内连接有两种，显式的和隐式的

1.1.1 显示的

显示的内连接，一般称为内连接，有inner join，形成的中间表为两个表经过on条件过滤后的笛卡尔积。

select o.id,o.order_number,c.id,c.name from customers c inner join orders o on c.id=o.customer_id;

1.1.2 隐式的

隐式的内连接，没有inner join，形成的中间表为两个表的笛卡尔积。

select o.id,o.order_number,c.id,c.name from customers c, orders o wherec.id=o.customer_id;

2. 外连接

外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。

外连接分三类：左外连接（left outer join）、右外连接（right outer join）和全外连接（full outer join）。

outer 关键字是可省略的。

2.1 左(外)连接 Left outer join

定义：在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，排除在右表 (table_name2) 中没有匹配的行。

doc连接mysql数据库 mysql数据库内连接_doc连接mysql数据库_02

语法结构：

SELECT * FROM TableA
 LEFT OUTER JOIN TableB
 ON TableA.name = TableB.name

例：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

doc连接mysql数据库 mysql数据库内连接_doc连接mysql数据库_03


结果集：

doc连接mysql数据库 mysql数据库内连接_图示_04


过程分析：

doc连接mysql数据库 mysql数据库内连接_交叉连接_05


从左边表逐条和右边进行查找，如果没有匹配到，仍然把左侧的放入结果，此时右侧表字段用null代替

2.2 右(外)连接 right outer join

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

即整个黄色区域：

doc连接mysql数据库 mysql数据库内连接_doc连接mysql数据库_06


从右边表逐条和右边进行查找，如果没有匹配到，仍然把右侧的放入结果，此时左侧表字段用null代替

语法结构：

SELECT * FROM TableA
 RIGHT OUTER JOIN TableB
 ON TableA.name = TableB.name

例：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

doc连接mysql数据库 mysql数据库内连接_左连接_07


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

2.3 全外连接 Full outer join

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

doc连接mysql数据库 mysql数据库内连接_左连接_08

SELECT * FROM TableA
 FULL OUTER JOIN TableB
 ON TableA.name = TableB.name

例：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

doc连接mysql数据库 mysql数据库内连接_图示_09


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

3. 交叉连接(迪卡尔积)

交叉连接：返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积

3.1 笛卡尔积

首先，先简单解释一下笛卡尔积：笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

举例：

现在，我们有两个集合A和B。

A = {0,1} B = {2,3,4}

集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：

A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；

B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；

以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。

从以上的数据分析我们可以得出以下两点结论：

1，两个集合相乘，不满足交换率，既 A×B ≠ B×A;

2，A集合和B集合相乘，包含了集合A中元素和集合B中元素相结合的所有的可能性。既两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数;

3.2 交叉连接实例

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into `student` (`id`, `name`) values('1','zhangsan');
insert into `student` (`id`, `name`) values('2','lisi');


CREATE TABLE `course` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into `course` (`id`, `name`) values('1','语文');
insert into `course` (`id`, `name`) values('2','英语');

交叉连接分为隐式和显示：

  • 隐式的交叉连接，没有CROSS JOIN。
SELECT s.name ,c.name FROM student AS s,course AS c
  • 显式的交叉连接，使用CROSS JOIN。
SELECT s.name ,c.name FROM student AS s CROSS JOIN course AS c

不管显示还是隐式的，其结果一致：

doc连接mysql数据库 mysql数据库内连接_右连接_10

3.3 交叉连接的作用

作用1：作为笛卡尔积显示，正如3.2 交叉连接实例那样
作用2：交叉连接可以作为连接查询的临时表，再此基础上，如果有where 条件，再进行where过滤

临时表涉及到性能问题，先介绍下sql中主要关键字的执行顺序：

from  
on  
join  
where  
group by  
having  
select  
distinct  
union  
order by

我们看到：

  • on(on条件，可以进行过滤操作)的执行顺序是在join(连接操作)前面的
  • where(where条件，可以进行过滤操作)的执行顺序是在join后面的

sql查询的基本原理：我们这里主要考虑第二种情况

  • 第一、 单表查询：根据where条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据select的选择列选择相应的列进行返回最终结果。
  • 第二、 两表连接查询：对两表求积（笛卡尔积）并用on条件和连接类型进行过滤形成中间表；然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。
  • 第三、 多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果。
    理解sql查询的过程是进行sql优化的理论依据。

第三种其实是第二种的特殊情况，逐个进行2表连接，直至全部连接

第二种情况牵扯到on后面的条件（on条件）和where条件的区别：

相同点：on条件和where条件都是进行过滤的
不同点：

  • on条件：是过滤两个链接表笛卡尔积形成中间表T1的约束条件。
  • where条件：
    在有on条件的select语句中是过滤中间表T1的约束条件(在中间表T1的基础上再进行过滤)。
    在没有on的单表查询中，是限制物理表或者中间查询结果返回记录的约束。(和本章节无关)

由以上可知：
如果两张表的数据量都比较大的话，直接进行连接查询时(不带on条件)，那样交叉连接生成的临时表就会占用很大的内存空间，这显然是不合理的。所以，我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法（带on条件），ON语句的执行是在JOIN语句之前的，也就是说两张表数据行之间进行匹配的时候，会先判断数据行是否符合ON语句后面的条件，再决定是否JOIN。

因此，有一个显而易见的SQL优化的方案是，当两张表的数据量比较大，又需要连接查询时，应该使用 FROM table1 JOIN table2 ON xxx的语法，避免使用 FROM table1,table2 WHERE xxx 的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。

on过滤过程：

doc连接mysql数据库 mysql数据库内连接_图示_11


where过滤过程：

doc连接mysql数据库 mysql数据库内连接_右连接_12



参考：
《SQL LEFT JOIN 关键字》《关系数据库内连接、左连接、右连接图示说明》

《SQL各种连接查询详解(左连接、右连接…)》

《Sql 中内连接、外连接、全连接、交叉连接的区别》交叉连接、笛卡尔积