无需UNION实现多表关联：book_users与assign_book_users查询优化咨询

我有book_users和assign_book_users两张数据表，表结构及数据如下：

book_users表 #

id  book_id  user_id  status
1   11       33       open
2   44       54       closed
3   11       98       pending
4   12       33       open
5   23       99       open
6   24       33       closed
7   25       98       pending
8   26       33       open

assign_book_users表 #

id  book_id  user_id  assigner_id  corp_id
1   11       33       55           2345
2   11       33       232          345
3   11       98       55           2345
4   12       33       235          667
5   12       33       77           876
6   12       45       89           2345

我想要得到如下查询结果：

book_id  user_id  assigner_id  status
11       33       55           open
11       98       55           pending
12       45       89           NULL 
44       54       NULL         closed
23       99       NULL         open

目前我只能通过UNION语句实现该查询，现有查询语句如下：

SELECT 
    book_users.book_id,
    book_users.user_id,
    assign_book_users.assigner_id,
    book_users.status
FROM book_users 
INNER JOIN assign_book_users 
    ON assign_book_users.user_id = book_users.user_id 
    AND assign_book_users.book_id = book_users.book_id 
    AND assign_book_users.corp_id = 2345 
UNION

SELECT 
    assign_book_users.book_id,
    assign_book_users.user_id,
    assign_book_users.assigner_id,
    NULL as status
FROM assign_book_users
WHERE assign_book_users.corp_id = 2345
    AND assign_book_users.user_id NOT IN (SELECT book_users.user_id FROM book_users)

UNION
SELECT 
    book_users.book_id,
    book_users.user_id,
    NULL as assigner_id,
    book_users.status
FROM book_users
WHERE book_users.user_id NOT IN (SELECT assign_book_users.user_id FROM assign_book_users WHERE assign_book_users.corp_id = 2345);

请问是否存在不使用UNION的更优方法来获取相同结果？

可以使用**全外连接（FULL OUTER JOIN）**结合筛选条件实现，不需要UNION，写法更简洁高效：

SELECT 
    COALESCE(b.book_id, a.book_id) AS book_id,
    COALESCE(b.user_id, a.user_id) AS user_id,
    a.assigner_id,
    b.status
FROM book_users b
FULL OUTER JOIN (
    -- 先筛选corp_id=2345的记录，减少连接数据量
    SELECT book_id, user_id, assigner_id 
    FROM assign_book_users 
    WHERE corp_id = 2345
) a ON b.book_id = a.book_id AND b.user_id = a.user_id
WHERE 
    -- 保留三类目标记录：两边匹配的、仅在筛选后的assign表的、仅在book表的
    (b.book_id IS NOT NULL AND a.book_id IS NOT NULL)
    OR (a.book_id IS NOT NULL AND b.book_id IS NULL)
    OR (b.book_id IS NOT NULL AND a.book_id IS NULL)
ORDER BY book_id, user_id;

说明： #

1. 子查询提前过滤assign_book_users中corp_id=2345的记录，减少后续连接的计算量
2. COALESCE函数处理字段的NULL值，确保book_id和user_id始终能取到有效数值
3. WHERE子句精准筛选出需要的三类记录，避免冗余数据
4. 最终按book_id和user_id排序，与期望结果顺序一致

如果你的数据库不支持FULL OUTER JOIN（比如MySQL），可以用LEFT JOIN加RIGHT JOIN的组合模拟，仅需一次UNION，比原写法简洁：

SELECT 
    COALESCE(b.book_id, a.book_id) AS book_id,
    COALESCE(b.user_id, a.user_id) AS user_id,
    a.assigner_id,
    b.status
FROM book_users b
LEFT JOIN (
    SELECT book_id, user_id, assigner_id 
    FROM assign_book_users 
    WHERE corp_id = 2345
) a ON b.book_id = a.book_id AND b.user_id = a.user_id
UNION
SELECT 
    COALESCE(b.book_id, a.book_id) AS book_id,
    COALESCE(b.user_id, a.user_id) AS user_id,
    a.assigner_id,
    b.status
FROM book_users b
RIGHT JOIN (
    SELECT book_id, user_id, assigner_id 
    FROM assign_book_users 
    WHERE corp_id = 2345
) a ON b.book_id = a.book_id AND b.user_id = a.user_id
WHERE b.book_id IS NULL
ORDER BY book_id, user_id;

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