先简单解释一下笛卡尔积。
现在，我们有两个集合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条件是表的唯一字段，则不会出现笛卡尔积。
下面是具体的试验：
文中会有两张表，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的条件是否为唯一索引没有关系,和具体的数值有关系
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)条
3.有连接
结果集为:MAX(m1,n1)+(n-n1);
如果m1 < n1,则不会产生笛卡尔积,因为无论不匹配的记录(n-n1),还是匹配的记录MAX(m1,n1),都是从右表中取记录,所以不会出现重复的记录;反之,如果m1 > n1,则一定会产生笛卡尔积,因为MAX(m1,n1)是从左表中取的,所以造成笛卡尔积的记录条数为(m1-n1)条
774
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
