Search code examples
sql

Can I include the table name in the result of an SQL query?

If I have two tables:

Actor:
ID | Name
1  : Patrick
3  : Emma
7  : Vanessa

Singer:
ID | Name
4  : Will
5  : Madonna
13 : Emma

Can I generate the following table from an SQL query, which contains the table name each record came from?

ID | Career | Name
1  : Actor  : Patrick
3  : Actor  : Emma
4  : Singer : Will
5  : Singer : Madonna
7  : Actor  : Emma
13 : Singer : Emma

I'm assuming the ID column items are unique across the two tables, but not the names.

Solution 
  • select ID, 'Actor' as Career, Name from Actor
union all
select ID, 'Singer' as Career, Name from Singer

    Or something along these lines.