如何用单条SQL关联内部/外部客户表查询购物清单？

问题背景 #

现有三张业务表：

• internalcustomer：存储内部客户，字段为name（全局唯一）、email
• externalcustomer：存储外部客户，字段为name（全局唯一）、email
• shoppinglist：存储客户购物清单，通过name字段与客户表关联，包含商品名字段（假设为product_name）

需求是用单条SQL查询指定客户name对应的购物清单，需返回商品名、客户名、客户邮箱，且逻辑上优先关联internalcustomer，匹配不到该客户时再关联externalcustomer。

最优SQL实现方案 #

利用LEFT JOIN关联两张客户表，结合COALESCE()函数优先取内部客户的邮箱，单条SQL即可完成需求：

SELECT 
    sl.product_name,
    sl.name AS customer_name,
    -- 优先取内部客户邮箱，不存在则取外部客户邮箱
    COALESCE(ic.email, ec.email) AS customer_email
FROM shoppinglist sl
-- 左关联内部客户表
LEFT JOIN internalcustomer ic 
    ON sl.name = ic.name
-- 左关联外部客户表
LEFT JOIN externalcustomer ec 
    ON sl.name = ec.name
-- 指定查询的客户名
WHERE sl.name = '目标客户名';

逻辑说明

因为两张客户表的name字段全局唯一，所以任意客户只会存在于其中一张表中：

• 如果目标客户在internalcustomer中，ic.email有值，COALESCE()会直接返回该值
• 如果目标客户不在internalcustomer中，ic.email为NULL，COALESCE()会返回ec.email的值

备选方案（UNION ALL 写法） #

如果更倾向于先查询内部客户、无结果再查外部客户的逻辑，也可以用UNION ALL结合NOT EXISTS实现：

-- 先查内部客户关联的购物清单
SELECT 
    sl.product_name,
    sl.name AS customer_name,
    ic.email AS customer_email
FROM shoppinglist sl
INNER JOIN internalcustomer ic 
    ON sl.name = ic.name
WHERE sl.name = '目标客户名'

UNION ALL

-- 仅当内部客户不存在时，查询外部客户的购物清单
SELECT 
    sl.product_name,
    sl.name AS customer_name,
    ec.email AS customer_email
FROM shoppinglist sl
INNER JOIN externalcustomer ec 
    ON sl.name = ec.name
WHERE sl.name = '目标客户名'
AND NOT EXISTS (
    SELECT 1 FROM internalcustomer ic 
    WHERE ic.name = sl.name
);

注意事项

这种写法的查询效率略低于LEFT JOIN方案，因为会对shoppinglist执行两次扫描（若内部客户不存在），因此更推荐第一种方案。

性能优化建议 #

为三张表的name字段建立索引，可大幅提升关联查询的效率：

-- 为内部客户表name字段建索引
CREATE INDEX idx_internalcustomer_name ON internalcustomer(name);
-- 为外部客户表name字段建索引
CREATE INDEX idx_externalcustomer_name ON externalcustomer(name);
-- 为购物清单表name字段建索引
CREATE INDEX idx_shoppinglist_name ON shoppinglist(name);

内容的提问来源于stack exchange，提问作者Wuxiang Zhou