查询两张表指定列的重叠记录方法求助

1. 找出两表中重叠的ID #

以下几种方式都能实现，可根据数据量选择合适的写法：

方式一：IN子查询 #

SELECT DISTINCT ID
FROM tableA
WHERE ID IN (SELECT ID FROM tableB);

方式二：EXISTS子查询（大表场景下效率更高） #

SELECT DISTINCT a.ID
FROM tableA a
WHERE EXISTS (SELECT 1 FROM tableB b WHERE b.ID = a.ID);

方式三：INNER JOIN #

SELECT DISTINCT a.ID
FROM tableA a
INNER JOIN tableB b ON a.ID = b.ID;

2. 检查重叠ID对应的location是否匹配 #

通过关联两表直接对比location列，还能直观看到两边的位置内容：

SELECT 
    a.ID,
    a.location AS location_from_A,
    b.location AS location_from_B,
    CASE WHEN a.location = b.location THEN '位置匹配' ELSE '位置不匹配' END AS match_status
FROM tableA a
INNER JOIN tableB b ON a.ID = b.ID;

如果只需要筛选出位置不匹配的重叠ID，加上WHERE条件即可：

SELECT 
    a.ID,
    a.location AS location_from_A,
    b.location AS location_from_B
FROM tableA a
INNER JOIN tableB b ON a.ID = b.ID
WHERE a.location != b.location;

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