SQL面试题：用1/2条SQL语句实现两表id存在性标识输出

Hey there! Let's tackle this SQL problem you ran into during your interview. You already had a 3-UNION solution, but let's break down the 1-statement and 2-statement approaches the interviewer mentioned, including both standard SQL and database-compatible tweaks.

1条语句的通用标准解法（FULL OUTER JOIN） #

This is the most efficient approach using standard SQL, and it's likely what your interviewer was hinting at. The key is using FULL OUTER JOIN to combine all records from both tables, then using a CASE statement to flag each ID's status:

SELECT
    COALESCE(a.id, b.id) AS id,
    CASE
        WHEN a.id IS NOT NULL AND b.id IS NOT NULL THEN 'In A & In B'
        WHEN a.id IS NOT NULL THEN 'In A'
        ELSE 'In B'
    END AS status
FROM A
FULL OUTER JOIN B ON A.id = B.id
ORDER BY id;

How it works: #

• COALESCE(a.id, b.id) grabs the non-null ID value (since one side will be null for IDs unique to A or B)
• The CASE statement checks presence in both tables:
  • If both IDs exist: it's the intersection
  • Only A's ID exists: unique to A
  • Only B's ID exists: unique to B

Note: This works in PostgreSQL, SQL Server, Oracle, and other databases that support FULL OUTER JOIN. If you're using MySQL 5.x (which doesn't support this join type), use the 2-statement approach below.

2条语句的兼容解法（适配无FULL OUTER JOIN的数据库） #

This approach uses LEFT JOIN + UNION ALL to achieve the same result without relying on FULL OUTER JOIN, making it compatible with MySQL 5.x and other databases with limited join support:

-- 第一条语句：处理A的所有记录（含与B的交集）
SELECT
    a.id,
    CASE WHEN b.id IS NOT NULL THEN 'In A & In B' ELSE 'In A' END AS status
FROM A
LEFT JOIN B ON A.id = B.id

UNION ALL

-- 第二条语句：处理B中独有的记录
SELECT b.id, 'In B' AS status
FROM B
LEFT JOIN A ON B.id = A.id
WHERE a.id IS NULL
ORDER BY id;

How it works: #

• The first query gets all IDs from A, using LEFT JOIN to check if they exist in B (flagging the intersection accordingly)
• The second query filters IDs in B that don't exist in A (using WHERE a.id IS NULL) and marks them as "In B"
• UNION ALL is used instead of UNION because there's no overlap between the two result sets, which avoids unnecessary deduplication and improves performance

另一种1条语句的通用解法（基于子查询+EXISTS） #

If you prefer a more readable approach that works across all databases (even those with limited join support), you can combine all unique IDs first, then check their presence in each table:

SELECT
    t.id,
    CASE
        WHEN EXISTS(SELECT 1 FROM A WHERE A.id = t.id) AND EXISTS(SELECT 1 FROM B WHERE B.id = t.id) THEN 'In A & In B'
        WHEN EXISTS(SELECT 1 FROM A WHERE A.id = t.id) THEN 'In A'
        ELSE 'In B'
    END AS status
FROM (
    SELECT id FROM A UNION SELECT id FROM B
) t
ORDER BY id;

How it works: #

• The subquery t combines all unique IDs from both tables using UNION
• For each ID in t, EXISTS checks if it's present in A, B, or both, and the CASE statement sets the status accordingly

对比你的原始解法 #

Your original 3-UNION approach works, but it runs three separate subqueries and uses UNION (which deduplicates results), making it less efficient than the join-based methods above. The approaches we've covered are more performant and align with the interviewer's request for fewer statements.

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