关于SQL Join的实现讨论

最新推荐文章于 2022-03-08 10:24:17 发布

chenwanya5281

最新推荐文章于 2022-03-08 10:24:17 发布

阅读量152

点赞数 1

原文链接：https://my.oschina.net/u/3960369/blog/1942455

版权

Inner Join

多表满足关联条件的记录才会出现在结果表中，我认为经过优化的inner join SQL伪码如下：

#select * from A inner join B on A.col=B.col and A或B表筛选条件条件 where A或B表筛选条件条件
#首先对A表进行筛选，纯A表筛选条件
for item in A:
  if item 满足 A表筛选条件:
     tempA <- item #赞记该语法表示：item插入到临时表tempA中
#再对B表进行筛选，纯B表筛选条件
for item in B:
   if item 满足 B表筛选条件:
     tempB <- item

#两表进行关联
for left in tempA:
  for right in tempB:
    if left满足关联字段 and right满足关联字段:
      tempResult <- (left,right)
    #else:
    #  tempReulst <- (left,null)

在inner join中，筛选条件无论在on中，还是在where中，都提前到from中执行得到临时表(提前筛选结果集)
临时表join时，相当于驱动表A中每条记录a，去匹配被驱动表B中每条记录b，如果B中记录b匹配a的关联字段，则元组(a,b)合入结果集。可能出现一条记录a匹配多条记录b。
~~如果记录b不匹配a的关联字段，则(a,null)返回~~

mysql> select * from person;
+----+-----------+------+------+------------+
| id | name      | age  | tel  | city       |
+----+-----------+------+------+------------+
|  1 | lilei     |   30 | 123  | hangzhou   |
|  2 | hanmeimei |   28 | 234  | suzhou     |
|  3 | tom       |   29 | 345  | los angels |
|  4 | jin       |   31 | 456  | paris      |
|  5 | lily      |   27 | 567  | london     |
|  6 | lucy      |   30 | 789  | new york   |
+----+-----------+------+------+------------+
6 rows in set (0.00 sec)

mysql> select * from country;
+----+----------+----------+
| id | city     | country  |
+----+----------+----------+
|  1 | hangzhou | china    |
|  2 | suzhou   | china    |
|  3 | paris    | franch   |
|  4 | london   | england  |
|  5 | new york | usa      |
|  6 | hangzhou | zhejiang |
+----+----------+----------+
6 rows in set (0.00 sec)

--- lilei匹配多条记录
mysql> select * from person as a inner join country as b on a.city=b.city;
+----+-----------+------+------+----------+----+----------+----------+
| id | name      | age  | tel  | city     | id | city     | country  |
+----+-----------+------+------+----------+----+----------+----------+
|  1 | lilei     |   30 | 123  | hangzhou |  1 | hangzhou | china    |
|  1 | lilei     |   30 | 123  | hangzhou |  6 | hangzhou | zhejiang |
|  2 | hanmeimei |   28 | 234  | suzhou   |  2 | suzhou   | china    |
|  4 | jin       |   31 | 456  | paris    |  3 | paris    | franch   |
|  5 | lily      |   27 | 567  | london   |  4 | london   | england  |
|  6 | lucy      |   30 | 789  | new york |  5 | new york | usa      |
+----+-----------+------+------+----------+----+----------+----------+
6 rows in set (0.00 sec)

--- inner join:条件在on或where中结果一样
mysql> select * from person as a inner join country as b on a.city=b.city and b.country='china';
+----+-----------+------+------+----------+----+----------+---------+
| id | name      | age  | tel  | city     | id | city     | country |
+----+-----------+------+------+----------+----+----------+---------+
|  1 | lilei     |   30 | 123  | hangzhou |  1 | hangzhou | china   |
|  2 | hanmeimei |   28 | 234  | suzhou   |  2 | suzhou   | china   |
+----+-----------+------+------+----------+----+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from person as a inner join country as b on a.city=b.city where b.country='china';
+----+-----------+------+------+----------+----+----------+---------+
| id | name      | age  | tel  | city     | id | city     | country |
+----+-----------+------+------+----------+----+----------+---------+
|  1 | lilei     |   30 | 123  | hangzhou |  1 | hangzhou | china   |
|  2 | hanmeimei |   28 | 234  | suzhou   |  2 | suzhou   | china   |
+----+-----------+------+------+----------+----+----------+---------+
2 rows in set (0.00 sec)

Left Join

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

#left join : select * from A left join B on A.col=B.col and A.col1 and B.col2 where A.col3 and B.col4
for item in A:
    if item 满足 where中A表筛选条件:                                   ---- (1)
        tempA <- item
        
for item in B:
    if item 满足 on中B表筛选条件 ：                                    ---- (2)
    	tempB <- item
        
for left in tempA:
   if left 满足 on中A表筛选条件:                                       ----(3)
      flag = False
      for right in tempB:       
            if left满足关联字段 and right满足on关联字段:                ----(4)
                flag = True
                tempResult <- (left,right)
      if flag == False:                                               ----(5)
                tempResult <- (left,null)
   else:                                                              ----(6)
            tempResult <- (left,null)
            
for item in tempResult:
    if item 满足 where中A/B表筛选条件:                                   ----(7)
        tableResult <- item
    else:
        continue

（1）中首先执行，主表(A)的where筛选条件。SQL优化器知道，你最后会丢弃这部分不满足条件记录，所以提前过滤掉记录，有助于减少驱动表规模。这里为什么不连同on条件中的A表条件仪器过滤？（on中过滤了让left join返回A表全部记录颜面何存？）
（2）中不满足on中关于B表条件这些记录将会被丢弃。因为on中B的筛选条件是匹配条件，如果不满足这部分条件，则不会和A表记录匹配，所以提前过滤到这部分失败记录，有助于减少被驱动表规模。
（3）和（6）联合看，如果驱动表A中记录left，不满足on中的A表条件，依然返回到临时表中（left join要返回），只不过这是匹配条件表现出来就是B表字段全是null即(left,null)。A表中满足on中A表条件的才会真正进入join。
（4）针对记录left，扫描tempB表中每条记录right，如果right和left关于on中字段匹配，则(left,right)进入临时结果集
（5）tempB表中无一条记录，能在on字段上匹配left的，则(left,null)插入临时结果集
（7）针对结果集筛选的A或B表条件，和（2）条件的区别是，这部分条件不能区分是Join前还是join后产生的。例如B.col is not null 可能是join时不能匹配条件时，产生的null。不能提前到（2）。
重点
- on中A表条件，不会过滤掉A表记录，只会使B表字段NULL
- B表中on和where中筛选字段作用相同，干掉B表中不符合记录，但是在结果集上表现却大相径庭。不满足on中条件记录不进入join过程，而where中B表条件是对最终结果集筛选。

mysql> select * from person as a left join country as b on a.city = b.city and b.country='china';
+----+-----------+------+------+------------+------+----------+---------+
| id | name      | age  | tel  | city       | id   | city     | country |
+----+-----------+------+------+------------+------+----------+---------+
|  1 | lilei     |   30 | 123  | hangzhou   |    1 | hangzhou | china   |
|  2 | hanmeimei |   28 | 234  | suzhou     |    2 | suzhou   | china   |
|  3 | tom       |   29 | 345  | los angels | NULL | NULL     | NULL    |
|  4 | jin       |   31 | 456  | paris      | NULL | NULL     | NULL    |
|  5 | lily      |   27 | 567  | london     | NULL | NULL     | NULL    |
|  6 | lucy      |   30 | 789  | new york   | NULL | NULL     | NULL    |
+----+-----------+------+------+------------+------+----------+---------+
6 rows in set (0.00 sec)

mysql> select * from person as a left join country as b on a.city = b.city where b.country='china';
+----+-----------+------+------+----------+------+----------+---------+
| id | name      | age  | tel  | city     | id   | city     | country |
+----+-----------+------+------+----------+------+----------+---------+
|  1 | lilei     |   30 | 123  | hangzhou |    1 | hangzhou | china   |
|  2 | hanmeimei |   28 | 234  | suzhou   |    2 | suzhou   | china   |
+----+-----------+------+------+----------+------+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from person as a left join country as b on a.city = b.city and a.age>=30;
+----+-----------+------+------+------------+------+----------+----------+
| id | name      | age  | tel  | city       | id   | city     | country  |
+----+-----------+------+------+------------+------+----------+----------+
|  1 | lilei     |   30 | 123  | hangzhou   |    1 | hangzhou | china    |
|  4 | jin       |   31 | 456  | paris      |    3 | paris    | franch   |
|  6 | lucy      |   30 | 789  | new york   |    5 | new york | usa      |
|  1 | lilei     |   30 | 123  | hangzhou   |    6 | hangzhou | zhejiang |
|  2 | hanmeimei |   28 | 234  | suzhou     | NULL | NULL     | NULL     |
|  3 | tom       |   29 | 345  | los angels | NULL | NULL     | NULL     |
|  5 | lily      |   27 | 567  | london     | NULL | NULL     | NULL     |
+----+-----------+------+------+------------+------+----------+----------+
7 rows in set (0.00 sec)

mysql> select * from person as a left join country as b on a.city = b.city where a.age>=30;
+----+-------+------+------+----------+------+----------+----------+
| id | name  | age  | tel  | city     | id   | city     | country  |
+----+-------+------+------+----------+------+----------+----------+
|  1 | lilei |   30 | 123  | hangzhou |    1 | hangzhou | china    |
|  4 | jin   |   31 | 456  | paris    |    3 | paris    | franch   |
|  6 | lucy  |   30 | 789  | new york |    5 | new york | usa      |
|  1 | lilei |   30 | 123  | hangzhou |    6 | hangzhou | zhejiang |
+----+-------+------+------+----------+------+----------+----------+
4 rows in set (0.00 sec)

right join 和left join类似

JOIN概览

转载于:https://my.oschina.net/u/3960369/blog/1942455

chenwanya5281

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关于SQL Join的实现讨论

Inner Join多表满足关联条件的记录才会出现在结果表中，我认为经过优化的inner join SQL伪码如下：#select * from A inner join B on A.col=B.col and A或B表筛选条件条件 where A或B表筛选条件条件#首先对A表进行...
复制链接

扫一扫