如何返回匹配JOIN部分条件的数据，近似FULL OUTER JOIN效果？

首先，先修正你原SQL里的一个小语法错误——T1.Cost后面缺少了逗号，这会导致SQL执行报错，先把基础语法理顺：

SELECT 
  T1.Name AS T1_Name, T1.Description AS T1_Description, T1.Style AS T1_Style, T1.Cost AS T1_Cost,
  T2.Name AS T2_Name, T2.Description AS T2_Description, T2.Style AS T2_Style, T2.Cost AS T2_Cost
FROM Table1 T1
FULL OUTER JOIN Table2 T2
ON T1.Name = T2.Name

接下来针对你的需求——返回匹配JOIN条件（这里是Name相同）的数据，同时达到近似FULL OUTER JOIN的效果（也就是不管哪个表有对应Name的数据都能展示，哪怕另一个表没有），分两种情况说明：

1. 标准FULL OUTER JOIN实现（支持的数据库：SQL Server、PostgreSQL、Oracle等） #

FULL OUTER JOIN本身就会返回左表和右表中所有匹配JOIN条件的行，以及任一表中不匹配的行（不匹配的一侧字段会显示为NULL）。针对你的场景，我们可以优化查询，让差异更直观：

SELECT 
  COALESCE(T1.Name, T2.Name) AS Item_Name, -- 统一显示商品名称，避免两侧NULL的情况
  T1.Description AS T1_Description,
  T2.Description AS T2_Description,
  T1.Style AS T1_Style,
  T2.Style AS T2_Style,
  T1.Cost AS T1_Cost,
  T2.Cost AS T2_Cost,
  -- 直观标记哪些字段存在差异
  CASE WHEN T1.Style <> T2.Style THEN 'Style不同' ELSE '' END AS Style_Diff,
  CASE WHEN T1.Cost <> T2.Cost THEN 'Cost不同' ELSE '' END AS Cost_Diff
FROM Table1 T1
FULL OUTER JOIN Table2 T2
ON T1.Name = T2.Name

执行这个查询后，针对你的示例数据，会得到这样的对比结果：

2. 不支持FULL OUTER JOIN的数据库替代方案（比如MySQL） #

如果你的数据库不支持FULL OUTER JOIN，可以用LEFT JOIN + RIGHT JOIN + UNION ALL来模拟效果，确保两个表的所有相关数据都能被展示：

-- 先取左表所有匹配、不匹配的行
SELECT 
  COALESCE(T1.Name, T2.Name) AS Item_Name,
  T1.Description AS T1_Description,
  T2.Description AS T2_Description,
  T1.Style AS T1_Style,
  T2.Style AS T2_Style,
  T1.Cost AS T1_Cost,
  T2.Cost AS T2_Cost
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.Name = T2.Name

UNION ALL

-- 再取右表中未在左表匹配到的行（避免重复）
SELECT 
  COALESCE(T1.Name, T2.Name) AS Item_Name,
  T1.Description AS T1_Description,
  T2.Description AS T2_Description,
  T1.Style AS T1_Style,
  T2.Style AS T2_Style,
  T1.Cost AS T1_Cost,
  T2.Cost AS T2_Cost
FROM Table1 T1
RIGHT JOIN Table2 T2
ON T1.Name = T2.Name
WHERE T1.Name IS NULL

另外，如果你原本的ON子句想添加更多匹配条件（比如AND T1.Description = T2.Description），直接加到ON后面即可——这样只会返回那些满足多条件匹配的行，同时展示其他字段的差异，完全符合你“返回匹配JOIN部分条件的数据”的需求。

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