merged SQL table names

Question

I have a merged table from several unions and i want to know from which of those tables the results were taken, is that possible?

example...

select name from users where name like '%alex%' union select name from admins where name like '%alex%';

Would return lets say two rows, Alexander and Alexandra. Alexander is an admin and Alexandra is a user. How can i tell them apart?

Answer 1

SELECT 
     Name, 
    'Users' AS Type 
FROM users 
WHERE name LIKE '%alex%'
UNION
SELECT 
     Name, 
     'Admins' AS Type 
FROM admins 
WHERE name LIKE'%alex%'

Answer 2

Include a virtual column in your select that will allow you to identify the source table

select name, 'Name' as Source from users where name like '%alex%' 
union select name, 'Admins' as Source from admins where name like '%alex%';