Select Union Query problem

Question

I have 2 tables

Table A

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

Table B

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

Result needs to be

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

I am able to do UNION to fetch the result but how i generate column Founds. Any idea ?? Thank you in advance :)

Answer 1

Wrong answer You are looking for "union distinct" instead of just union. Corrected when I got the smack down and realized I mis read the question.

Generate a bitmask as below, using simple power notation to make it clearer that im just inserting ingrementing powers of 2 for the bitmask.

with data as (
SELECT Id,Name, 2^0 as bitmask FROM A
UNION ALL 
SELECT Id,Name, 2^1 as bitmask FROM B
UNION ALL 
SELECT Id,Name, 2^2 as bitmask FROM C)
SELECT Id,Name, SUM(bitmask)
FROM data
GROUP BY Id,Name

Answer 2

It looks to me like you effectively want to simultaneously do a left and right join. This technically isn't possible, since you always need to have one reference table. The only way I can think of doing this would be the following:

SELECT tableA.*
  LEFT JOIN tableB.* USING name
UNION DISTINCT SELECT tableB.*
  LEFT JOIN tableA USING name

After thinking some more, you may also be able to do:

SELECT tableA.*
  LEFT JOIN  tableB.* USING name
  RIGHT JOIN tableB.* USING name

...although I'm not sure that's valid.

Answer 3

Use:

  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

Alternative:

  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

Answer 4

The way to do this is to use a FULL OUTER JOIN, but since this is not supported in MySQL you can instead use a combination of a LEFT JOIN and INNER JOIN and RIGHT JOIN.

(
    SELECT A.Id, A.Name, 'A' AS Found
    FROM A LEFT JOIN B ON A.Name = B.Name
    WHERE B.Name IS NULL
)
UNION ALL
(
    SELECT B.Id, B.Name, 'B' AS Found
    FROM A RIGHT JOIN B ON A.Name = B.Name
    WHERE A.Name IS NULL
)
UNION ALL
(
    SELECT A.Id, A.Name, 'C' AS Found
    FROM A JOIN B ON A.Name = B.Name
)

In fact, you only need a LEFT and RIGHT JOIN because you can handle the INNER JOIN at the same time as you do one of the other two joins. I think the above demonstrates the principle more clearly, but in practice the following will give better performance:

SELECT A.Id, A.Name, IF(B.Name IS NULL, 'A', 'C') AS Found
FROM A LEFT JOIN B ON A.Name = B.Name
UNION ALL
SELECT B.Id, B.Name, 'B' AS Found
FROM A RIGHT JOIN B ON A.Name = B.Name
WHERE A.Name IS NULL

Result:

Id  Name   Found
1   Scott  A    
2   Dan    C    
3   Sam    A    
2   Andi   B    
3   Jess   B    

Answer 5

select tmp.name, case count(*) when 1 then tmp.tbl else 'C' end found 
  from (select id, name, 'A' tbl from TableA 
        union all 
        select id, name, 'B' tbl from TableB) as tmp 
 group by tmp.name;

+-------+-------+
| name  | found |
+-------+-------+
| Andi  | B     |
| Dan   | C     |
| Jess  | B     |
| Sam   | A     |
| Scott | A     |
+-------+-------+