ADD STATUS COLUMN FROM SELECT FULL OUTER JOIN to identify that matched and unmatched-CodePudding

Using FULL OUTER JOIN, I need an extra column to identify the matched data, unmatched data from table1, and unmatched data from table2. Having trouble identifying as the table construction doesn't have a primary key (I can't change the table structure of the 2 tables).

SELECT 
    a.column1,
    a.column2, 
    a.column3,
    a.column4, 
    a.column5,
--EXTRA COLUMN(STATUS) to identify the MATCHED(M), UNMATCHED FROM table1(UT1), UNMATCHED FROM table2(UT2)
FROM 
table1 a
FULL OUTER JOIN
table2 b ON
a.column1 = b.column1 AND
a.column2 = b.column2;

CodePudding user response：

Values from the table will be null when there is no match.

SELECT 
    a.column1,
    a.column2, 
    a.column3,
    a.column4, 
    a.column5,
CASE 
    WHEN b.column1 IS null THEN 'UT1'
    WHEN a.column1 IS null THEN 'UT2'
    ELSE 'M'
END as STATUS
FROM 
table1 a
FULL OUTER JOIN
table2 b ON
a.column1 = b.column1 AND
a.column2 = b.column2;