如何编写MySQL查询找出库存中无活跃所有者的书籍

表结构说明 #

• book_inventory：存储书籍的库存状态，status字段用active表示书籍在库，deleted表示已下架
• book_ownership：存储书籍的所有者变更记录，同一本书可以有多条记录，status为active代表当前所有者，deleted代表原所有者已售出

示例数据 #

book_inventory表 #

book_ownership表 #

查询需求 #

需要找出book_inventory中状态为active（在库），同时在book_ownership里没有对应active状态记录的书籍——也就是在库但目前没有当前所有者的书，示例里符合条件的是《指环王》。

实现方案 #

这里提供两种常用的MySQL写法，都能满足需求：

方法1：左连接筛选空值 #

SELECT bi.book_id, bi.book_name
FROM book_inventory bi
LEFT JOIN book_ownership bo 
    ON bi.book_id = bo.book_id 
    AND bo.status = 'active'
WHERE bi.status = 'active'
AND bo.book_id IS NULL;

方法2：NOT EXISTS子查询 #

SELECT book_id, book_name
FROM book_inventory bi
WHERE bi.status = 'active'
AND NOT EXISTS (
    SELECT 1
    FROM book_ownership bo
    WHERE bo.book_id = bi.book_id
    AND bo.status = 'active'
);

逻辑解释 #

• 左连接写法：把所有在库书籍和它们对应的活跃所有者记录关联起来，那些关联后没有匹配到所有者记录（bo.book_id IS NULL）的，就是我们要找的目标书籍。
• NOT EXISTS写法：直接检查每本在库书籍，确认不存在对应的活跃所有者记录，满足条件就返回该书籍。

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