前言
我们都知道内联接返回的是两张表的交集数据:inner join 左连接返回左表的数据,如果没有右表匹配的返回null
但是我们知道底层是为什么吗? 下面我们就来看具体的底层算法,其实看了算法就很明白为什么了,而不用去死记硬背!!
实践
有两张表
mysql> select * from score;
+----+--------+-------+
| id | stu_id | score |
+----+--------+-------+
| 1 | 1 | 69 |
| 2 | 2 | 90 |
+----+--------+-------+
2 rows in set (0.00 sec)
mysql> select * from student;
+----+------+
| id | name |
+----+------+
| 1 | zx |
| 2 | ls |
| 3 | jack |
+----+------+
3 rows in set (0.00 sec)
内连接
mysql> select * from student s inner join score c on s.id=c.stu_id;
+----+------+----+--------+-------+
| id | name | id | stu_id | score |
+----+------+----+--------+-------+
| 1 | zx | 1 | 1 | 69 |
| 2 | ls | 2 | 2 | 90 |
+----+------+----+--------+-------+
2 rows in set (0.00 sec)
左连接
mysql> select * from student s left join score c on s.id=c.stu_id;
+----+------+------+--------+-------+
| id | name | id | stu_id | score |
+----+------+------+--------+-------+
| 1 | zx | 1 | 1 | 69 |
| 2 | ls | 2 | 2 | 90 |
| 3 | jack | NULL | NULL | NULL |
+----+------+------+--------+-------+
3 rows in set (0.00 sec)
上面的查询结果明显的区别,就不用多说了 ,下面我们查看官网是怎么来处理这两种联接的
结论
以下内容均来自官网:
内连接
SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
INNER JOIN T3 ON P2(T2,T3)
WHERE P(T1,T2,T3)
在这里,P1(T1,T2)
and P2(T3,T3)
是一些连接条件(在表达式上),而是P(T1,T2,T3)
在table的列上的条件T1,T2,T3
。
嵌套循环联接算法将以以下方式执行此查询:
FOR each row t1 in T1 {
FOR each row t2 in T2 such that P1(t1,t2) {
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
外联接
现在考虑一个带有嵌套外部联接的查询:
SELECT * FROM T1 LEFT JOIN (T2 LEFT JOIN T3 ON P2(T2,T3)) ON P1(T1,T2) WHERE P(T1,T2,T3)
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
BOOL f2:=FALSE;
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF P(t1,t2,NULL) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
我相信看到这里大家就很明白为啥上面的结果是那样的
未完待续。。。。