mysql - 选择联合查询？

Question

我有两个表:

表A:

id  name
------------    
1   Scott
2   Dan
3   Sam

表 B:

id  name
------------    
1   Dan
2   Andi
3   Jess

我的结果需要是:

Id Name  Found
1  Scott A
2  Dan   C i.e. found in both
3  Sam   A
2  Andi  B
3  Jess  B

我可以执行 UNION 来获取结果，但如何创建 Found 列？

Answer 1

使用:

  SELECT CASE 
           WHEN y.name IS NULL THEN z.id
           WHEN z.name IS NULL THEN y.id
           ELSE y.id
         END AS id,
         x.name,
         CASE 
           WHEN y.name IS NULL THEN 'B'
           WHEN z.name IS NULL THEN 'A'
           ELSE 'C'
         END AS found
    FROM (SELECT a.name
            FROM TABLE_A a
          UNION 
          SELECT b.name
            FROM TABLE_B b) x
LEFT JOIN TABLE_A y ON y.name = x.name
LEFT JOIN TABLE_B z ON z.name = x.name

备选方案:

  SELECT COALESCE(y.id, z.id) AS id,
         x.name,
         CASE 
           WHEN y.name IS NULL THEN 'B'
           WHEN z.name IS NULL THEN 'A'
           ELSE 'C'
         END AS found
    FROM (SELECT a.name
            FROM TABLE_A a
          UNION 
          SELECT b.name
            FROM TABLE_B b) x
LEFT JOIN TABLE_A y ON y.name = x.name
LEFT JOIN TABLE_B z ON z.name = x.name