mysql join two tables- how to give different value based on where the value originally come from?-CodePudding

let's say I have two table A, B


    A: NAME, ID, BIRTHDAY, COUNTRY
---------------------------------------
       john, ab123, 12/21/1999, USA
       JANE, as111, 11/02/1956, FRANCE

    B: NAME, ID, SALARY
---------------------------------------
       MARY, AS111, 12333


I WANT TO JOIN TWO TABLES WITH THESE COLUMNS :
NAME, ID, FROM_WHERE
-------------------------------
john, ab123, 'A'
JANE, as111, 'A'
MARY, AS111, 'B'

FROM_WHERE shows "A" if the value is originated from A, and "B" if the value is originated from B.

SELECT NAME, ID FROM A JOIN B USING (NAME, ID)

With the query above, what should I add to get the FROM_WHERE value?

CodePudding user response：

you can use UNION to do that like:

SELECT NAME, ID, 'A' as FROM_TABLE from A 
UNION ALL
SELECT NAME, ID, 'B' as FROM_TABLE from B 
ORDER by NAME;