可以拿来吊打面试官的 SQL Join （一）

Lenis

发布于 2020-06-15 11:41:11

3670

发布于 2020-06-15 11:41:11

文章被收录于专栏：有关SQL有关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 (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER 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的条件。