sql - 查询输出

Question

我在一次采访中被问到这个问题。有2张 table A & B (他们只有一列 id )具有以下数据

Table A
id
--
1
2
3

Table B
id
--
2
3
4

我需要产生以下类型的输出:

id | Present
------------
1  | "In A"
2  | "In A & In B"
3  | "In A & In B"
4  | "In B"

我使用 Union 提出了一个解决方案条款，像这样:

SELECT id, "In A" FROM A WHERE id NOT IN (SELECT id FROM B)
UNION
SELECT id, "In A & In B" WHERE id IN (SELECT id FROM B)
UNION
SELET id, "In B" WHERE id NOT IN (SELECT id FROM A)

在这种情况下，我使用 3 个单独的查询并使用 UNION将它们结合起来。我认为面试只是在寻找我的方法而不是确切的查询。话虽如此，在我提出我的解决方案后，他告诉我至少有一个仅使用 2 个查询的解决方案，并且还有一个仅使用 1 个查询的解决方案。我尝试使用 FULL OUTER JOIN ，但想不出任何具体的东西。

那么获得以下输出的更好查询是什么？面试官没有提到任何特定的数据库，所以我认为存在通用的解决方案。但是，如果查询特定于某些 SQL 风格，那也没关系。不过通用会很好。

Answer 1

这是一个与 DBMS 相当不可知的答案(尽管由于缺乏对 FULL OUTER JOIN 的支持，它在 MySQL 上不起作用):

SELECT COALESCE(A.id, B.id) AS id,
       CASE WHEN A.id IS NULL THEN 'In B'
            WHEN B.id IS NULL THEN 'In A'
            ELSE 'In A & In B' END AS Present
FROM A
FULL OUTER JOIN B ON B.id = A.id

输出:

id  present
1   In A
2   In A & In B
3   In A & In B
4   In B

查询依赖于这样一个事实:如果外连接的第二个表中没有匹配的行，那么具有 NULL 值的行将是 JOIN编。所以如果 A.id IS NULL ，这意味着该值仅在表 B 中。如果 B.id IS NULL ，则该值仅在表 A 中。如果两者都是 id值为非 NULL，该值必须在两个表中都存在。

Oracle demo
SQL Server demo
PostgreSQL demo