MySQL how to add an extra column to a search query that has unioned two rows from different tables t-CodePudding

So I have unioned two rows from 2 different tables, which works quite alright.

SELECT CONCAT(contactFirstName," ",contactLastName), customerNumber FROM classicmodels.customers 
UNION 
SELECT CONCAT(firstName, " ", lastName), employeeNumber FROM classicmodels.employees;

What I am struggling to do is to have another column that shows from which table I got each name, since I have to sort them alphabetically later, which means I can´t just seperate them by table 1 and table 2 through a limit or something.

CodePudding user response：

You can just add your own column and use it as the first value to order by, eg

SELECT CONCAT(contactFirstName," ",contactLastName) as FullName, customerNumber, 1 as Source
FROM classicmodels.customers 
UNION 
SELECT CONCAT(firstName, " ", lastName), employeeNumber, 2 
FROM classicmodels.employees;

CodePudding user response：

Just add table name in the form of String with a specific alias.

SELECT CONCAT(contactFirstName," ",contactLastName), customerNumber, 'customers' as data_source FROM classicmodels.customers 
UNION 
SELECT CONCAT(firstName, " ", lastName), employeeNumber, 'employees' as data_source FROM classicmodels.employees;