SQL查询问题：按两种逻辑关联documents与users表

Got it, let's work through this problem. You need to cover two key scenarios: returning users with their assigned documents, and returning users who have no assigned documents paired with unassigned documents (plus handling cases where either might not exist). Here are two solid approaches:

方法1：使用UNION ALL组合两个查询（兼容性强） #

这种方法把两种场景拆成两个独立查询，再合并结果，几乎所有SQL数据库都支持。

第一部分：获取有分配文档的用户与对应文档 #

这部分用INNER JOIN直接关联用户和他们已分配的文档：

SELECT
  u.id AS user_id,
  u.name AS user_name,
  d.id AS document_id,
  d.title AS document_title
FROM users u
INNER JOIN documents d 
  ON u.id = d.user_id

第二部分：获取无分配文档的用户与未分配的文档 #

这里先筛选出没有任何分配文档的用户，再关联所有未分配给任何人的文档（如果没有未分配文档，会返回用户数据，文档字段为NULL）：

SELECT
  u.id AS user_id,
  u.name AS user_name,
  d.id AS document_id,
  d.title AS document_title
FROM users u
LEFT JOIN documents d_assigned 
  ON u.id = d_assigned.user_id
LEFT JOIN documents d 
  ON d.user_id IS NULL
WHERE d_assigned.id IS NULL -- 确保用户没有分配任何文档

合并两个查询 #

用UNION ALL（比UNION高效，因为不会去重）把两部分结果合并：

-- 有分配文档的用户+文档
SELECT
  u.id AS user_id,
  u.name AS user_name,
  d.id AS document_id,
  d.title AS document_title
FROM users u
INNER JOIN documents d 
  ON u.id = d.user_id

UNION ALL

-- 无分配文档的用户+未分配文档（或用户数据+NULL文档）
SELECT
  u.id AS user_id,
  u.name AS user_name,
  d.id AS document_id,
  d.title AS document_title
FROM users u
LEFT JOIN documents d_assigned 
  ON u.id = d_assigned.user_id
LEFT JOIN documents d 
  ON d.user_id IS NULL
WHERE d_assigned.id IS NULL

方法2：使用LATERAL JOIN（简洁高效，适合支持的数据库） #

如果你的数据库支持LATERAL JOIN（比如PostgreSQL）或CROSS APPLY（SQL Server），可以用这种更简洁的方式，在一个查询里处理两种场景：

SELECT
  u.id AS user_id,
  u.name AS user_name,
  d.id AS document_id,
  d.title AS document_title
FROM users u
LEFT JOIN LATERAL (
  -- 优先取用户已分配的文档
  SELECT id, title FROM documents WHERE user_id = u.id
  -- 如果用户没有已分配文档，取所有未分配的文档
  UNION ALL
  SELECT id, title FROM documents 
  WHERE user_id IS NULL 
    AND NOT EXISTS (SELECT 1 FROM documents WHERE user_id = u.id)
) d ON true

这个查询的逻辑是：对每个用户，先尝试关联他们的已分配文档；如果没有，就关联所有未分配的文档。如果既没有已分配文档也没有未分配文档，会返回用户数据，文档字段为NULL。

关键注意点 #

• 如果你需要确保无文档用户即使没有未分配文档也能被返回，两种方法都已经覆盖了这种情况（文档字段为NULL）。
• 用UNION ALL时，确保两个查询的列数和数据类型完全一致，避免报错。

内容的提问来源于stack exchange，提问作者Piotr Kłos