Get data origin after a UNION

Question

I have a SQL query like this:

SELECT *
FROM (
  (SELECT name FROM man)
  UNION
  (SELECT name FROM woman )
) AS my_table
ORDER BY name

how can I retrieve the source of my data? For example if my result is like this:

Bob
Alice
Mario
...

I want to know if the name 'Bob' is retrieve from the 'man' table or from the 'woman' table.

Answer 1

SELECT * 
FROM ( 
  (SELECT name, 'man' as source FROM man) 
  UNION ALL
  (SELECT name, 'woman' FROM woman ) 
) AS my_table 
ORDER BY name 

I added the UNION ALL becasue if these are mutually exclusive tables, it will be faster. If they are not, then adding the source will make the results mutually exclusive and you wil be able to see where the dups are. If they are not mutually exclusive but you only want to show one record, what business rule do you want to show which record you took?

Answer 2

A select can include a literal string, so the simplest way is probably to do:

SELECT *
FROM (
  (SELECT name, 'man' as source FROM man)
  UNION
  (SELECT name, 'woman' as source FROM woman )
) AS my_table
ORDER BY name

Answer 3

These will only work if there is no intersection of Man & Woman.

If you expect duplicates, you will need to add some magic to the where clause. and perhaps a 3rd query in the union to cover those where both exist.