mysql 内联接和外联接底层算法

前言

 我们都知道内联接返回的是两张表的交集数据: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;
    }
  }
}

我相信看到这里大家就很明白为啥上面的结果是那样的

 

未完待续。。。。