Search code examples
mysqlsqldatabaseunionunion-all

Mysql UNION way to differ from which table result row is

Lets say I have below query. 

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Lest say there is one same city NY in both tables witch will give me: 

| City     |
| -------- | 
| NY       |
| NY       |

I am fetching this in a HTML table, is there a way to differ from witch MySQL table result row is from, other then making another column in tables itself, for example column type.

Maybe is possible to append something to result set itself in query?

Solution

  • I don't understand your results, because union removes duplicates, so it should produce only one row.

    If you want to include the table name, I would advise using select distinct and union all:

    SELECT DISTINCT 'customers' as type, City FROM Customers
UNION ALL
SELECT DISTINCT 'suppliers' as type, City FROM Suppliers
ORDER BY City;