SQL JOIN 探究

1. 基本概念

平时在写 SQL 的时候，经常需要关联多张表查数据。比如在电力行业中，要查询电表的电量采集数据，就得把电表档案表和电量采集数据表关联起来。

比较常见的关联方式就下面两种：

• 内连接 ( JOIN 或 INNER JOIN ) ，只返回两表都匹配的数据。比如只返回电表档案表里有记录，并且有电量采集数据的电表。

  -- 标准的内连接语法
  SELECT a.*, b.*
  FROM table_a a
  JOIN table_b b ON a.id = b.id;
  
  
  -- 还有一种等价的老式语法
  SELECT a.*, b.*
  FROM table_a a, table_b b
  WHERE a.id = b.id;

• 左外连接 ( LEFT JOIN ) ，保留左表的所有数据，右表没匹配上的显示NULL。

  -- 基础的左连接语法
  SELECT a.*, b.*
  FROM table_a a
  LEFT JOIN table_b b ON a.id = b.id;

2. 内连接隐式 Join 跟显式 Join

内连接支持隐式连接跟显示连接，外连接正常情况下是必须使用显式连接语法的，除非是数据库方言支持。

准备表结构以及测试数据：

-- 电表档案表
CREATE TABLE meters (
    meter_id varchar(20)    -- 电表编号
);

-- 电表数据表
CREATE TABLE readings (
    meter_id varchar(20), -- 电表编号
    read_time timestamp,     -- 采集时间
    value decimal(10,2)  -- 读到的数值
);

-- 插入测试数据
INSERT INTO meters VALUES
    ('M001'),
    ('M003'),
    ('M004');

INSERT INTO readings VALUES
    ('M001', '2019-01-01 10:00:00', 100.50),
    ('M001', '2019-01-01 11:00:00', 102.50),
    ('M002', '2019-01-01 10:00:00', 200.00),  -- 注意这个表号在电表档案表中是不存在的
    ('M003', '2019-01-01 10:00:00', 300.75);

隐式连接：

SELECT m.meter_id, r.read_time, r.value
FROM meters m,
     readings r
WHERE m.meter_id = r.meter_id;

-- 执行结果
meter_id | read_time           | value
M001     | 2019-01-01 10:00:00 | 100.50
M001     | 2019-01-01 11:00:00 | 102.50
M003     | 2019-01-01 10:00:00 | 300.75

显式连接：

SELECT m.meter_id, r.read_time, r.value
FROM meters m
         JOIN readings r ON m.meter_id = r.meter_id;

-- 执行结果
meter_id | read_time           | value
M001     | 2019-01-01 10:00:00 | 100.50
M001     | 2019-01-01 11:00:00 | 102.50
M003     | 2019-01-01 10:00:00 | 300.75         

可以看到两种写法的结果是一模一样的。分析下结果：

• M001出现两次是因为有两条采集记录。
• M002不出现是因为不在档案表中。
• M003出现一次是档案表和采集表都有数据。
• M004不出现是因为在档案表中有数据但没有采集数据。

看下执行计划：

-- 隐式连接

Hash Join  (cost=26.65..170.13 rows=3478 width=82)
  Hash Cond: ((m.meter_id)::text = (r.meter_id)::text)
  ->  Seq Scan on meters m  (cost=0.00..19.40 rows=940 width=58)
  ->  Hash  (cost=17.40..17.40 rows=740 width=82)
        ->  Seq Scan on readings r  (cost=0.00..17.40 rows=740 width=82)

-- 显式连接
Hash Join  (cost=26.65..170.13 rows=3478 width=82)
  Hash Cond: ((m.meter_id)::text = (r.meter_id)::text)
  ->  Seq Scan on meters m  (cost=0.00..19.40 rows=940 width=58)
  ->  Hash  (cost=17.40..17.40 rows=740 width=82)
        ->  Seq Scan on readings r  (cost=0.00..17.40 rows=740 width=82)

可以看到两种查询的查询计划也是一模一样的，这里肯定是数据库会把隐式连接转换成显式连接来处理掉了。

两种写法没有区别，但还是优先用显式连接，这样多表连接时逻辑更清晰。

3. 外连接中 ON 与 WHERE 子句过滤的坑

这里直接看两个例子。

在 ON 子句中加过滤条件:

SELECT m.meter_id, r.read_time, r.value
FROM meters m
         LEFT JOIN readings r ON (m.meter_id = r.meter_id and r.value > 200);
         
-- 查询结果
meter_id | read_time           | value
M001     | NULL               | NULL       -- 保留了这行
M003     | 2019-01-01 10:00:00 | 300.75    
M004     | NULL               | NULL       -- 保留了这行

结果仍然返回所有电表的所有行数据，SQL 看起来是应该只返回 1 行的，但是实际上最后返回了 3 行。

这里的执行逻辑是先保留 meters 表所有行，对于每行检查ON条件。然后条件不满足时填充NULL，返回3行结果。

这里需要使用WHERE子句才能真正过滤行。

SELECT m.meter_id, r.read_time, r.value
FROM meters m
         LEFT JOIN readings r ON m.meter_id = r.meter_id
WHERE r.value > 200;

-- 查询结果
meter_id | read_time           | value
M003     | 2019-01-01 10:00:00 | 300.75

可以看到 M001 跟 M004 没有出现了， 因为对应的 value 为空或者没有 > 200。

这里的执行逻辑是先执行 LEFT JOIN ，然后再使用 WHERE 过滤，仅返回符合条件的这 1 行。

总结一下就是： 使用 ON 子句定义匹配条件，使用 WHERE 子句进行数据过滤 。