sql - How to combine two tables (with same schema) into a single table with distinct values and indicating which table each row came from? -

suppose have 2 tables in sql:

table_alpha   table_bravo id | name     id | name ---+------    ---+-----  1 | alice     1 | charlie  2 | bob       2 | bob  3 | charlie   3 | dorothy 

i want combine 2 tables single table, avoiding duplicates , keeping track of table each name came so:

result name   | alpha | bravo -------+-------+------ alice  |   1   |   0   bob    |   1   |   1   charlie|   1   |   1   dorothy|   0   |   1   

i think query want this:

select name, 1 alpha, 0 bravo table_alpha union select name, 0 alpha, 1 bravo table_bravo; 

however, above query return 2 rows each name appears in both tables. how can write query return 1 row each distinct name?

will work?

select distinct name, sum(alpha) 'alpha', sum(bravo) 'bravo' (     select name, 1 alpha, 0 bravo table_alpha     union     select name, 0 alpha, 1 bravo table_bravo ) x group name