如何基于共有列customer_id获取两表均存在数据的UNION查询结果

存在table1、table2两张表，两表共有列customer_id。当前使用UNION搭配筛选条件可获取两表全量匹配筛选规则的数据，但实际需要仅保留基于customer_id匹配，在两张表中都存在对应数据的行。

• 源数据示例：
  
• 期望输出示例：
  
  当前使用的SQL代码如下：

SELECT customer_id, 
       age,
       amount,
       type 
FROM table1 
WHERE age > 5 

UNION 

SELECT customer_id,
       age,
       amount,
       type 
FROM table2 
WHERE age > 5 

UNION的作用是返回两个查询结果的去重并集，无法实现“仅保留两表共有的customer_id对应记录”的筛选逻辑，可通过以下两种方式实现需求：

写法1：子查询匹配共有ID #

写法简洁，适配绝大多数SQL引擎：

SELECT customer_id, age, amount, type
FROM table1
WHERE age > 5
  AND customer_id IN (SELECT customer_id FROM table2 WHERE age > 5)

UNION ALL

SELECT customer_id, age, amount, type
FROM table2
WHERE age > 5
  AND customer_id IN (SELECT customer_id FROM table1 WHERE age > 5)

这里用UNION ALL替代UNION性能更高，如果你不需要对两表合并后的结果做去重，优先选择UNION ALL。

写法2：CTE提前筛选共有客户 #

逻辑更清晰，适合后续需要复用共有客户集合的场景：

WITH valid_common_customer AS (
    SELECT DISTINCT t1.customer_id
    FROM table1 t1
    INNER JOIN table2 t2
        ON t1.customer_id = t2.customer_id
    WHERE t1.age > 5 AND t2.age > 5
)
SELECT customer_id, age, amount, type
FROM table1
WHERE age > 5 AND customer_id IN (SELECT customer_id FROM valid_common_customer)

UNION ALL

SELECT customer_id, age, amount, type
FROM table2
WHERE age > 5 AND customer_id IN (SELECT customer_id FROM valid_common_customer)

两种写法最终返回的结果都和期望输出一致：仅保留两张表中age > 5、且customer_id同时在两表筛选结果中存在的对应行。

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