如何用WHERE语句替代LEFT JOIN实现图书借阅统计查询？

问题背景 #

我有一个包含4张表的数据库，需使用其中3张（reader、copy、book）。已经有一个正确的LEFT JOIN查询语句，但需要将其改写为仅使用WHERE语句的形式。

表结构说明 #

• reader表：包含nr（用户编号）、personal_code（个人编码）、name（姓名）、surname（姓氏）字段
• copy表：包含ISBN（图书ISBN）、reader（关联的用户编号）、taken（借阅日期）、return（归还日期）字段
• book表：包含ISBN、pages（页数）、cost（成本）字段

原LEFT JOIN代码片段 #

FROM        reader
LEFT JOIN   copy
ON          reader.nr = copy.reader
LEFT JOIN   book
ON          copy.ISBN = book.ISBN

该查询输出8行正确结果，包含所有用户的个人编码、姓名、姓氏、已借图书数量、已借图书总页数、已借图书总成本，包括当前无借阅图书的用户。

尝试与问题 #

我尝试用以下WHERE语句替代，但仅输出4行，缺失了当前无借阅图书的用户数据：

WHERE       copy.ISBN = book.ISBN AND 
            reader.nr = copy.reader AND
            CURRENT_DATE BETWEEN copy.taken AND copy.return

正确的完整LEFT JOIN查询 #

SELECT
    reader.personal_code,
    reader.name,
    reader.surname,
    COALESCE(COUNT(copy.reader), 0) AS Taken_books,
    COALESCE(SUM(book.pages), 0) AS Pages_sum,
    CASE
        WHEN COUNT(copy.reader) > 0
             THEN SUM(COALESCE(book.cost, 15))
        ELSE
            0
    END AS books_cost
    
FROM        reader
LEFT JOIN   copy
ON          reader.nr = copy.reader AND CURRENT_DATE BETWEEN copy.taken AND copy.return
LEFT JOIN   book
ON          copy.ISBN = book.ISBN
GROUP BY
    reader.personal_code,
    reader.name,
    reader.surname;

解决方案：用WHERE语句替代LEFT JOIN #

要保留所有reader表的行（包括无借阅的用户），需用老式逗号连接表，并通过OR 字段 IS NULL模拟LEFT JOIN的逻辑，确保无匹配行时用户数据不被过滤。

改写后的SQL如下：

SELECT
    r.personal_code,
    r.name,
    r.surname,
    COALESCE(COUNT(c.reader), 0) AS Taken_books,
    COALESCE(SUM(b.pages), 0) AS Pages_sum,
    CASE
        WHEN COUNT(c.reader) > 0
             THEN SUM(COALESCE(b.cost, 15))
        ELSE
            0
    END AS books_cost
FROM reader r, copy c, book b
WHERE 
    -- 模拟reader与copy的LEFT JOIN：要么匹配有效借阅，要么无对应copy记录
    (r.nr = c.reader AND CURRENT_DATE BETWEEN c.taken AND c.return)
    OR c.reader IS NULL
    -- 模拟copy与book的LEFT JOIN：要么匹配图书，要么无对应book记录
    AND (c.ISBN = b.ISBN OR b.ISBN IS NULL)
GROUP BY
    r.personal_code,
    r.name,
    r.surname;

关键说明 #

• 老式逗号连接表默认是INNER JOIN，必须通过OR 表字段 IS NULL来保留无匹配的用户行，还原LEFT JOIN的效果。
• 用括号明确条件优先级，避免逻辑冲突导致的结果错误。
• 保留原查询中的COALESCE和CASE逻辑，确保无借阅用户的统计值显示为0。

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