mysql 笛卡尔积_Mysql内连接、左连接会出现笛卡尔积的理解

先简单解释一下笛卡尔积。

现在，我们有两个集合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集合的元素个数;

MySQL的多表查询(笛卡尔积原理)

先确定数据要用到哪些表。

将多个表先通过笛卡尔积变成一个表。

然后去除不符合逻辑的数据(根据两个表的关系去掉)。

最后当做是一个虚拟表一样来加上条件即可。

数据库表连接数据行匹配时所遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。

比如现在数据库中有两张表，student表和 student_subject表，如下所示：

　　

我们执行以下的sql语句，只是纯粹的进行表连接。

SELECT * from student JOIN student_subject;

SELECT * from student_subject JOIN student;

看一下执行结果：

　　

表1.0　　　　　　　　　　　　　　　　　　　　　　　　　 　　表1.1

从执行结果上来看，结果符合我们以上提出的两点结论(红线标注部分)；

以第一条sql语句为例我们来看一下他的执行流程，

1，from语句把student表 和 student_subject表从数据库文件加载到内存中。

2，join语句相当于对两张表做了乘法运算，把student表中的每一行记录按照顺序和student_subject表中记录依次匹配。

3，匹配完成后，我们得到了一张有 (student中记录数 × student_subject表中记录数)条的临时表。 在内存中形成的临时表如表1.0所示。我们又把内存中表1.0所示的表称为‘笛卡尔积表’。

针对以上的理论，我们提出一个问题，难道表连接的时候都要先形成一张笛卡尔积表吗，如果两张表的数据量都比较大的话，那样就会占用很大的内存空间这显然是不合理的。所以，我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法，ON语句的执行是在JOIN语句之前的，也就是说两张表数据行之间进行匹配的时候，会先判断数据行是否符合ON语句后面的条件，再决定是否JOIN。

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

下面引出Mysql的左右连接和内连接的笛卡尔积...

一个同事跟我讨论左连接查询，是不是笛卡尔积。我第一反应，左连接肯定不是笛卡尔积啊，左连接是以左表为准，左表有m条记录，则结果集是m条记录(哈哈，如果是你，你是不是也是这样的反映)，同事听了，说内连接会是笛卡尔积。在数据库里试验了一下，发现，事实比想象中要复杂。

首先说下结论：链接查询，如果on条件是非唯一字段，会出现笛卡尔积(局部笛卡尔积)；如果on条件是表的唯一字段，则不会出现笛卡尔积。

下面是具体的试验：(以真三国无双v3.9d蜀国阵容为例...)

文中会有两张表，user表和job表，表数据如下,其中user为5条记录,job为4条记录

USER:

   job:  

1.交叉连接

如果A表有m(5)条记录，m1条符合on条件，B表有n(4)条记录，有n1条符合on条件，无条件交叉连接的结果为: m*n=5*4=20

SELECT * FROM `user` CROSS JOIN job;

这种等同于(交叉查询等于不加on的内连接)

SELECT * FROM `user` , job;

sql执行结果：总共20条记录

结论：交叉连接，会产生笛卡尔积。

2.内连接(可以当做左连接的特殊情况,只保留符合主表中on条件的记录)

(1)内连接唯一字段

如果A表有m(5)条记录，m1(4)条符合on条件，B表有n(4)条记录，有n1(3)条符合on条件，内连接唯一字段结果为:Max(m1,n1)=4

1,2,2,6,7 和 1,2,7,8对比,以user表为主表,因为主表中有4条符合条件的记录(1,2,2,7),而job表有3条符合条件的记录(1,2,7),取两者中的最大的,所以为4条

SELECT * FROM `user` u JOIN job j ON u.JOB_ID=j.ID;

sql执行结果为:4条记录

结论：假如，内连接查询，on条件是A表或者B表的唯一字段，则结果集是两表的交集，不是笛卡尔积。

(2)内连接非唯一字段

如果A表有m(5)条记录，m1(2)条符合on条件，B表有n(4)条记录，有n1(3)条符合on条件，则结果集是Max(m1,n1)=3条

1,2,2,6,7 和 1,1,7,8对比,以user表为主表,因为主表中有2条符合条件的记录(1,7),而job表有3条符合条件的记录(1,1,7),取两者中的最大的,所以为3条

SELECT * FROM `user` u JOIN job j ON u.valid=j.valid;

结论：假如，on条件是表中非唯一字段，则结果集是两表匹配到的结果集的笛卡尔积(局部笛卡尔积) 。

3.外连接

(1)左连接

a.左连接唯一字段

假如A表有m(5)条记录，B表有n(4)条记录，则结果集是m=5

1,2,2,6,7 和 1,2,7,8对比,以user表为主表,因为主表中有4条符合条件的记录(1,2,2,7),而job表有3条符合条件的记录(1,2,7),取两者中的最大的,所以取4条,然后再加上user表中没有在job表中找到对应关系的记录(即对应的job表都为null,5-4=1),所以最终结果为4+1=5条

SELECT * FROM USER u LEFT JOIN job j ON u.JOB_ID=j.id;

SQL查询结果:5条记录

结论：on条件是唯一字段，则结果集是左表记录的数量。

b.左连接非唯一字段

1,2,2,6,7 和 1,1,7,8对比,以user表为主表,因为主表中有2条符合条件的记录(1,7),而job表有3条符合条件的记录(1,1,7),取两者中的最大的,所以取3条,然后在加上user表在job表中没有匹配的记录(即对应的job表都为null,为5-2=3),所以最终结果为3+3=6条

SELECT * FROM `user` u LEFT JOIN job j ON u.VALID=j.VALID;

结论：左连接非唯一字段，是局部笛卡尔积。

c.当on 条件为假时的内连接：

SELECT * FROM `user` u LEFT JOIN job j ON 0;

sql查询结果:5条

结论：当on条件为假的时候,即user在job表中一条符合记录的都没有,那么即为:user表中的所有记录条数,所以为5条,job表中的值都为null

(2)右连接

同左连接，这里就不赘述了

全外连接

mysql不支持

总结:左右连接是否产生笛卡尔积,和on的条件是否为唯一索引没有关系,只和左表、右表两者各自匹配on条件的记录条数有关系(左连接和右连接可以这么做:1:由INNER JOIN查询全匹配记录;2:查询左表或者右表没有符合on条件的记录;3.全匹配记录+没有匹配上的记录就是左连接或者右连接查询的结果集)

1.全匹配:

无论哪种查询,首先计算出on匹配记录(FROM user INNER JOIN job ON ...或者使用 FROM user,job where...),匹配记录的查询结果为:若A表有m条记录,符合on查询条件的为m1条,B表有n条记录,符合on条件的为n1条,那么匹配记录为MAX(m1,n1);

2.左连接:

结果集为:MAX(m1,n1)+(m-m1);

如果m1 > n1,则不会产生笛卡尔积,因为无论不匹配的记录(m-m1),还是匹配的记录MAX(m1,n1),都是从左表中取记录,所以不会出现重复的记录;反之,如果m1 < n1,则一定会产生笛卡尔积,因为MAX(m1,n1)是从右表中取的,而根据笛卡尔积的原理,右表中的每条记录都会和左表中的所有记录匹配一次,所以符合on条件的n1条记录也一定会和左表中的所有记录都匹配一次,而左表中符合记录只有m1条,所以造成笛卡尔积的条数为(n1-m1)条

即用内连接的记录(MAX(m1,n1)),加上左表没有满足on条件的记录(m-m1),所以为:MAX(m1,n1)+(m-m1);

3.有连接

结果集为:MAX(m1,n1)+(n-n1);

如果m1 n1,则一定会产生笛卡尔积,因为MAX(m1,n1)是从左表中取的,所以造成笛卡尔积的记录条数为(m1-n1)条

即用内连接的记录(MAX(m1,n1)),加上右表没有满足on条件的记录(n-n1),所以为:MAX(m1,n1)+(n-n1);

下面再来谈下:

Mysql：ON 与 WHERE 的区别

很多同学在学习 Mysql 表关联的时候弄不清 ON 与 WHERE 的区别，不知道条件应该写在 ON 里面还是 WHERE 里面，作者在工作的时候也入过坑，总觉得条件写在哪里查询结果都是一样的，最后出错坏了事，差点惹了大祸。所以今天简单易懂的总结一下他们的区别，大家共同学习。

准备工作

我们先准备两个表，并造一些数据：

表t1:

CREATE TABLE `t1` (

`id` BIGINT(20) NOT NULL DEFAULT '0' COMMENT '主键id',

`name` CHAR(100) NOT NULL DEFAULT '' COMMENT '姓名',

`age` INT(11) NOT NULL DEFAULT '0'

) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `t1`(`id`,`name`,`age`) VALUES (1,'C罗',33),(2,'梅西',31),(3,'内马尔',29);

表t2:

CREATE TABLE `t2` (

`id` BIGINT(20) NOT NULL DEFAULT '0' COMMENT '主键id',

`goals` INT(11) NOT NULL DEFAULT '0' COMMENT '进球数',

`matches` INT(11) NOT NULL DEFAULT '0' COMMENT '比赛编号'

) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `t2`(`id`,`goals`,`matches`) VALUES (1,3,1),(1,5,2),(2,0,1),(2,8,2);

查询结果如图:

表t1记录:

表t2记录:

探究

口诀：先执行 ON，后执行 WHERE；ON 是建立关联关系，WHERE 是对关联关系的筛选。

记住这句话就可以准确地判断查询结果了，我们通过两个 sql 来进行分析：

SQL1:

SELECT t1.id,t2.id,t1.name,t1.age,t2.`matches` FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE matches = 2; -- 条件放在 WHERE

前提是 LEFT JOIN，所以左边的数据在创建关联关系时会保留，根据口诀，先执行 ON 建立关联关系,形成子表,最后在子表中通过 WHERE 筛选，过程如下：

左表符合记录的t1.id = t2.id的记录有2条,右表符合记录的有4条,所以MAX(m1,n1)为4条,其中左表没有符合on条件的记录为(3-2=1)条,所以LEFT JOIN的结果总共有5条,最后一条左表没有匹配上右表记录,所以右表的属性都为null,如下:

但是最终结果从5条记录中再通过where进行筛选,即matches为2,所以结果只有2条(注意先LEFT JOIN,然后再是where,所以此时5条记录中不符合where条件的记录会被排除,即最终的结果不再是左表所有的记录):

SQL2:

SELECT t1.id,t2.id,t1.name,t1.age,t2.`matches` FROM t1 LEFT JOIN t2 ON t1.id = t2.id AND matches = 2; -- 条件放在 ON

SQL2没有 WHERE，那么 ON 建立的关联关系就是最终结果(因为没有where条件进行最终筛选,所有最终结果为内联记录加上左表中没有符合on条件的记录)：

符合ON条件的记录,改为内联INNER JOIN ,查询基础数据:MAX(m1,n1)

SELECT t1.id,t2.id,t1.name,t1.age,t2.`matches` FROM t1 INNER JOIN t2 ON t1.id = t2.id AND matches = 2;

所以MAX(m1,n1)为2条,结果为:

再由基础数据加上左表没有匹配上的记录数(只有id为3的没有匹配上,即(n-n1) = 1)只有1条,所以最终结果为:

最终结果为:MAX(m1,n1)+(m-m1) = 2 + (id为3的记录) = 3条

通过这两个 sql 可以很好的区分 WHERE 和 ON 的区别了，希望大家使用的时候多注意这一点，避免犯错！

逻辑上一个query的执行顺序(不是实际)

1. FROM

2. ON

3. JOIN

4. WHERE

5. GROUP BY

6. WITH CUBE or WITH ROLLUP

7. HAVING

8. SELECT

9. DISTINCT

10. ORDER BY

11. TOP

说是“逻辑上” 顺序，因为实际执行时还要看索引，数据分布等，看最终优化器如何处理，最真实的顺序肯定是执行计划展示的顺序。

QL语句中join连表时on和where后都可以跟条件，那么对查询结果集，执行顺序，效率是如何呢？

通过查询资料发现：

区别：

on是对中间结果进行筛选，where是对最终结果筛选。

执行顺序：

先进行on的过滤, 而后才进行join。

效率：

如果是inner join, 放on和放where产生的结果一样, 但没说哪个效率速度更高? 如果有outer join (left or right), 就有区别了, 因为on生效在先, 已经提前过滤了一部分数据, 而where生效在后.

最后来了解下T-SQL对查询逻辑处理。

T-SQL逻辑查询的各个阶段(编号代表顺序)：

(5)SELECT DISTINCT TOP()

(1)FROM JOIN ON

(2)WHERE

(3)GROUP BY

(4)HAVING

(6)ORDER BY

T-SQL在查询各个阶级分别干了什么：

(1)FROM 阶段

FROM阶段标识出查询的来源表，并处理表运算符。在涉及到联接运算的查询中(各种join)，主要有以下几个步骤：

a.求笛卡尔积。不论是什么类型的联接运算，首先都是执行交叉连接(cross join)，求笛卡儿积，生成虚拟表VT1-J1。

b.ON筛选器。这个阶段对上个步骤生成的VT1-J1进行筛选，根据ON子句中出现的谓词进行筛选，让谓词取值为true的行通过了考验，插入到VT1-J2。

c.添加外部行。如果指定了outer join，还需要将VT1-J2中没有找到匹配的行，作为外部行添加到VT1-J2中，生成VT1-J3。

经过以上步骤，FROM阶段就完成了。概括地讲，FROM阶段就是进行预处理的，根据提供的运算符对语句中提到的各个表进行处理(除了join，还有apply，pivot，unpivot)

(2)WHERE阶段

WHERE阶段是根据中条件对VT1中的行进行筛选，让条件成立的行才会插入到VT2中。

(3)GROUP BY阶段

GROUP阶段按照指定的列名列表，将VT2中的行进行分组，生成VT3。最后每个分组只有一行。

(4)HAVING阶段

该阶段根据HAVING子句中出现的谓词对VT3的分组进行筛选，并将符合条件的组插入到VT4中。

(5)SELECT阶段

这个阶段是投影的过程，处理SELECT子句提到的元素，产生VT5。这个步骤一般按下列顺序进行

a.计算SELECT列表中的表达式，生成VT5-1。

b.若有DISTINCT，则删除VT5-1中的重复行，生成VT5-2

c.若有TOP，则根据ORDER BY子句定义的逻辑顺序，从VT5-2中选择签名指定数量或者百分比的行，生成VT5-3

(6)ORDER BY阶段

根据ORDER BY子句中指定的列明列表，对VT5-3中的行，进行排序，生成游标VC6.

如果是inner join, 放on和放where产生的结果一样, 执行计划也是一样，但推荐使用on。但如果有outer join (left or right), 就有区别了, 因为on生效在先, 已经提前过滤了一部分数据, 而where生效在后，而且where对于outer join有不生效的情况

使用left join时on后面的条件只对右表有效

on是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表 (table_name1) 的行。

where则是在生成临时表之后使用的条件，此时已经不管是否使用了left join了，只要条件不为真的行，全部过滤掉。