SQL - JOIN TABLE A AND TABLE B SO THAT EACH ROW STATES WHICH TABLE IT COMES FROM, A, B OR BOTH-CodePudding

You have two tables A and B, both tables have the same columns but different rows, some rows exist in both table A and B, some rows only exist in table A or B but not both. How can you join both tables so that the resulting table has all the rows from both tables and a new column that states where each row comes from, table A, table B, or BOTH.

EXAMPLE:

Table A
 ------------ 
| id | value |
 ------------ 
| 1  |   10  |
| 2  |   20  | 
 ------------ 

Table B
 ------------ 
| id | value |
 ------------ 
| 1  |   10  |
| 3  |   30  | 
 ------------ 

EXPECTED RESULT
 ---------------------- 
| id | value | origin  |
 ---------------------- 
| 2  |  20   |    A    |
| 3  |  30   |    B    |
| 1  |  10   |   BOTH  |
 ----------------------

I got asked this question on an interview and I didn't answer it properly so now I'm curious on what the correct answer is. Thanks in advance for answering my question, every little bit helps.

CodePudding user response：

You can union the rows from both tables (with an added column indicating where each row came from) then group by and group_concat(origin) to check if the row exists in either or both tables:

select id, value, 
case group_concat(origin order by origin)
when 'A' then 'A'
when 'B' then 'B'
when 'A,B' then 'BOTH'
end as origin from
(select *, 'A' as origin from A
union all 
select *, 'B' as origin from B) t
group by id, value

Fiddle

(Assuming a couple (id, value) cannot appear more than once in each table)