找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,极速登录

可以拿来吊打面试官的 SQL Join (一)

讨论所有与SQL有关的内容

这一系列讲述,两张表的 Join, 或许你都不一定知道的事儿。

之前写过 SQL 的编译原理,很多朋友都不知道 SQL 背后,居然还有编译一说。SQL 用起来和 C#/Java 还是有些异样的。写好 SELECT * (虽然这么写很糟糕!)以后,按下 F5 便能得到结果。而不像 C#/Java 需要经过 CLR/JVM 这样的“转译”,才能看到实实在在的程序输入输出窗口。

正是由于 SQL 这个隐形编译器的存在,很多莫名的语法,看起来就很费解。比如最让初学者头疼的 Left Join:

SELECT Header.OrderId
    ,   Detail.ProductName
    ,   Detail.Amount 
FROM tblOrderHeader Header 
    LEFT JOIN tblOrderDetail Detail 
        ON Header.OrderId = Detail.OrderId 
WHERE Header.OrderDate > '2020-01-01' 
    AND Detail.Amount > 1000

本意上,这段 SQL 要达到的目的是,找出 2020 年 1 月 1 日以来,单件商品超过 1000 元销售额的订单,并显示该件商品的产品名。

到底为止,若能根据这个要求,完整写出上面的 SQL,那就是合格的数据库开发工程师了。但有些朋友,经常会写出这样的 SQL:

SELECT Header.OrderId
    ,   Detail.ProductName
    ,   Detail.Amount 
FROM tblOrderHeader Header 
    LEFT JOIN tblOrderDetail Detail 
        ON Header.OrderId = Detail.OrderId 
        AND Detail.Amount > 1000
WHERE Header.OrderDate > '2020-01-01' 

还有这样的 SQL:

SELECT Header.OrderId
    ,   Detail.ProductName
    ,   Detail.Amount 
FROM tblOrderHeader Header 
    LEFT JOIN tblOrderDetail Detail 
        ON Header.OrderId = Detail.OrderId 
        AND Header.OrderDate > '2020-01-01' 
        AND Detail.Amount > 1000

看上去,及其相似的三段 SQL,为什么出来的结果就千差万别呢。甚至,还会把数据库给跑死。

这就是 SQL 编译的底层。

要了解 SQL 编译的底层,要从这张图,全面入手:

而 Parsing 就是我们正确理解 SQL 执行过程的第一步。

下面这段 SQL 是比较完整的全范本:

(8) SELECT (9DISTINCT (11) <TOP_specification> <select_list>
(1FROM <left_table>
(3) <join_type> JOIN <right_table>
(2ON <join_condition>
(4WHERE <where_condition>
(5GROUP BY <group_by_list>
(6WITH {CUBE | ROLLUP}
(7HAVING <having_condition>
(10ORDER BY <order_by_list>

细心的读者可能会留意到每个 SQL 关键字前面都有一个用括号包起来的数字,这个数字就是 SQL 关键字执行的顺序。

我们通常会认为 SELECT 是 SQL 的第一步,其实 FROM 才是,紧接 From 的是 ON, JOIN. 之后才是 WHERE. 正确理解 JOIN 和 WHERE 的执行顺序,才可避免 LEFT JOIN 留下的坑。

当然,你别以为这样就结束了,那我也太不负责任了。接着往下读。

当两表 Join 的时候,先按照 ON 的条件做了一次笛卡尔积计算。甭管按照 ON 的条件能不能匹配,匹配的上,就拼接起来;匹配不上的,暂时保留。所以 ON 这一步,两边的数据,都会保留在一张虚拟的大表里。

比如,上面两张表, tblOrderHeader, tblOrderDetail. 他们的外键是OrderId.

这两个表,join 起来,会有这些情况:

tblOrderHeader 有些数据,在 tblOrderDetail 里按照 OrderId 找不到对应的订单明晰数据。

同样的,在 tblOrderDetail 中有些明细的订单,却在 tblOrderHeader 头部中找不到订单表头信息,比如订单时间,商店,会员信息等。

更常见的,是互相找不到对应数据

用实线框,框起来的表示两表可以互相匹配的数据。而对方表缺失的部分就用白色标注。

两表 Join 的初步结果就出来了,就是上面最后一张图的情况,即保留两表所有的数据,匹配上的,排在前头,匹配不上的依次排在后面。但必须保留两张表所有的数据。这要牢牢记住。

接着根据第三步 JOIN 的 Join Type(Left join, Right Join, Full Outer Join)来限制留下哪部分。

Left Join, 留下左半部分:

Right Join, 留下右半部分:

Full Outer Join , 左右都留下:

接下来,才是执行 WHERE 命令的时候。

此时,下面这段 SQL , 即

SELECT Header.OrderId
    ,   Detail.ProductName
    ,   Detail.Amount 
FROM tblOrderHeader Header 
    LEFT JOIN tblOrderDetail Detail 
        ON Header.OrderId = Detail.OrderId 
        AND Detail.Amount > 1000
WHERE Header.OrderDate > '2020-01-01' 

会比这条 SQL , 多出来很多数据:

SELECT Header.OrderId
    ,   Detail.ProductName
    ,   Detail.Amount 
FROM tblOrderHeader Header 
    LEFT JOIN tblOrderDetail Detail 
        ON Header.OrderId = Detail.OrderId 
WHERE Header.OrderDate > '2020-01-01' 
    AND Detail.Amount > 1000

那是因为,在 WHERE 中,Detail.Amount > 1000 这个命令,限制了右半边的数据必须要对应上左半边的OrderId, 所以 tblOrderDetail 中如果没有 tblOrderHeader 中的OrderId, 则就被舍去。哪怕 tblOrderHeader 的 OrderDate 是符合 OrderDate 大于 2020-01-01的条件。

比如有 tblOrderHeader 是有 5 条记录,符合  OrderDate 大于 2020-01-01的条件。

我们用红色实星框表示符合条件的记录

但最终,因为在 WHERE 中添加了 Detail.Amount > 1000 的条件,相当于把 Left join 改成了 INNER JOIN, 即增加了 Detail.OrderId IS NOT NULL 条件

您需要登录后才可以回帖 登录 | 立即注册  

本版积分规则

0回帖数 1关注人数 289浏览人数
最后回复于:2021-11-25 12:51

Array ( [id] => 15 [code] => 4301 [product] => [title] => 帖子详情页右侧banner [demand] => 334px * 410px [img_url] => /uploads/20211129/c31b8a4cf8f84570c19466d0f0cd51ca.png [click_url] => https://bbs.fanruan.com/jobs [status] => 1 [end_time] => 1656567829 [username] => mia.wang [edit_time] => 1638164633 )

联系社区管理员|联系帆软|《帆软社区协议》|手机版|帆软社区|Copyright © 帆软软件有限公司 ( 苏ICP备18065767号-7 )

GMT+8, 2021-12-9 15:38 , Processed in 0.112202 second(s), 45 queries , Gzip On.

返回顶部 返回列表